SNAP PAC and groov Nodes


#21

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


#22

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.


#23

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


#24

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


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

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


#26

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.


#27

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


#28

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


#29

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?


#30

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


#31

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


#32

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


#33

Have you watched this?


#34

If this is Azure, check the Use Encryption box.


#35

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?


#36

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


#37

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