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

born2see · December 31, 2017, 7:23pm

I’m trying to take a bunch of values from a PAC table and write each value as a separate record in a MS SQL database. How do I separate the values or reference them individually? Here’s what I’ve got:

The msg.PlannedHours is obviously an array (I guess). NR data types are a mystery to me. In any case, seems to me I could loop through them and insert them into my SQL table, one at a time, and that would give me my result. However I don’t know how to reference them individually. I’m sure there’s some kind of syntax I’m not aware of.

Thanks,

Dave

Beno · December 31, 2017, 7:47pm

Dave,
Take a look at the split node.
Just drop it between your groov node and your debug node in this screen shot.
It will take the table and split it into separate payloads.

Also, don’t forget the tip to put the debug node into ‘full message payload’ mode, it can help show all the data, rather than just msg.payload.

Stick with it, I am also at that same place where I am sometimes just not sure about the data types… We have actually spent the past 2 days playing with Node-RED at home, getting lots of cool stuff done, but sometimes just feel disconnected, but when it clicks, its pretty sweet.

born2see · January 1, 2018, 4:36pm

Thanks Ben. So far, so good. My next question is how do I reference an individual element in my INSERT statement?

Beno · January 1, 2018, 6:03pm

Dave, sorry, I don’t fully understand your question… You want to do a select now? (Reference).
Can you give us a screen shot (snip) of the bit you mean?

born2see · January 1, 2018, 7:07pm

So here’s what I want to do. I want to take this table:

And I want to insert it into my MS SQL server database just like it is: Each row being a record.

Here is what I’ve got so far:

So I want to either do a SQL INSERT command for the whole array or loop through these values and INSERT them one at a time. If this was Optoscript I would do something like msg.payload[x] to reference each one of my table elements. However, from what I can tell you can’t do that kind of reference in transact-sql. So my question is how do I do this? I see where it can be done by declaring each element but that doesn’t work when you have hundreds of data items. This table I’m referring to is just a sample.

Beno · January 2, 2018, 2:27am

I am going have to take a pause here, I have never used the mssql node…

Anyone?

born2see · January 2, 2018, 3:05am

So how about if I move to MySQL? In this post: SNAP PAC and groov Nodes - #24 by Beno it appears you’re inserting an array into a MySQL database. If that works, I’m all for it. I just can’t declare each variable. My whole application is written in arrays, and if I have any hope of being able to do any kind of meaningful reporting of all the data we;re collecting, I need to be able to automate the process of inserting it into a database.

Dave

philip · January 2, 2018, 6:09pm

If you are using MSSQL 2008+ you can perform a multi-row insert which looks like this for a single column table:
insert into tblData1 (Item) values (15), (12), (10);
If you have multiple columns to insert:
insert into tblData1 (Item1, Item2) values (15, 10), (12, 7), (10, 5);

So after your split node, you can place a template node with the following simple template:

({{{payload}}})

This will generate (x) for each item in your array, for example (15)

You then can place a join node to concatenate all these together - set the join node to manual and to create a string joined using a comma.

The payload will now be (15),(12),(10)…etc

Then in your mssql node, place the final insert template:

insert into dbo.tblData1 (Item) values {{{payload}}};

If you are using an older MS SQL, then you will need to perform each insert separately. Unfortunately the node-red-contrib-mssql node will give you errors if you try to use it asynchronously (https://github.com/redconnect-io/node-red-contrib-mssql/issues/12), so you will need to build a loop with nodes so the inserts are performed synchronously (see the while pattern on this site: Node-RED Design Patterns. When programming object oriented code… | by Stephen Keep | Node RED | Medium).

If you get stuck, post your flow and we can try to help you out.

born2see · January 3, 2018, 5:05am

Brother Philip, you are a GENIUS!!! If you only knew how many hours I spent combing the web, installing different platforms, looking at endless lines of complicated JavaScript and SQL queries no mere mortal could comprehend. I was done.

I can’t thank you enough.

Here is my flow:

Here is the result:

born2see · January 6, 2018, 6:47pm

I thought I would share this for anyone who wants to try this with MySQL. Don’t really know how to do any of this but this is what I changed to make it work from what you see above. If someone else has a better way of doing this, please let me know.

First you have to create a template node and change its type to msg.topic to put in your query. You also have to modify the query for the MySQL syntax.

The other thing you have to do is remove the Join node. Otherwise MySQL thinks your inserting a single record with the amount of columns equal to the length of your table (I hope I’m explaining that right). If you don’t you get the error “column count doesn’t match the value count at row 1”.

Here is my modified flow:

I have a permanent flat spot on my forehead from beating my head against the wall. It’s not pretty, so I hope this helps some folks out there.

Dave

philip · January 8, 2018, 5:54pm

MySQL should support the same multi-row insert that MSSQL 2008+ does. By removing the join, you are sending and insert command for each row separately which will have a performance penalty - but it will still work so if if performance is okay, you should be fine.

born2see · January 9, 2018, 4:00am

I’m trying to insert 3 records of two columns into MySQL. The data type for both columns is a varchar[25].

So here is my flow:

From the looks of it I have three msg.payload of two array elements. So trying to mimic what I did before to make this work I used the template node and put ({{{payload}}}) in it. However when I do that, it turns it into a string which of course doesn’t work:

Here is my error from the server.

Here is my INSERT statement:

I’ve tried every permutation I can think of but I’m stumped again. Thanks for the help.

Dave

philip · January 9, 2018, 4:46am

Since you are sending a varchar, you need to surround each value in quotes/apostrophes. So you will end up with (‘SL2-1-V1’, ‘31’). You should then be able to perform a muli-row insert.

born2see · January 9, 2018, 2:37pm

So how do I do that?

Here is my flow;

I guess my question is how do I get this:

to this, which works:

philip · January 9, 2018, 5:41pm

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 , 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!

born2see · January 9, 2018, 7:59pm

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:

I’m only pulling the first 6

philip · January 9, 2018, 8:13pm

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

philip · January 9, 2018, 8:20pm

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.

born2see · January 9, 2018, 8:57pm

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

nmcdonald · February 15, 2018, 2:55pm

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.