Write variables to MariaDB (SQL Database)

Hey there,

I was trying to get my data to a sql database and I'm struggling with the correct syntax.
I hope someone can help me out.

I'm using this node here: node-red-contrib-stackhero-mysql (node) - Node-RED (nodered.org)

According to his small guide to get values out of SQL database I tryed to write them to it:

That is the flow I have built:

Here is my functionnode:

    device = "deskLED",
    room = "office",
    value = 0;

msg.topic = "UPDATE persistent-variables WHERE room = :room AND device = :device SET value = :value;"
msg.payload = {room, device, value}

return msg;

And the error message tells me that the syntax is wrong?

Notice where it says the error is. It really isn't a good idea to have a minus sign in a table name. Change that if you can. Otherwise you will have to put the appropriate sort of quotes round the table name. I always have to look that up so I am not going to guess exactly what you need.

1 Like

Ty for that hint, changed it from persistent-variables to just variables and now I get this:

"Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE room = 'office' AND device = 'deskLED' SET value = 0' at line 1"

The object is not correct.
msg.payload = {room: room, device: device, value: value}

Try putting the set clause before the where clause

1 Like

it is correct and that only works when the variable for the name ob the object key is the same, try it out :slight_smile:

that was the issue with the dash in the tablename!

Well you learn something everyday. Cheers

that's true :slight_smile: and after I noticed this behaviour I started rethinking of coding when it comes to objects and there key names. makes code looks cleaner.

mhhh maybe you can help me out with the next problem? going to create an other post, because this issue is solved!

Always helps to check the actual syntax of a command. All I did was a google search ‘mariadb update syntax’ :slightly_smiling_face:

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.