Post to sql from array

General
1

hi everyone.
Once more as i have just started using node red and i am not a programmer, i have some questions.
I have the flow below

[{"id":"42edc0b9.7ba91","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"19767207.06a18e","type":"mysql","z":"42edc0b9.7ba91","mydb":"e717861e.a17788","name":"MYSQL","x":1108,"y":167,"wires":[["7693b48a.4d133c"]]},{"id":"b66a3eab.b6907","type":"template","z":"42edc0b9.7ba91","name":"Format data","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO Devices (id,pulse,date) VALUES (id,pulse,date)","output":"str","x":845,"y":169,"wires":[["19767207.06a18e","7693b48a.4d133c"]]},{"id":"b7e62e32.3c844","type":"comment","z":"42edc0b9.7ba91","name":"Flow to insert data into the database","info":"","x":880,"y":120,"wires":[]},{"id":"8b512bd8.e229c8","type":"delay","z":"42edc0b9.7ba91","name":"","pauseType":"rate","timeout":"2","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":576,"y":125,"wires":[["b66a3eab.b6907","7693b48a.4d133c"]]},{"id":"7693b48a.4d133c","type":"debug","z":"42edc0b9.7ba91","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1069,"y":303,"wires":[]},{"id":"cc1e9263.ecc9","type":"function","z":"42edc0b9.7ba91","name":"Parse data","func":"let parsed = {} \nlet p = msg.payload\nparsed.parts = p.split(\"\");\n//parsed.data = p;\n//parsed.datalength = parseInt(parsed.parts[1],16);\nparsed.date = parseInt(parsed.parts[0] + parsed.parts[1] + parsed.parts[2] + parsed.parts[3] + parsed.parts[4] + parsed.parts[5]+ parsed.parts[6] + parsed.parts[7],10);\nparsed.time = parseInt(parsed.parts[8] + parsed.parts[9] + parsed.parts[10] + parsed.parts[11] + parsed.parts[12] + parsed.parts[13],10);\nparsed.type = parseInt(parsed.parts[14] + parsed.parts[15],10);\nparsed.id = parseInt(parsed.parts[16] + parsed.parts[17] + parsed.parts[18] + parsed.parts[19] + parsed.parts[20] + parsed.parts[21],16);\nparsed.BatStatus = parseInt(parsed.parts[22] + parsed.parts[23],16);\nparsed.pulse = parseInt(parsed.parts[24] + parsed.parts[25] + parsed.parts[26] + parsed.parts[27] + parsed.parts[28] + parsed.parts[29],16);\nparsed.Sigstatus = parseInt(parsed.parts[30] + parsed.parts[31],16);\nparsed.CheckCode = parseInt(parsed.parts[32] + parsed.parts[33] + parsed.parts[34] + parsed.parts[35],16);\n\n\nmsg.payload = parsed; \n//.nodegroup = parsed.id;\n//msg.payload.parsed.Date\nreturn msg;\n","outputs":1,"noerr":0,"x":399,"y":171,"wires":[["8b512bd8.e229c8"]]},{"id":"f0cfae94.94da6","type":"file in","z":"42edc0b9.7ba91","name":"msg.filename","filename":"","format":"lines","chunk":false,"sendError":false,"encoding":"utf8","x":288,"y":357,"wires":[["cc1e9263.ecc9"]]},{"id":"956b1953.df0cc8","type":"change","z":"42edc0b9.7ba91","name":"","rules":[{"t":"set","p":"filename","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":137,"y":290,"wires":[["f0cfae94.94da6"]]},{"id":"800c4dc.40830b","type":"watch","z":"42edc0b9.7ba91","name":"","files":"/home/emonadmin/testdb/","recursive":"","x":115,"y":196,"wires":[["956b1953.df0cc8"]]},{"id":"e717861e.a17788","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"AMRData","tz":""}]

I have the above flow working and post data to emoncms.
Now what i want to do is to post specific data to an mysql database.
How can i send specific data from the array and insert it to sql at the corresponding tables?
Here is the data i get from the file i try to read.

array_data
array_data.jpg289×584 46.7 KB

Thank you in advance

2

It is not clear which part of the problem you don't know how to do

  1. Connect to the database
  2. Extract the data from your message
  3. Build the message to insert into the database

If it is 2 or 3 then possibly the page Working with Messages in the docs would help.

3

Thanks for the reply. I suppose as you can see from the above picture i have the data extracted that i need. I dont know how to post to mysql the data that i have outlined in blue. I will take a look at the docs once more to see if i can manage it.
Thank you once more

4

Do you mean you don't know how to extract those items from the message?
The Working with Messages page includes details of how to copy the path to a particular item from the debug window so you can use it in, for example, a Change or Function node.
https://nodered.org/docs/user-guide/messages

5

As i saw each of these messages its a payload item( i saw the path besides each item and shows payload.id, payload.date etc) I can not figure out how to inject these payloads to mysql.

6

Do you know how to insert data to mysql in node-red or is it just this particular data that is the problem?

[Edit] Since earlier you indicated that connecting to the database is not an issue then you have already determined that node-red-contrib-mysql is the node to use. As described in the information for that node all you have to do is to put the SQL query into msg.topic and pass it to the node.

7

The particular data, for when i try to inject "static" values to mysql, with the command INSERT INTO Devices(id,pulse,date) VALUES (1,2,3) they appear in my tables.

8

Probably the simplest way to build the query string is to use a template node (that is not the dashboard template node, but the one in the Functions category in the palette. The info tab for that node explains how to insert data from the payload into the string.

9

Thanks, as you can see in my flow above i already used a template node and in the template i have INSERT INTO Device(id,pulse,time) VALUES ({{payload.id}},{{payload.pulse}},{{payload.time}})
When i use static values they go to my tables, but when i am using the above line, i get an error

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 '/home/emonadmin/testdb/' at line 1
I am using mysql Ver 14.14 Distrib 5.7.27
Thanks once more.

10

Put a debug node set to show complete message on the output of the template node and show us what you see.

11

So i see in the message the values

10/7/2019, 4:17:24 PMnode: 7693b48a.4d133c
/home/emonadmin/testdb/ : msg : Object
object
payload: "INSERT INTO Devices(id,pulse,date) VALUES (335754,7716,20191007)"
topic: "/home/emonadmin/testdb/"
filename: "/home/emonadmin/testdb/ON02_20191007123734.txt"
parts: object
index: 0xd
ch: "↵"
type: "string"
id: "7a2d0f86.cedd3"
_msgid: "dabc3bbd.75ff18"

but i get the error

msg : error
"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 '/home/emonadmin/testdb/' at line 1"
12

Just a thought... do the 'types' you have used in your INSERT match what you have declared in your database ?

13

mysql
mysql.jpg1042×229 61.5 KB

14

You have put the query into the payload instead of the topic. However, the flow you posted showed that you had selected msg.topic. I wonder whether you have somehow changed that to msg.payload since you posted the flow.

16

At last!!!! Thanks that was what i was missing!!! Thank you all for your help!!!!

17

I have to point out that if you had posted that output at the start and said that it did not insert it into the database you would have got an answer much more quickly.

18

I am still learning node-red and i really appreciate your help. As i go along and learn how things work it will be much easier for the next time. Thank you once more :slight_smile: