# Flow to insert into MySQL table

**URL:** <https://discourse.nodered.org/t/flow-to-insert-into-mysql-table/31712>\
**Category:** General\
**Created:** [19 August 2020 02:34 UTC](https://discourse.nodered.org/t/flow-to-insert-into-mysql-table/31712 "2020-08-19T02:34:30Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![thagan](https://avatars.discourse-cdn.com/v4/letter/t/3d9bf3/32.png) [@thagan](https://discourse.nodered.org/u/thagan)\
**Post date:** [19 August 2020 02:34 UTC](https://discourse.nodered.org/t/flow-to-insert-into-mysql-table/31712/1 "2020-08-19T02:34:31Z")

</div>

Hello,

I currently have a flow working that inserts data from a controller device into a MSSQL table. However I need to change it to insert the data into a MySQL table. The flow is a timestamp insert every minute, it reads data from the controller, the data is placed into a msg.object, and then a insert sql query into the appropriate table. Just can't figure out how to get it into a MySQL table. Here is the working flow into MSSQL. Any help would be great.

```auto
[{"id":"1017beb5.640331","type":"inject","z":"12ce0a0e.af95c2","name":"","topic":"","payload":"","payloadType":"date","repeat":"60","crontab":"","once":true,"onceDelay":"60","x":180,"y":540,"wires":[["66e2edf3.d5b204"]]},{"id":"66e2edf3.d5b204","type":"pac-read","z":"12ce0a0e.af95c2","device":"dba83a6d.a23ee8","dataType":"float-table","tagName":"StorageData","tableStartIndex":"","tableLength":"","value":"StorageData","valueType":"msg","topic":"","topicType":"none","name":"","x":340,"y":420,"wires":[["8f2370b3.af78"]]},{"id":"8f2370b3.af78","type":"function","z":"12ce0a0e.af95c2","name":"StorageData","func":"msg.Relay1=msg.StorageData[0];\nmsg.Relay2=msg.StorageData[1];\nmsg.Relay3=msg.StorageData[2];\nmsg.Relay4=msg.StorageData[3];\nmsg.BatteryVolts=msg.StorageData[4];\nmsg.BatteryAmps=msg.StorageData[5];\nmsg.PVVolts=msg.StorageData[6];\nmsg.PVAmps=msg.StorageData[7];\nmsg.LoadVoltage=msg.StorageData[8];\nmsg.LoadAmps=msg.StorageData[9];\nmsg.CurrentChannel1A=msg.StorageData[10];\nmsg.CurrentChannel2A=msg.StorageData[11];\nmsg.CurrentChannel3A=msg.StorageData[12];\nmsg.CurrentChannel4A=msg.StorageData[13];\nmsg.CurrentChannel5A=msg.StorageData[14];\nmsg.CurrentChannel6A=msg.StorageData[15];\nmsg.CurrentChannel7A=msg.StorageData[16];\nmsg.CurrentChannel8A=msg.StorageData[17];\nmsg.CurrentChannel1B=msg.StorageData[18];\nmsg.CurrentChannel2B=msg.StorageData[19];\nmsg.CurrentChannel3B=msg.StorageData[20];\nmsg.CurrentChannel4B=msg.StorageData[21];\nmsg.Voltage1=msg.StorageData[22];\nmsg.Voltage2=msg.StorageData[23];\nmsg.Relay5=msg.StorageData[24];\nmsg.Relay6=msg.StorageData[25];\nmsg.Relay7=msg.StorageData[26];\nmsg.Relay8=msg.StorageData[27];\nmsg.Volt_Battery2=msg.StorageData[28];\nmsg.Amp_Battery2=msg.StorageData[29];\nmsg.Volt_PV2=msg.StorageData[30];\nmsg.Amp_PV2=msg.StorageData[31];\nmsg.Load_Voltage2=msg.StorageData[32];\nmsg.RelayRandom=msg.StorageData[33];\nreturn msg;","outputs":1,"noerr":0,"x":450,"y":520,"wires":[["42d3c795.52aa68"]]},{"id":"42d3c795.52aa68","type":"MSSQL","z":"12ce0a0e.af95c2","mssqlCN":"ecababf4.5b8728","name":"Azure","query":"insert into dbo.OptoData\n([Relay1]\n ,[Relay2]\n ,[Relay3]\n ,[Relay4]\n ,[BatteryVolts]\n ,[BatteryAmps]\n ,[PVVolts]\n ,[PVAmps]\n ,[LoadVoltage]\n ,[LoadAmps]\n ,[CurrentChannel1A]\n ,[CurrentChannel2A]\n ,[CurrentChannel3A]\n ,[CurrentChannel4A]\n ,[CurrentChannel5A]\n ,[CurrentChannel6A]\n ,[CurrentChannel7A]\n ,[CurrentChannel8A]\n ,[CurrentChannel1B]\n ,[CurrentChannel2B]\n ,[CurrentChannel3B]\n ,[CurrentChannel4B]\n ,[Voltage1]\n ,[Voltage2]\n ,[Relay5]\n ,[Relay6]\n ,[Relay7]\n ,[Relay8]\n ,[Volt_Battery2]\n ,[Amp_Battery2]\n ,[Volt_PV2]\n ,[Amp_PV2]\n ,[Load_Voltage2]\n ,[RelayRandom])\n Values\n ({{{Relay1}}}\n ,{{{Relay2}}}\n ,{{{Relay3}}}\n ,{{{Relay4}}}\n ,{{{BatteryVolts}}}\n ,{{{BatteryAmps}}}\n ,{{{PVVolts}}}\n ,{{{PVAmps}}}\n ,{{{LoadVoltage}}}\n ,{{{LoadAmps}}}\n ,{{{CurrentChannel1A}}}\n ,{{{CurrentChannel2A}}}\n ,{{{CurrentChannel3A}}}\n ,{{{CurrentChannel4A}}}\n ,{{{CurrentChannel5A}}}\n ,{{{CurrentChannel6A}}}\n ,{{{CurrentChannel7A}}}\n ,{{{CurrentChannel8A}}}\n ,{{{CurrentChannel1B}}}\n ,{{{CurrentChannel2B}}}\n ,{{{CurrentChannel3B}}}\n ,{{{CurrentChannel4B}}}\n ,{{{Voltage1}}}\n ,{{{Voltage2}}}\n ,{{{Relay5}}}\n ,{{{Relay6}}}\n ,{{{Relay7}}}\n ,{{{Relay8}}}\n ,{{{Volt_Battery2}}}\n ,{{{Amp_Battery2}}}\n ,{{{Volt_PV2}}}\n ,{{{Amp_PV2}}}\n ,{{{Load_Voltage2}}}\n ,{{{RelayRandom}}})","outField":"payload","x":610,"y":380,"wires":[["148c2403.9c926c"]]},{"id":"148c2403.9c926c","type":"debug","z":"12ce0a0e.af95c2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":710,"y":520,"wires":[]},{"id":"dba83a6d.a23ee8","type":"pac-device","z":"","address":"192.168.1.50","protocol":"http"},{"id":"ecababf4.5b8728","type":"MSSQL-CN","z":"","name":"Azure Opto","server":"168.61.182.91","encyption":true,"database":"OPTO"}]

```

---

<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:** [19 August 2020 09:48 UTC](https://discourse.nodered.org/t/flow-to-insert-into-mysql-table/31712/2 "2020-08-19T09:48:29Z")

</div>

Tim,  
First off when you are inserting a flow in a thread it needs to be formated correctly or it will not be importable by others. Please read [How to share code or flow json](https://discourse.nodered.org/t/how-to-share-code-or-flow-json/506) then edit your post and fix it. (click the pencil icon under your post )

Secondwhat have you tried so far?

- have you defined the MySQL database and table?
- which node are you using to talk to MySQL?

I would also suggest you create a new flow with a `insert` that contains your query, the mysql node you are using and a `debug` node (set to display the complete msg object) so you can see the results. You might want to add a `catch` node connected to another `debug` node (set to display the complete msg object) to catch any errors.

---

<div class="post-metadata">

**Author:** ![thagan](https://avatars.discourse-cdn.com/v4/letter/t/3d9bf3/32.png) [@thagan](https://discourse.nodered.org/u/thagan)\
**Post date:** [19 August 2020 13:31 UTC](https://discourse.nodered.org/t/flow-to-insert-into-mysql-table/31712/3 "2020-08-19T13:31:20Z")

</div>

Paul,

First thank you so much for responding. And I read how to post code and fixed it so I appreciate that tip as well.

- Yes I have the MySQL database and table built and defined. But not sure where the insert query as I am using with the MSSQL node goes.
- I am using the node-red-node-mysql node.

I have an inject with a timestamp, then a function that grabs the data. Then should I do the insert with the query?

Thank you

---

<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:** [19 August 2020 14:21 UTC](https://discourse.nodered.org/t/flow-to-insert-into-mysql-table/31712/4 "2020-08-19T14:21:32Z")

</div>

Did you read the info/data tab or readme for the node? If you did you would have seen

> `msg.topic` must hold the _query_ for the database, and the result is returned in `msg.payload`

I suggest you use the `template` node to build the query and attach a `debug` node (set to display the complete msg object) to look at the query you have created.

---

<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:** [19 August 2020 17:59 UTC](https://discourse.nodered.org/t/flow-to-insert-into-mysql-table/31712/5 "2020-08-19T17:59:10Z")

</div>

Here is a series of flows showing different ways of building the sql query. Like I said, I think the `template` is the easiest to understand and maintain.  
 ![Screen Shot 2020-08-19 at 1.58.14 PM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/7/574fb7366203b4a8f3d25f8577a093bb1e5ff7e4.png)

```auto
[{"id":"7eff76b7.648cc","type":"inject","z":"5738bb79.b53c54","name":"use function to build query","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"","payloadType":"date","x":150,"y":180,"wires":[["7bf25c4d.aed4d4"]]},{"id":"2af18bd.c680874","type":"change","z":"5738bb79.b53c54","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"select * from wp_users","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":400,"y":100,"wires":[["865b0a72.cee8d"]]},{"id":"865b0a72.cee8d","type":"debug","z":"5738bb79.b53c54","name":"","active":true,"console":"false","complete":"true","x":570,"y":100,"wires":[]},{"id":"7bf25c4d.aed4d4","type":"function","z":"5738bb79.b53c54","name":"","func":"var newMsg = { payload: msg.payload };\nnewMsg.topic=\"select * from wp_users where 'user_login' = 'xxx'\";\nreturn newMsg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":380,"y":180,"wires":[["d83461de.a50cd8"]]},{"id":"a6ac1a5c.60ae58","type":"inject","z":"5738bb79.b53c54","name":"using a change node","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"","payloadType":"date","x":140,"y":100,"wires":[["2af18bd.c680874"]]},{"id":"4bc4e723.2643b8","type":"function","z":"5738bb79.b53c54","name":"","func":"data = (msg.payload);\nvar m = {topic : \"SELECT * FROM `wp_users` WHERE `user_login` = '\" + data + \"'\"} ;\nreturn m;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":380,"y":260,"wires":[["1b9812c8.c0e44d"]]},{"id":"5b30772e.ed772","type":"inject","z":"5738bb79.b53c54","name":"using msg.payload in function","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"xxxxxx","payloadType":"str","x":160,"y":260,"wires":[["4bc4e723.2643b8"]]},{"id":"d83461de.a50cd8","type":"debug","z":"5738bb79.b53c54","name":"","active":true,"console":"false","complete":"true","x":570,"y":180,"wires":[]},{"id":"1b9812c8.c0e44d","type":"debug","z":"5738bb79.b53c54","name":"","active":true,"console":"false","complete":"true","x":570,"y":260,"wires":[]},{"id":"9c5e2c13.85384","type":"inject","z":"5738bb79.b53c54","name":"use msg.payload in template","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"xxxxxx","payloadType":"str","x":160,"y":340,"wires":[["b1e310ba.00966"]]},{"id":"b1e310ba.00966","type":"template","z":"5738bb79.b53c54","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT * FROM 'wp_users' \n WHERE 'user_login` = '{{{payload}}}'","output":"str","x":380,"y":340,"wires":[["1a618d24.903deb"]]},{"id":"1a618d24.903deb","type":"debug","z":"5738bb79.b53c54","name":"","active":true,"console":"false","complete":"true","x":570,"y":340,"wires":[]}]

```

---

<div class="post-metadata">

**Author:** ![thagan](https://avatars.discourse-cdn.com/v4/letter/t/3d9bf3/32.png) [@thagan](https://discourse.nodered.org/u/thagan)\
**Post date:** [20 August 2020 19:06 UTC](https://discourse.nodered.org/t/flow-to-insert-into-mysql-table/31712/6 "2020-08-20T19:06:50Z")

</div>

Paul,

Got it working!!! Thank you so much. Using the template node is what did it. Thank you so much. Here is my flow as it may help others.

````auto
[{"id":"30aa701b.43cd2","type":"inject","z":"6dc61293.4fca5c","name":"","topic":"","payload":"","payloadType":"date","repeat":"60","crontab":"","once":true,"onceDelay":"60","x":270,"y":380,"wires":[["6ed6806a.c9ab5"]]},{"id":"6ed6806a.c9ab5","type":"pac-read","z":"6dc61293.4fca5c","device":"dba83a6d.a23ee8","dataType":"float-table","tagName":"StorageData","tableStartIndex":"","tableLength":"","value":"StorageData","valueType":"msg","topic":"","topicType":"none","name":"","x":620,"y":340,"wires":[["d304211b.1ef87"]]},{"id":"d304211b.1ef87","type":"function","z":"6dc61293.4fca5c","name":"StorageData","func":"msg.Relay1=msg.StorageData[0];\nmsg.Relay2=msg.StorageData[1];\nmsg.Relay3=msg.StorageData[2];\nmsg.Relay4=msg.StorageData[3];\nmsg.BatteryVolts=msg.StorageData[4];\nmsg.BatteryAmps=msg.StorageData[5];\nmsg.PVVolts=msg.StorageData[6];\nmsg.PVAmps=msg.StorageData[7];\nmsg.LoadVoltage=msg.StorageData[8];\nmsg.LoadAmps=msg.StorageData[9];\nmsg.CurrentChannel1A=msg.StorageData[10];\nmsg.CurrentChannel2A=msg.StorageData[11];\nmsg.CurrentChannel3A=msg.StorageData[12];\nmsg.CurrentChannel4A=msg.StorageData[13];\nmsg.CurrentChannel5A=msg.StorageData[14];\nmsg.CurrentChannel6A=msg.StorageData[15];\nmsg.CurrentChannel7A=msg.StorageData[16];\nmsg.CurrentChannel8A=msg.StorageData[17];\nmsg.CurrentChannel1B=msg.StorageData[18];\nmsg.CurrentChannel2B=msg.StorageData[19];\nmsg.CurrentChannel3B=msg.StorageData[20];\nmsg.CurrentChannel4B=msg.StorageData[21];\nmsg.Voltage1=msg.StorageData[22];\nmsg.Voltage2=msg.StorageData[23];\nmsg.Relay5=msg.StorageData[24];\nmsg.Relay6=msg.StorageData[25];\nmsg.Relay7=msg.StorageData[26];\nmsg.Relay8=msg.StorageData[27];\nmsg.Volt_Battery2=msg.StorageData[28];\nmsg.Amp_Battery2=msg.StorageData[29];\nmsg.Volt_PV2=msg.StorageData[30];\nmsg.Amp_PV2=msg.StorageData[31];\nmsg.Load_Voltage2=msg.StorageData[32];\nmsg.RelayRandom=msg.StorageData[33];\nreturn msg;","outputs":1,"noerr":0,"x":850,"y":320,"wires":[["c20fc0ec.6240e"]]},{"id":"30bead8c.018402","type":"debug","z":"6dc61293.4fca5c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1450,"y":320,"wires":[]},{"id":"c20fc0ec.6240e","type":"template","z":"6dc61293.4fca5c","name":"test","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"insert into OptoData\n(Relay1\n ,Relay2\n ,Relay3\n ,Relay4\n ,BatteryVolts\n ,BatteryAmps\n ,PVVolts\n ,PVAmps\n ,LoadVoltage\n ,LoadAmps\n ,CurrentChannel1A\n ,CurrentChannel2A\n ,CurrentChannel3A\n ,CurrentChannel4A\n ,CurrentChannel5A\n ,CurrentChannel6A\n ,CurrentChannel7A\n ,CurrentChannel8A\n ,CurrentChannel1B\n ,CurrentChannel2B\n ,CurrentChannel3B\n ,CurrentChannel4B\n ,Voltage1\n ,Voltage2\n ,Relay5\n ,Relay6\n ,Relay7\n ,Relay8\n ,Volt_Battery2\n ,Amp_Battery2\n ,Volt_PV2\n ,Amp_PV2\n ,Load_Voltage2\n ,RelayRandom)\n Values\n ({{{Relay1}}}\n ,{{{Relay2}}}\n ,{{{Relay3}}}\n ,{{{Relay4}}}\n ,{{{BatteryVolts}}}\n ,{{{BatteryAmps}}}\n ,{{{PVVolts}}}\n ,{{{PVAmps}}}\n ,{{{LoadVoltage}}}\n ,{{{LoadAmps}}}\n ,{{{CurrentChannel1A}}}\n ,{{{CurrentChannel2A}}}\n ,{{{CurrentChannel3A}}}\n ,{{{CurrentChannel4A}}}\n ,{{{CurrentChannel5A}}}\n ,{{{CurrentChannel6A}}}\n ,{{{CurrentChannel7A}}}\n ,{{{CurrentChannel8A}}}\n ,{{{CurrentChannel1B}}}\n ,{{{CurrentChannel2B}}}\n ,{{{CurrentChannel3B}}}\n ,{{{CurrentChannel4B}}}\n ,{{{Voltage1}}}\n ,{{{Voltage2}}}\n ,{{{Relay5}}}\n ,{{{Relay6}}}\n ,{{{Relay7}}}\n ,{{{Relay8}}}\n ,{{{Volt_Battery2}}}\n ,{{{Amp_Battery2}}}\n ,{{{Volt_PV2}}}\n ,{{{Amp_PV2}}}\n ,{{{Load_Voltage2}}}\n ,{{{RelayRandom}}})","output":"str","x":1070,"y":320,"wires":[["e92b4a92.2871c8"]]},{"id":"e92b4a92.2871c8","type":"mysql","z":"6dc61293.4fca5c","mydb":"757c2973.6e8988","name":"mysql","x":1250,"y":320,"wires":[["30bead8c.018402"]]},{"id":"dba83a6d.a23ee8","type":"pac-device","z":"","address":"192.168.1.50","protocol":"http"},{"id":"757c2973.6e8988","type":"MySQLdatabase","z":"","name":"mysql","host":"192.168.254.83","port":"3306","db":"OPTO","tz":""}]```
````

---

<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:** [3 September 2020 19:06 UTC](https://discourse.nodered.org/t/flow-to-insert-into-mysql-table/31712/7 "2020-09-03T19:06:56Z")

</div>

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