Format KNX values into SQL INSERT for MYSQL

General
1

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.

2

node-red can write to MYSQL.

node-red has a KNX nodes

I'm gonna say yes

3

Ok.

I make this flow:

imagen
imagen985×409 15.1 KB

Inside:

[
    {
        "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:

{"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:

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,

4

Hello again.

A Few things...

  1. read how to share code or flow json
  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 - 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"

5

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

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;
6

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.

no try something like...

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 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...

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

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:

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:

{"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:

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:

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

8

:msg.topic

the : notation doesn't work here.

Like @Steve-Mcl indicated:

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

What does the debug output immediately after the function show.

Did it produce a valid SQL statement in the topic?

10

without :msg.topic

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:

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
11

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....

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

12

The Debug Node just after funtion:

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

13

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. ...

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;
14

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

2 Likes
15

The joy of programming.

16

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