Flow to insert into MySQL table

[thagan]

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.

[{"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"}]

[zenofmud]

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

[thagan]

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

[zenofmud]

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.

[zenofmud]

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.

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

[thagan]

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.

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