# Write variables to MariaDB (SQL Database)

**URL:** <https://discourse.nodered.org/t/write-variables-to-mariadb-sql-database/49452>\
**Category:** General\
**Created:** [7 August 2021 15:42 UTC](https://discourse.nodered.org/t/write-variables-to-mariadb-sql-database/49452 "2021-08-07T15:42:15Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![nicedevil](https://avatars.discourse-cdn.com/v4/letter/n/858c86/32.png) [@nicedevil](https://discourse.nodered.org/u/nicedevil)\
**Post date:** [7 August 2021 15:42 UTC](https://discourse.nodered.org/t/write-variables-to-mariadb-sql-database/49452/1 "2021-08-07T15:42:15Z")

</div>

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)](https://flows.nodered.org/node/node-red-contrib-stackhero-mysql)

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/5/05c29f03e3c507dcf031e0498ea2222ee26cab27.png)

That is the flow I have built:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/5/4531077a0af232fa6c2d90cbf498d45f3029e5d8.png)

Here is my functionnode:

```auto
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?

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/7/072e62107357ea0a8ddeb3fefac5d2bad4d8cb67.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [7 August 2021 16:20 UTC](https://discourse.nodered.org/t/write-variables-to-mariadb-sql-database/49452/2 "2021-08-07T16:20:46Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![nicedevil](https://avatars.discourse-cdn.com/v4/letter/n/858c86/32.png) [@nicedevil](https://discourse.nodered.org/u/nicedevil)\
**Post date:** [7 August 2021 16:25 UTC](https://discourse.nodered.org/t/write-variables-to-mariadb-sql-database/49452/3 "2021-08-07T16:25:02Z")

</div>

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"`

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [7 August 2021 17:01 UTC](https://discourse.nodered.org/t/write-variables-to-mariadb-sql-database/49452/4 "2021-08-07T17:01:57Z")

</div>

> [@nicedevil](#):
>
> `msg.payload = {room, device, value}`

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [7 August 2021 17:02 UTC](https://discourse.nodered.org/t/write-variables-to-mariadb-sql-database/49452/5 "2021-08-07T17:02:22Z")

</div>

Try putting the `set` clause before the `where` clause

---

<div class="post-metadata">

**Author:** ![nicedevil](https://avatars.discourse-cdn.com/v4/letter/n/858c86/32.png) [@nicedevil](https://discourse.nodered.org/u/nicedevil)\
**Post date:** [7 August 2021 17:06 UTC](https://discourse.nodered.org/t/write-variables-to-mariadb-sql-database/49452/6 "2021-08-07T17:06:02Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![nicedevil](https://avatars.discourse-cdn.com/v4/letter/n/858c86/32.png) [@nicedevil](https://discourse.nodered.org/u/nicedevil)\
**Post date:** [7 August 2021 17:09 UTC](https://discourse.nodered.org/t/write-variables-to-mariadb-sql-database/49452/7 "2021-08-07T17:09:07Z")

</div>

that was the issue with the dash in the tablename!

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [7 August 2021 17:10 UTC](https://discourse.nodered.org/t/write-variables-to-mariadb-sql-database/49452/8 "2021-08-07T17:10:03Z")

</div>

Well you learn something everyday. Cheers

---

<div class="post-metadata">

**Author:** ![nicedevil](https://avatars.discourse-cdn.com/v4/letter/n/858c86/32.png) [@nicedevil](https://discourse.nodered.org/u/nicedevil)\
**Post date:** [7 August 2021 17:11 UTC](https://discourse.nodered.org/t/write-variables-to-mariadb-sql-database/49452/9 "2021-08-07T17:11:18Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![nicedevil](https://avatars.discourse-cdn.com/v4/letter/n/858c86/32.png) [@nicedevil](https://discourse.nodered.org/u/nicedevil)\
**Post date:** [7 August 2021 17:16 UTC](https://discourse.nodered.org/t/write-variables-to-mariadb-sql-database/49452/10 "2021-08-07T17:16:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [7 August 2021 18:05 UTC](https://discourse.nodered.org/t/write-variables-to-mariadb-sql-database/49452/11 "2021-08-07T18:05:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [21 August 2021 18:06 UTC](https://discourse.nodered.org/t/write-variables-to-mariadb-sql-database/49452/12 "2021-08-21T18:06:29Z")

</div>

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