I think Philip is better suited to answer this than I am since he made mine work. But if we don’t hear from him I’m happy to post mine. The only thing is mine is for MS SQL not MySQL. Having said that, the difference is probably only syntax.
I think Philip is better suited to answer this than I am since he made mine work. But if we don’t hear from him I’m happy to post mine. The only thing is mine is for MS SQL not MySQL. Having said that, the difference is probably only syntax.
Have you tried separating each of your statements with a semicolon and sending that to your SQL node?
Yes I have. Still did not work.
I checked the source for node-red-node-mysql and it looks to have multiple statements queries enabled, so it should work - can you post the sql that you are sending to the node?
msg.topic = “insert into Zone1 (Temp, CO2 ,RH) values (” +
msg.payload.tCurTemp0 + ", " + msg.payload.tCurHumidity0 + ", " +
msg.payload.tCurCO20 + “)” + “insert into Zone2 (Temp, CO2 ,RH) values (”
return msg;
I’m not seeing any semicolons being put in after each insert statement.
Strong recommendation: Consider changing your database design/schema to have a single table with the zone # as a column. It will make life much easier for you when querying your data.
Yeah I took them out to try it without them after I tried using them. I dont have the option to set them as just separate columns because there already are multiple columns in each table. If you mean by adding the Name of the Zone to each column etc, that will not work either. Our customers want it this specific way.
You will need the semicolons. Did the mysql node generate an error that would help track down the issue? Can you post the generated sql (after this function) for us to look at?
I don’t understand what you are saying here. Name of the zone on each column is not what I am saying. This is what I am saying:
insert into Zone (Zone, Temp, CO2, RH) values (1, 72, 500, 55);
One table for all the zones. When your customer adds zone 324 and you need the data:
select * form Zone where Zone=324;
No additional table needed. If the customer insists on a “zone table” then create a view:
create view Zone1 as
select * form Zone where Zone=1; --List the columns out, don't use the * when creating a view though.
What you have now is a bad design that will cause more trouble down the road. Imagine what you would have to do if your customer requested a report of all the zone temperatures from yesterday. You will need to write a separate query for each zone. If you do it the right way, then this is simple:
select Zone, Temp from Zone where EntryDate = '2/14/2018'; --If you have a EntryDate column
Hello,
Following up on this thread. I have almos the same situation in that I have 2 arrays coming from PAC and I need these arrays to be inserted into a MSSQL DB. The instructions provided between phillip and Born2see works fine for just one array, but how would the process be for 2 arrays doing a multi row insert to SQL?
Are the two arrays from the PAC going into different columns of the same table?
Here is an example of inserting two arrays into different columns of the same table. It is assumed the arrays are the same length. You can add additional arrays/columns as needed.
The sequence is:
*If inserting string data from the PAC that could come from the end user, then look up and understand what an SQL injection attack is. Also, your string data will need surrounded by ‘apostrophes’ in the “Build SQL value list” node.
Flow:
[{"id":"26198e86.a487aa","type":"debug","z":"8d6f74c8.a53ad8","name":"","active":false,"console":"false","complete":"true","x":493,"y":212,"wires":[]},{"id":"382a8fd4.ead828","type":"function","z":"8d6f74c8.a53ad8","name":"Move payload to msg","func":"msg = msg.payload;\nmsg.payload = 1517425818148;\n\nreturn msg;","outputs":1,"noerr":0,"x":334,"y":263,"wires":[["26198e86.a487aa","2f590c9b.a5dbac"]]},{"id":"68ef6875.56e55","type":"function","z":"8d6f74c8.a53ad8","name":"Transform Data","func":"var data = [];\n\nfor (var i = 0; i< msg.ntColumn1.length; i++)\n{\n\tdata.push({\n\t\t\t\"Column1\" : msg.ntColumn1[i],\n\t\t\t\"Column2\" : msg.ntColumn2[i]\n\t\t\t});\n}\n\nmsg.payload = data;\n\nreturn msg;","outputs":"1","noerr":0,"x":197,"y":379,"wires":[["14256041.446a68","be85238.d0afc6"]]},{"id":"14256041.446a68","type":"debug","z":"8d6f74c8.a53ad8","name":"","active":false,"console":"false","complete":"false","x":360,"y":332,"wires":[]},{"id":"66e195fb.73e5cc","type":"split","z":"8d6f74c8.a53ad8","name":"","splt":"\\n","x":156,"y":489,"wires":[["34eb0535.813e7a"]]},{"id":"34eb0535.813e7a","type":"template","z":"8d6f74c8.a53ad8","name":"Build SQL value list","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"({{payload.Column1}},{{{payload.Column2}}})\n","x":321,"y":489.3333740234375,"wires":[["c726ab36.6e99d8"]]},{"id":"c726ab36.6e99d8","type":"join","z":"8d6f74c8.a53ad8","name":"","mode":"custom","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":",","timeout":"","count":"","x":483,"y":489.66668701171875,"wires":[["f6792a0e.6539c8"]]},{"id":"f6792a0e.6539c8","type":"template","z":"8d6f74c8.a53ad8","name":"Create SQL Statement","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"insert into MyTable ([Column1], [Column2]) values \n{{{payload}}};","x":655.9999389648438,"y":490.16668701171875,"wires":[["d5079ceb.68efb8"]]},{"id":"d5079ceb.68efb8","type":"debug","z":"8d6f74c8.a53ad8","name":"Send to your DB","active":true,"console":"false","complete":"payload","x":703,"y":424,"wires":[]},{"id":"c097c.18fc7685","type":"inject","z":"8d6f74c8.a53ad8","name":"Test Data","topic":"","payload":"{\"ntColumn1\":[0,1,2,3,4,5,6,7,8,9],\"ntColumn2\":[0,10,20,30,40,50,60,70,80,90]}","payloadType":"json","repeat":"","crontab":"","once":false,"x":141,"y":263,"wires":[["382a8fd4.ead828"]]},{"id":"f50a54ff.bc78","type":"comment","z":"8d6f74c8.a53ad8","name":"Connect wire to enable","info":"","x":212,"y":109,"wires":[]},{"id":"d9609ba2.832488","type":"pac-read","z":"8d6f74c8.a53ad8","device":"93a5787a.9451b","dataType":"int32-table","tagName":"ntColumn1","tableStartIndex":"0","tableLength":"9","value":"ntColumn1","valueType":"msg","topic":"","topicType":"none","name":"ntColumn1","x":312,"y":152,"wires":[["fce3eb43.36301","2774f2b9.d0509e"]]},{"id":"fce3eb43.36301","type":"pac-read","z":"8d6f74c8.a53ad8","device":"93a5787a.9451b","dataType":"int32-table","tagName":"ntColumn2","tableStartIndex":"0","tableLength":"9","value":"ntColumn2","valueType":"msg","topic":"","topicType":"none","name":"ntColumn2","x":516,"y":152,"wires":[["5b4249b1.7047c8","118cf47d.aaf4f4"]]},{"id":"fe77aca6.4644a8","type":"inject","z":"8d6f74c8.a53ad8","name":"Timer Inject","topic":"","payload":"[]","payloadType":"json","repeat":"30","crontab":"","once":true,"x":149,"y":152,"wires":[[]]},{"id":"914aae04.2dc878","type":"comment","z":"8d6f74c8.a53ad8","name":"Get data from strategy","info":"Note that the End Time table contains\none extra element that is used for \nsyncronization with the strategy to make \nsure the strategy is done writing to \nthe table.","x":115,"y":64,"wires":[]},{"id":"118cf47d.aaf4f4","type":"link out","z":"8d6f74c8.a53ad8","name":"To Data Process","links":["1b32fb1f.77678d"],"x":656,"y":152,"wires":[]},{"id":"7d2208ac.08d5b8","type":"comment","z":"8d6f74c8.a53ad8","name":"Debug/Test injections","info":"","x":110,"y":207,"wires":[]},{"id":"2f590c9b.a5dbac","type":"link out","z":"8d6f74c8.a53ad8","name":"To Data Process","links":["1b32fb1f.77678d"],"x":518,"y":262,"wires":[]},{"id":"1b32fb1f.77678d","type":"link in","z":"8d6f74c8.a53ad8","name":"Data Process","links":["2f590c9b.a5dbac","118cf47d.aaf4f4"],"x":63,"y":379,"wires":[["68ef6875.56e55"]]},{"id":"b5602f0f.29bcf8","type":"comment","z":"8d6f74c8.a53ad8","name":"Process Data","info":"","x":80,"y":330,"wires":[]},{"id":"2c6945d8.b3bfd2","type":"link in","z":"8d6f74c8.a53ad8","name":"Process for SQL Server","links":["be85238.d0afc6"],"x":62,"y":488,"wires":[["66e195fb.73e5cc"]]},{"id":"be85238.d0afc6","type":"link out","z":"8d6f74c8.a53ad8","name":"To Process for SQL Server","links":["2c6945d8.b3bfd2"],"x":349,"y":379,"wires":[]},{"id":"c79f316.628cdd","type":"comment","z":"8d6f74c8.a53ad8","name":"Build SQL Statement and send","info":"","x":140,"y":431,"wires":[]},{"id":"2774f2b9.d0509e","type":"debug","z":"8d6f74c8.a53ad8","name":"","active":false,"console":"false","complete":"true","x":373,"y":61,"wires":[]},{"id":"5b4249b1.7047c8","type":"debug","z":"8d6f74c8.a53ad8","name":"","active":false,"console":"false","complete":"true","x":538,"y":62,"wires":[]},{"id":"93a5787a.9451b","type":"pac-device","z":"","address":"192.168.0.100","protocol":"http"}]Yes, the two arrays are of the same length and going to the same table.
I will try out the code that you provided and reply soon
Hello there again, I have successfully implemented the flow and adapted it to my application.
Thank you again for the support!
Hello, I’m trying to insert data into an MSSQL DB with the example of philip.
I added the MS SQL node at the end of the flow.
Here the settings:
But this is the result:
Thanks in advance for the support.
Welcome to the forums!
Can you confirm that your table is created, viewable, and called “MyTable” exactly?
It may be helpful if you paste a screenshot of your configuration node. Make sure the query mode set to Query and not Bulk Insert.
Yes, I confirm that.
In fact replacing {{{payload}}} with (10, 100) everything works fine
The configuration node for the connection is what I meant.
Does replacing it with (10, 100), (20, 200) work?
Is this a really old SQL Server version?
Yes, it works.
SQL Server 2014 Express.
