Write variables to MariaDB (SQL Database)

[nicedevil]

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:

const
    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?

[Colin]

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.

[nicedevil]

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"

[E1cid]

@nicedevil

msg.payload = {room, device, value}

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

[zenofmud]

Try putting the set clause before the where clause

[nicedevil]

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

[nicedevil]

that was the issue with the dash in the tablename!

[E1cid]

Well you learn something everyday. Cheers

[nicedevil]

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

[nicedevil]

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

[zenofmud]

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