SNAP PAC and groov Nodes

Using a function block with the following:

var fTbl_IO = msg.payload;
msg.payload = {};

msg.payload.tempc = fTbl_IO[1];
msg.payload.tempf = fTbl_IO[2];
msg.payload.tempk = fTbl_IO[3];
msg.payload.description = fTbl_IO[4];
msg.payload.weather = fTbl_IO[5];
msg.payload.humidity = fTbl_IO[6];
msg.payload.windspeed = fTbl_IO[7];
msg.payload.winddirection = fTbl_IO[8];
msg.payload.location = fTbl_IO[9];
msg.payload.forecast = fTbl_IO[10];
msg.payload.epoch = fTbl_IO[11];

return msg;

and SQL as follows:

INSERT INTO dbo.Table1 (epoch,
 description,
 weather,
 tempc,
 tempf,
 tempk,
 humidity,
 windspeed,
 winddirection,
 location,
 forecast)
VALUES ({{{payload.epoch}}},
 {{{payload.description}}}, 
 {{{payload.weather}}},
 {{{payload.tempc}}}, 
 {{{payload.tempf}}}, 
 {{{payload.tempk}}}, 
 {{{payload.humidity}}},
 {{{payload.windspeed}}},
 {{{payload.winddirection}}},
 {{{payload.location}}},
 {{{payload.forecast}}});

should get you pretty close.

Also, in the MSSQL node using mustache, if the variable doesn’t exist, then mustache will put in nothing - this can result in a syntax error on your sql node.

1 Like

Right on the money, Phil! Function Block for the win. And that’s the beauty of the FB; you can do just about anything Javascript can do in that block and pass the results on in the flow.

Thanks for your help on this. -Benson

Eureka! This is wonderful. It works. It seems that the key was you declaring the input from the SNAP PAC as a variable in the beginning of your function node.

I had tried a similar approach using global.context but the result was never correct. Thank you guys so much for your support!

@Beno

Just wanted to thank you again for your original post.

Just recently I came into an application that needed to be made using MYSQL and your syntax works wonders. There isn’t a lot of documentation on Node-RED syntax, so that tends to be what I struggle with most.

Thanks again.

I’m struggling to get mine to work even after reading this thread. So here’s my Function node:

Works great, I think… They’re all strings, being pulled from a string table in PAC. When I hook up a Debug node all the values look good.

So here’s my mssql node:

When I put numbers in the table these values are coming from, I get an “undefined” error but it writes my table correctly. When I put text in the same table I get this error:

I figure I’m not declaring something right but after a couple of hours, I’ve given up and thrown myself on the mercy of the forum.

EDIT: So the problem seems to be the string data type between what I’m declaring in the flow and what’s in the SQL database. When I make them all integers it works fine. Don’t know if that helps.

Thanks,

Dave.

I got it to work. Benson saved the day. The issue was the conversion from a UTC string to one for MS SQL. I used the node in his sample flow and all is good.

Great end to a long day. :slight_smile:

Dave

1 Like

Shaweet. Thanks for closing the loop and letting us know that you got it going and what fixed it.

Hi Guys,
I cannot get mine to work! I am doing the same thing as above but I cannot getting anything to write to the database. I have tried everything! What am I doing wrong?
Here’s the function code:

var fTbl_IO = msg.payload;
msg.payload = {};

msg.payload.tempc = fTbl_IO[0];
msg.payload.tempf = fTbl_IO[1];
return msg;

And the code in the MSSQL Node:

INSERT INTO db_owner.MachineDATA (tempc, tempf)
VALUES ({{{payload.tempc}}}, {{{payload.tempf}}});

Attached is my DB setup. Anybody have any suggestions??

DB

Can you post the output of the msg.payload prior to the function node.

Also, I have since learned that mustache templates do support arrays (it just isn’t in the documentation). To access array elements you use a period and the index like: {{{payload.3}}}

So you could skip the function node if you like and use:
INSERT INTO db_owner.MachineDATA (tempc, tempf)
VALUES ({{{payload.0}}}, {{{payload.1}}});

This won’t fix your problem though - need to see what is in the payload.

Yes, here is the debug output before the Function and after the function. (see Pic)
I do not get any errors, so weird!?!
Output

That looks good to me, what is the output from the SQL node - any errors?

No errors on output. Actually nothing even shows up in debug. When I run the query to select anything from my columns, nothing is there and message says "0 rows effected."
What else can I look for?

I changed my SQL node to: VALUES ({{{payload.0}}}, {{{payload.1}}});
Still nothing and no errors in debug. What else can I share?

Open the debug Node and change the output to be ‘complete msg object’.
Redeploy and fire it again. See if you see something then.

I changed to ‘complete message object’ and still completely blank in debug. I should see something there right?
Here’s my DB connection in the MSSQL node. My usr name and password are correct, the address is correct.
Output

Have you watched this?

If this is Azure, check the Use Encryption box.

The Opto Video is what got me started on this. I’ve studied it several times. If I check the encryption box, I do get an error in debug: "ConnectionError: Windows logins are not supported in this version of SQL Server."
I think it is Azure, so why would this not work?

Azure only uses SQL server logins - I think you need to remove the domain

Ugh! LOL, are you kidding me? I removed the domain and it works great! Sheesh! Thank you Guys!