How to Insert Table Values Into MS SQL and MySQL as Separate Records

So how do I do that?

Here is my flow;

I guess my question is how do I get this:

to this, which works:

image

You will need to move each item into its own parameter, since mustache templates don’t support arrays. (Edit: This is wrong, see edit at bottom) So add a function node after the split node and enter the following javascript:

var payload = {};
payload.col1 = msg.payload[0];
payload.col2 = msg.payload[1];

msg.payload = payload;

return msg;

This will allow you to modify the template node like this:

("{{{payload.col1}}}","{{{payload.col2}}}")

You can then perform the join node for a multi-row insert if you would like.

Edit:

I was wrong :blush:, mustache can access array indexes.

So you don’t need the function node, you can just change the template node as follows:

("{{{payload.0}}}","{{{payload.1}}}")

To access index items, you just use a dot and the index #. Strange design, but I learned something new today!

1 Like

Unfortunately, I’m not getting the same result you are. Here is what I’m pulling from my table:

This is what I end up with:

Here is my table if it helps:
image

I’m only pulling the first 6

From above - you had them in arrays of two elements - do you remember how you did that?

Try adding a function node before the split and inserting the following javascript:

data = msg.payload;
var newdata = [];

for(i=0; i < data.length; i+=2)
{
  newdata[i/2] = [data[i], data[i+1]];
}

msg.payload = newdata;

return msg;

That will convert the single array to multiple arrays of pairs.

1 Like

Success!!! Again, I can’t thank you enough! I now I have a template I can modify moving forward. I just changed the Function node to add a third column and it worked fine, You can probably see that I’m using a single column table and turning it into a mutli-dimensional array by grabbing groups of table elements and inserting them as rows into my database. A little klugy but it’s the only way I know to do that in my strategy. I think it was Opto Mary who posted that particular trick and it works great.

Thanks again,

Dave

1 Like

Is there any chance you can show what your finished code is like? I have a similar situation going in. I have mysql setup to send multiple values to a database but currently I have to make separate connections to each table meaning I have to have multiple SQL nodes instead of one.

I want to beable to make one large sql statement and send the info to multiple tables through one singular connection.

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?

1 Like

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 (”

  • msg.payload.tCurTemp1 + ", " + msg.payload.tCurHumidity1 + ", " +
    msg.payload.tCurCO21 + “)”
  • “insert into Zone3 (Temp, CO2 ,RH) values (” + msg.payload.tCurTemp2 +
    ", " + msg.payload.tCurHumidity2 + ", " + msg.payload.tCurCO22 + “)”
  • “insert into Zone4 (Temp, CO2 ,RH) values (” + msg.payload.tCurTemp3 +
    ", " + msg.payload.tCurHumidity3 + ", " + msg.payload.tCurCO23 + “)”
  • “insert into Zone5 (Temp, CO2 ,RH) values (” + msg.payload.tCurTemp4 +
    ", " + msg.payload.tCurHumidity4 + ", " + msg.payload.tCurCO24 + “)”
  • “insert into Zone6 (Temp, CO2 ,RH) values (” + msg.payload.tCurTemp5 +
    ", " + msg.payload.tCurHumidity5 + ", " + msg.payload.tCurCO25 + “)”
  • “insert into Zone7 (Temp, CO2 ,RH) values (” + msg.payload.tCurTemp6 +
    ", " + msg.payload.tCurHumidity6 + ", " + msg.payload.tCurCO26 + “)”
  • “insert into Zone8 (Temp, CO2 ,RH) values (” + msg.payload.tCurTemp7 +
    ", " + msg.payload.tCurHumidity7 + ", " + msg.payload.tCurCO27 + “)”
  • “insert into Zone9 (Temp, CO2 ,RH) values (” + msg.payload.tCurTemp8 +
    ", " + msg.payload.tCurHumidity8 + ", " + msg.payload.tCurCO28 + “)”;

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

2 Likes

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:

  1. Get all the array data from the PAC (I simulate this with the “Test Data” inject node). *
  2. Transform the data into a new array of javascript objects that represent all our data. If you have other data you want to include with your insert, this is the place to add it.
  3. Split the array so we can build a SQL statement.
  4. Build our value statement portion of our insert using the data in the javascript object we created.
  5. Join our value statements together.
  6. Append our value statements into a full SQL insert statement.
  7. Send the insert to the database - This insert is setup for MSSQL, you may need to adjust the syntax for other DBs.

*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"}]
1 Like

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!