# Format KNX values into SQL INSERT for MYSQL

**URL:** <https://discourse.nodered.org/t/format-knx-values-into-sql-insert-for-mysql/25774>\
**Category:** General\
**Created:** [29 April 2020 22:16 UTC](https://discourse.nodered.org/t/format-knx-values-into-sql-insert-for-mysql/25774 "2020-04-29T22:16:30Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![Hyxion14](https://avatars.discourse-cdn.com/v4/letter/h/977dab/32.png) [@Hyxion14](https://discourse.nodered.org/u/Hyxion14)\
**Post date:** [29 April 2020 22:16 UTC](https://discourse.nodered.org/t/format-knx-values-into-sql-insert-for-mysql/25774/1 "2020-04-29T22:16:30Z")

</div>

Hello I have a project in which I need to save in a bbdd mysql all the states of all KNX groups in real time.

In other words, I need to listen to the KNX BUS and send all the datagrams that are written to the BUS to a mysql DB.

I just met Node Red and I find it very interesting, but I don't know if Node Red will be able to do this.

Can someone help or advise me.

Thank you.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [30 April 2020 00:57 UTC](https://discourse.nodered.org/t/format-knx-values-into-sql-insert-for-mysql/25774/2 "2020-04-30T00:57:58Z")

</div>

node-red can write to [MYSQL](https://flows.nodered.org/search?term=MYSQL).

node-red has a [KNX nodes](https://flows.nodered.org/search?term=knx)

I'm gonna say yes

---

<div class="post-metadata">

**Author:** ![Hyxion14](https://avatars.discourse-cdn.com/v4/letter/h/977dab/32.png) [@Hyxion14](https://discourse.nodered.org/u/Hyxion14)\
**Post date:** [30 April 2020 09:41 UTC](https://discourse.nodered.org/t/format-knx-values-into-sql-insert-for-mysql/25774/3 "2020-04-30T09:41:48Z")

</div>

Ok.

I make this flow:

 ![imagen](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/8/28ee69fccd9b6131bd931521d305cc712d697dae.png)  
Inside:

```auto
[
    {
        "id": "881f0096.15857",
        "type": "tab",
        "label": "Flow 1",
        "disabled": false,
        "info": ""
    },
    {
        "id": "f016b4b0.d42468",
        "type": "knxUltimate",
        "z": "881f0096.15857",
        "server": "8545ba84.ac5078",
        "topic": "",
        "outputtopic": "",
        "dpt": "",
        "initialread": false,
        "notifyreadrequest": true,
        "notifyresponse": true,
        "notifywrite": true,
        "notifyreadrequestalsorespondtobus": false,
        "notifyreadrequestalsorespondtobusdefaultvalueifnotinitialized": "0",
        "listenallga": true,
        "name": "",
        "outputtype": "read",
        "outputRBE": false,
        "inputRBE": false,
        "formatmultiplyvalue": 1,
        "formatnegativevalue": "leave",
        "formatdecimalsvalue": 999,
        "passthrough": "no",
        "x": 270,
        "y": 160,
        "wires": [
            [
                "2cbc76ca.4e9d82",
                "33ec34b7.82f684"
            ]
        ]
    },
    {
        "id": "2cbc76ca.4e9d82",
        "type": "debug",
        "z": "881f0096.15857",
        "name": "",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "x": 530,
        "y": 60,
        "wires": []
    },
    {
        "id": "765e5344.0272d4",
        "type": "mysql",
        "z": "881f0096.15857",
        "mydb": "4ac8a5e1.dd4dac",
        "name": "",
        "x": 760,
        "y": 340,
        "wires": [
            [
                "42243b2c.0976cc"
            ]
        ]
    },
    {
        "id": "33ec34b7.82f684",
        "type": "function",
        "z": "881f0096.15857",
        "name": "",
        "func": "msg.topic=\"INSERT INTO `knx-group` (`id`, `timestamp`, `topic`, `payload`, `devicename`, `payloadmeasureunit`, `payloadsubtypevalue`) VALUES (NULL, CURRENT_TIMESTAMP, \" +flow.get(\"topico\")+ \", '', 'temperatura', 'ºC', 'DTP9')\"\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "x": 530,
        "y": 340,
        "wires": [
            [
                "765e5344.0272d4",
                "4986fa1a.26d1a4"
            ]
        ]
    },
    {
        "id": "42243b2c.0976cc",
        "type": "debug",
        "z": "881f0096.15857",
        "name": "",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "x": 970,
        "y": 340,
        "wires": []
    },
    {
        "id": "4986fa1a.26d1a4",
        "type": "debug",
        "z": "881f0096.15857",
        "name": "",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "x": 750,
        "y": 440,
        "wires": []
    },
    {
        "id": "8545ba84.ac5078",
        "type": "knxUltimate-config",
        "z": "",
        "host": "192.168.0.151",
        "port": "3671",
        "physAddr": "15.15.22",
        "suppressACKRequest": false,
        "csv": "\"temperatura\"\t\"0/1/0\"\t\"\"\t\"\"\t\"\"\t\"DPST-9-1\"\t\"Auto\"\n\"heartbeat\"\t\"0/1/1\"\t\"\"\t\"\"\t\"\"\t\"DPST-1-17\"\t\"Auto\"",
        "KNXEthInterface": "Auto",
        "KNXEthInterfaceManuallyInput": "",
        "statusDisplayLastUpdate": true,
        "statusDisplayDeviceNameWhenALL": true,
        "statusDisplayDataPoint": false,
        "stopETSImportIfNoDatapoint": "stop",
        "loglevel": "error",
        "name": "Gateway KNX",
        "localEchoInTunneling": true
    },
    {
        "id": "4ac8a5e1.dd4dac",
        "type": "MySQLdatabase",
        "z": "",
        "name": "BBDD_NODE",
        "host": "91.142.220.3",
        "port": "3306",
        "db": "node",
        "tz": "GMT+2",
        "charset": "UTF8"
    }
]

```

The up debug show in windows debug:

```auto
{"topic":"0/1/1","payload":true,"devicename":"heartbeat","payloadmeasureunit":"unknown","payloadsubtypevalue":"trigger","knx":{"event":"GroupValue_Write","dpt":"1.017","dptdesc":"Trigger","source":"1.1.13","destination":"0/1/1","rawValue":[1]},"_msgid":"eb398c0f.734c6"}

```

Ok, to write into mysql table, my doubt is how to write in the bbdd the fields that I see in msg of the debug window.

If I do the function with this configuration:

```auto
msg.topic="INSERT INTO `knx-group` (`id`, `timestamp`, `topic`, `payload`, `devicename`, `payloadmeasureunit`, `payloadsubtypevalue`) VALUES (NULL, CURRENT_TIMESTAMP, '0/0/0', '', 'temperatura', 'ºC', 'DTP9')"
return msg;

```

Write the values correctly in the bbdd.

What I can't do is how to write the values that we see in the debug window.

In that window, we see that we have: topic, payload, devicename, payloadmeasureunit, payloadsubtypevalue

How do I extract those values to write them in the mysql statement?

Thank you,

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [30 April 2020 10:09 UTC](https://discourse.nodered.org/t/format-knx-values-into-sql-insert-for-mysql/25774/4 "2020-04-30T10:09:27Z")

</div>

Hello again.

A Few things...

1. read [how to share code or flow json](https://discourse.nodered.org/t/how-to-share-code-or-flow-json/506)
2. Put DEBUG nodes after every NODE to see what happens to the `msg` as it travels down the wire.
3. It is impossible to tell as you have only sent a screen shot.
  - We cannot see inside a function node from a screen shot

4. Have you read the BUILT IN HELP INFO in the sidebar of the MYSQL node? It tells you how to send SQL to the node.

Lastly, I strongly recommend you spend 20 minutes on this page - [working with messages](https://nodered.org/docs/user-guide/messages) - there is INVALUABLE information that will help you help yourself. Especially the bit about **"When you hover over any element, a set of buttons appear on the right"**

---

<div class="post-metadata">

**Author:** ![Hyxion14](https://avatars.discourse-cdn.com/v4/letter/h/977dab/32.png) [@Hyxion14](https://discourse.nodered.org/u/Hyxion14)\
**Post date:** [30 April 2020 11:26 UTC](https://discourse.nodered.org/t/format-knx-values-into-sql-insert-for-mysql/25774/5 "2020-04-30T11:26:54Z")

</div>

Hello Steve,

Thanks, It is my first Node Red Flow, and my firts message in this forum.

1: Ok thanks, edited my post to make it better.

2: Yes, Debug Nodes I am realizing that Debug Nodes are essential to understand how this works.

3: I exported the flow so you can see what it contains.

4: Yes, Writing to the mysql bbdd is something that it does exactly, my problem is rather to understand how the exchange of messages between nodes works.

I have seen the page that you recommend, thank you.

As you can see, in the Debug Node that connects directly to the KNX node, the message that is sent has: topic, payload, devicename, payloadmeasureunit, payloadsubtypevalue.

In my bbdd I have a table that has those fields.

The only thing I need is in the function where I make the insertion to the bbdd, replace the values with the contents in the message.

Could use?:

```auto
msg.topic="INSERT INTO `knx-group` (`id`, `timestamp`, `topic`, `payload`, `devicename`, `payloadmeasureunit`, `payloadsubtypevalue`) VALUES (NULL, CURRENT_TIMESTAMP, :msg.topic, :msg.payload, :msg.devicename, :msg.payloadmeasureunit, :payloadsubtypevalue)"
return msg;

```

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [30 April 2020 11:41 UTC](https://discourse.nodered.org/t/format-knx-values-into-sql-insert-for-mysql/25774/6 "2020-04-30T11:41:36Z")

</div>

> [@Hyxion14](#):
>
> Thanks, It is my first Node Red Flow, and my firts message in this forum.

no worries (we all start somewhere).

And welcome to the forum.

You will find people here very helpful if you show that you have tried something yourself first.

Happy coding.

> [@Hyxion14](#):
>
> Could use?:
> 
> ```auto
> msg.topic="INSERT INTO `knx-group` (`id`, `timestamp`, `topic`, `payload`, `devicename`, `payloadmeasureunit`, `payloadsubtypevalue`) VALUES (NULL, CURRENT_TIMESTAMP, :msg.topic, :msg.payload, :msg.devicename, :msg.payloadmeasureunit, :payloadsubtypevalue)"
> return msg;
> 
> ```

no try something like...

```auto
msg.topic = `INSERT INTO \`knx-group\` (id, \`timestamp\`, topic, payload, devicename, payloadmeasureunit, payloadsubtypevalue) VALUES (NULL, CURRENT_TIMESTAMP, ${msg.topic}, ${msg.payload}, ${msg.devicename}, ${msg.payloadmeasureunit}, ${msg.payloadsubtypevalue})`
return msg;

```

remember to check the debug output to ensure format is correct.

PS here i am using a [Template Literal](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals) to simplify generating the string

PS2 - try not to use `-` hyphens in SQL table names or fields (removes the need for MYSQL specific `field` backticks.) Also, avoid keywords like `timestamp` (so that you dont have to wrap them in backticks)

PS3 - if you really want to learn more about node-red - a little exercise for you.  
Instead of using a function node, use a template node. Then you could do something like...

```auto
INSERT INTO `knx-group` (id, `timestamp`, topic, etc, etc) 
VALUES (NULL, CURRENT_TIMESTAMP, {{msg.topic}}, {{etc}}, {{etc}} )

```

---

<div class="post-metadata">

**Author:** ![Hyxion14](https://avatars.discourse-cdn.com/v4/letter/h/977dab/32.png) [@Hyxion14](https://discourse.nodered.org/u/Hyxion14)\
**Post date:** [30 April 2020 12:51 UTC](https://discourse.nodered.org/t/format-knx-values-into-sql-insert-for-mysql/25774/7 "2020-04-30T12:51:04Z")

</div>

Thank you very much, I always think the same, that "we all start somewhere".

Thanks for your advice, it is true that if you do not show that you are trying to do the code, it may seem that you want others to do it, it is not my case, I am breaking my head trying and understanding to learn to code in Node Red.

I have tried your code but it doesn't work for me, the error it gives me in the debug window is:

```auto
Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'trigger)' at line 1

```

If I look at the debug node that I have put at the output of the KNX Node, the output I have is:

```auto
{"topic":"0/1/0","payload":22.2,"devicename":"temperatura","payloadmeasureunit":"°C","payloadsubtypevalue":"unknown","knx":{"event":"GroupValue_Write","dpt":"9.001","dptdesc":"Temperature","source":"1.1.13","destination":"0/1/0","rawValue":[12,86]},"_msgid":"954cc87b.882818"}

```

I think what I am doing wrong is passing the variables to the mysql statement.

I have looked in the mysql documentation and seen this example:

```auto
msg.payload={}
msg.payload.userToChange=42;
msg.payload.newUsername="example-user";
msg.topic="INSERT INTO users (`userid`, `username`) VALUES (:userToChange, :newUsername) ON DUPLICATE KEY UPDATE `username`=:newUsername;"
return msg;

```

So he believed that the sentence:

```auto
msg.topic="INSERT INTO `knx-group` (`id`, `timestamp`, `topic`, `payload`, `devicename`, `payloadmeasureunit`, `payloadsubtypevalue`) VALUES (NULL, CURRENT_TIMESTAMP, :msg.topic, :msg.payload, :msg.devicename, :msg.payloadmeasureunit, :payloadsubtypevalue)"
return msg;

```

But no

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [30 April 2020 12:54 UTC](https://discourse.nodered.org/t/format-knx-values-into-sql-insert-for-mysql/25774/8 "2020-04-30T12:54:52Z")

</div>

`:msg.topic`

the `:` notation doesn't work here.

Like @Steve-Mcl indicated:

```auto
INSERT INTO `knx-group` (id, `timestamp`, topic, etc, etc) 
VALUES (NULL, CURRENT_TIMESTAMP, {{msg.topic}}, {{msg.payload}}, {{msg.devicename}} , {{etc...}})

```

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [30 April 2020 12:57 UTC](https://discourse.nodered.org/t/format-knx-values-into-sql-insert-for-mysql/25774/9 "2020-04-30T12:57:44Z")

</div>

> [@Hyxion14](#):
>
> I have tried your code but it doesn't work for me, the error it gives me in the debug window is:

What does the debug output immediately after the function show.

Did it produce a valid SQL statement in the topic?

---

<div class="post-metadata">

**Author:** ![Hyxion14](https://avatars.discourse-cdn.com/v4/letter/h/977dab/32.png) [@Hyxion14](https://discourse.nodered.org/u/Hyxion14)\
**Post date:** [30 April 2020 13:08 UTC](https://discourse.nodered.org/t/format-knx-values-into-sql-insert-for-mysql/25774/10 "2020-04-30T13:08:11Z")

</div>

without :msg.topic

```auto
msg.topic="INSERT INTO `knx-group` (`id`, `timestamp`, `topic`, `payload`, `devicename`, `payloadmeasureunit`, `payloadsubtypevalue`) VALUES (NULL, CURRENT_TIMESTAMP, {{msg.topic}}, {{msg.payload}}, {{msg.devicename}}, {{msg.payloadmeasureunit}}, {{payloadsubtypevalue}})"
return msg;

```

dont work, the debug message is:

```auto
Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{msg.topic}}, {{msg.payload}}, {{msg.devicename}}, {{msg.payloadmeasureunit}}, {' at line 1

```

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [30 April 2020 13:11 UTC](https://discourse.nodered.org/t/format-knx-values-into-sql-insert-for-mysql/25774/11 "2020-04-30T13:11:41Z")

</div>

That error isn't from the function. That's from something else.

Also, that code `msg.topic="INSERT ....."` is NOT what I told you to do. That format was intended for a `template` node.

I said....

> [@Steve-Mcl](#):
>
> try something like...
> 
> ```auto
> msg.topic = `INSERT INTO \`knx-group\` (id, \`timestamp\`, topic, payload, devicename, payloadmeasureunit, payloadsubtypevalue) VALUES (NULL, CURRENT_TIMESTAMP, ${msg.topic}, ${msg.payload}, ${msg.devicename}, ${msg.payloadmeasureunit}, ${msg.payloadsubtypevalue})`
> return msg;
> 
> ```

Make the change I suggested, connect a debug to the output of the function node. What does topic have (before it goes to database)

---

<div class="post-metadata">

**Author:** ![Hyxion14](https://avatars.discourse-cdn.com/v4/letter/h/977dab/32.png) [@Hyxion14](https://discourse.nodered.org/u/Hyxion14)\
**Post date:** [30 April 2020 13:12 UTC](https://discourse.nodered.org/t/format-knx-values-into-sql-insert-for-mysql/25774/12 "2020-04-30T13:12:38Z")

</div>

The Debug Node just after funtion:

```auto
30/4/2020 15:11:10node: 9b97dee5.bb6468
INSERT INTO `knx-group` (id, `timestamp`, topic, payload, devicename, payloadmeasureunit, payloadsubtypevalue) VALUES (NULL, CURRENT_TIMESTAMP, 0/1/1, true, heartbeat, unknown, trigger) : msg.payload : boolean

```

It is Ok

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [30 April 2020 13:16 UTC](https://discourse.nodered.org/t/format-knx-values-into-sql-insert-for-mysql/25774/13 "2020-04-30T13:16:35Z")

</div>

> [@Hyxion14](#):
>
> It is Ok

No.

String values in SQL need quotes.

you can easily test that by copy and paste into a SQL application or into a test flow.

So try tweaking until you get it right e.g. ...

```auto
msg.topic = `INSERT INTO \`knx-group\` (id, \`timestamp\`, topic, payload, devicename, payloadmeasureunit, payloadsubtypevalue) VALUES (NULL, CURRENT_TIMESTAMP, '${msg.topic}', '${msg.payload}', '${msg.devicename}', '${msg.payloadmeasureunit}', '${msg.payloadsubtypevalue}')`
return msg;

```

---

<div class="post-metadata">

**Author:** ![Hyxion14](https://avatars.discourse-cdn.com/v4/letter/h/977dab/32.png) [@Hyxion14](https://discourse.nodered.org/u/Hyxion14)\
**Post date:** [30 April 2020 13:22 UTC](https://discourse.nodered.org/t/format-knx-values-into-sql-insert-for-mysql/25774/14 "2020-04-30T13:22:28Z")

</div>

Yes now.

Making code is something that frustrates you, makes you p\*\*\*\*d off, changes your mood, until you finally get it to work, at that moment happiness is impossible to describe, ha ha ha.

Thank you very much for your help and your patience.

You are right, Debug NODE is essential to understand what is happening.

Thank you

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [30 April 2020 13:36 UTC](https://discourse.nodered.org/t/format-knx-values-into-sql-insert-for-mysql/25774/15 "2020-04-30T13:36:56Z")

</div>

> [@Hyxion14](#):
>
> code is something that frustrates you, makes you p\*\*\*\*d off, changes your mood, until you finally get it to work, at that moment happiness is impossible to describe, ha ha ha.

The joy of programming.

---

<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:** [14 May 2020 13:36 UTC](https://discourse.nodered.org/t/format-knx-values-into-sql-insert-for-mysql/25774/16 "2020-05-14T13:36:57Z")

</div>

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