# Post to sql from array
**URL:** https://discourse.nodered.org/t/post-to-sql-from-array/16418
**Category:** General
**Created:** [7 October 2019 09:42 UTC](https://discourse.nodered.org/t/post-to-sql-from-array/16418 "2019-10-07T09:42:44Z")
**Posts on this page:** 17
**Page:** 1
### Author:  [@johnym30](https://discourse.nodered.org/u/johnym30)
#### Post date: [7 October 2019 09:42 UTC](https://discourse.nodered.org/t/post-to-sql-from-array/16418/1 "2019-10-07T09:42:44Z")
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
```auto
[{"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.
Thank you in advance
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [7 October 2019 09:57 UTC](https://discourse.nodered.org/t/post-to-sql-from-array/16418/2 "2019-10-07T09:57:54Z")
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.
---
### Author:  [@johnym30](https://discourse.nodered.org/u/johnym30)
#### Post date: [7 October 2019 10:13 UTC](https://discourse.nodered.org/t/post-to-sql-from-array/16418/3 "2019-10-07T10:13:00Z")
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
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [7 October 2019 10:22 UTC](https://discourse.nodered.org/t/post-to-sql-from-array/16418/4 "2019-10-07T10:22:48Z")
> [@johnym30](#):
>
> I dont know how to post to mysql the data that i have outlined in blue.
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](https://nodered.org/docs/user-guide/messages)
---
### Author:  [@johnym30](https://discourse.nodered.org/u/johnym30)
#### Post date: [7 October 2019 10:48 UTC](https://discourse.nodered.org/t/post-to-sql-from-array/16418/5 "2019-10-07T10:48:35Z")
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.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [7 October 2019 10:56 UTC](https://discourse.nodered.org/t/post-to-sql-from-array/16418/6 "2019-10-07T10:56:36Z")
> [@johnym30](#):
>
> I can not figure out how to inject these payloads to mysql.
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.
---
### Author:  [@johnym30](https://discourse.nodered.org/u/johnym30)
#### Post date: [7 October 2019 12:19 UTC](https://discourse.nodered.org/t/post-to-sql-from-array/16418/7 "2019-10-07T12:19:31Z")
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.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [7 October 2019 12:27 UTC](https://discourse.nodered.org/t/post-to-sql-from-array/16418/8 "2019-10-07T12:27:21Z")
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.
---
### Author:  [@johnym30](https://discourse.nodered.org/u/johnym30)
#### Post date: [7 October 2019 12:39 UTC](https://discourse.nodered.org/t/post-to-sql-from-array/16418/9 "2019-10-07T12:39:02Z")
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.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [7 October 2019 12:46 UTC](https://discourse.nodered.org/t/post-to-sql-from-array/16418/10 "2019-10-07T12:46:41Z")
Put a debug node set to show complete message on the output of the template node and show us what you see.
---
### Author:  [@johnym30](https://discourse.nodered.org/u/johnym30)
#### Post date: [7 October 2019 13:15 UTC](https://discourse.nodered.org/t/post-to-sql-from-array/16418/11 "2019-10-07T13:15:44Z")
So i see in the message the values
```auto
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
```auto
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"
```
---
### Author:  [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)
#### Post date: [7 October 2019 13:19 UTC](https://discourse.nodered.org/t/post-to-sql-from-array/16418/12 "2019-10-07T13:19:24Z")
Just a thought... do the 'types' you have used in your INSERT match what you have declared in your database ?
---
### Author:  [@johnym30](https://discourse.nodered.org/u/johnym30)
#### Post date: [7 October 2019 13:21 UTC](https://discourse.nodered.org/t/post-to-sql-from-array/16418/13 "2019-10-07T13:21:50Z")
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [7 October 2019 13:24 UTC](https://discourse.nodered.org/t/post-to-sql-from-array/16418/14 "2019-10-07T13:24:09Z")
> [@johnym30](#):
>
> payload: "INSERT INTO Devices(id,pulse,date) VALUES (335754,7716,20191007)" topic: "/home/emonadmin/testdb/"
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.
---
### Author:  [@johnym30](https://discourse.nodered.org/u/johnym30)
#### Post date: [7 October 2019 13:29 UTC](https://discourse.nodered.org/t/post-to-sql-from-array/16418/16 "2019-10-07T13:29:46Z")
At last!!!! Thanks that was what i was missing!!! Thank you all for your help!!!!
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [7 October 2019 13:31 UTC](https://discourse.nodered.org/t/post-to-sql-from-array/16418/17 "2019-10-07T13:31:19Z")
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.
---
### Author:  [@johnym30](https://discourse.nodered.org/u/johnym30)
#### Post date: [7 October 2019 13:40 UTC](https://discourse.nodered.org/t/post-to-sql-from-array/16418/18 "2019-10-07T13:40:23Z")
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 🙂