SNAP PAC and groov Nodes

Hello All,

I need a little bit of information about SNAP PAC and groov Nodes. I’ve been using Node-RED in different ways to interact with MSSQL databases and it’s worked great so far, but I’m having issues putting information from groov and SNAP-PAC nodes into the same database. It seems like the values no matter how I format them are coming up as Nulls in the database.

What format are the SNAP PAC and groov nodes outputing their data as?

Short answer, it depends.

Long answer… WARNING. All that follows is for MySQL. I have not used the MSSQL node, but I hear the DB’s are sort of similar, so offer it in the hope it will help.

Both PAC and NR will send data as either a string, Int32 or Float.
It will be up to you as the programmer to chose and do any type conversion (if necessary).
So for example, if I am getting the weather from weatherunderground API, the city comes back as a string, the temperature comes back as a float, the chance of rain comes back as an int.
Makes no difference if I do that API call from a PAC or from Node-RED, that’s the way the data is formatted when it comes back from the call.

So, to put all that in a MySQL node and do the insert statement in a function block, you will need to handle it accordingly.
I am probably waaaaay wrong on this, so please, any database experts reading this, please send your flames my way and teach me the error of my ways… BUT, what I do in MySQL is to set everything up as a ‘varchar’, float or int.
At the end of the day, you are the programmer, you will need to know what sort of data you are working with in the flow.

Note, I am a super crappy/lazy/efficient programmer, so I sometimes just set MySQL tables to be all varchars and thus I never have to think or worry about that end of it, I just have to manage the insert statement… which is the next step (and yes, we are going backwards here, but for a reason).

Ok, so here is a screen shot of all varchars.

And here is one where I have been a little more careful.

You get the idea. Now, the Node-RED insert…
Ints and floats can just be inserted with the usual statement, strings HAVE to be singled quoted.
This gets a tiny bit frustrating, but with some careful keeping track, you can get the data in the format the insert requires.
Here is a typical insert statement from a Function Node for a pair of float inserts;
msg.topic = "INSERT INTO solar (solargti, housepower) VALUES(" + msg.gtiw +","+ msg.payload +")";

Here is an insert for a mix of floats, ints and strings;
msg.topic = "INSERT INTO strava (type, title, duration, distance, calories, starttime, gear, gear_dist, elevation, id, descrip, city, state,country) VALUES('"+msg.payload[0]+"'"+","+"'"+ msg.payload[1]+"'" +","+ msg.payload[2] +","+ msg.payload[3] +","+ msg.payload[4] +","+ "'"+msg.payload[5]+"'" +","+ "'"+ msg.payload[6] +"'" +","+ msg.payload[7] +","+ msg.payload[8] +","+ msg.payload[9] +","+"'"+msg.payload[10]+"'"+","+"'"+msg.payload[11]+"'"+","+"'"+msg.payload[12]+"'"+","+"'"+msg.payload[13]+"'"+")";
return msg;

The thing to notice here is that the method of inserting ints and floats is the same, just the strings are single quoted.
So that’s, 0, 1, 5, 6, 10,11,12 and 13.

Lets know if that helps. I am interested in how similar the MySQL and MSSQL nodes are.

1 Like

Great Explanation.

I tried using your insert but I keep throwing up a syntax error

RequestError: Incorrect syntax near ’ +’.

What are all the plus signs and the quotation marks for? They seem randomly scattered through out the values.

Sounds like there is a big difference between the MySQL and the MSSQL Nodes…
Let me see if I can get some examples for the MSSQL node.

(Reason I say the above is that if the MSSQL node is choking on the insert string, the nodes are different… They are not random… They are building a string, so data[0] + data[1] + ‘data[2]’ + data[3] + ‘data[4]’)

Oh I see. Forgive my ignorance. Is it necessary to build a string to insert it into SQL? Is the string just so you can see the result in a debug node?

Ok, just jumped into the Boss’s node RED and hes using MSSQL, here is what he has in his Node;

DECLARE @TurbineTime VARCHAR(255)

SET @TurbineRPM = {{{payloadTurbineRPM}}};
SET @TurbineTemp = {{{payloadTurbineTemp}}};
SET @TurbinePitch = {{{payloadTurbinePitch}}};
SET @TurbineYaw = {{{payloadTurbineYaw}}};
SET @WindSpeed = {{{payloadWindSpeed}}};
SET @AmbientTemp = {{{payloadAmbientTemp}}};
SET @SpotPrice = {{{payloadSpotPrice}}};
SET @RunPrice = {{{payloadRunPrice}}};
SET @TurbinePower = {{{payloadTurbinePower}}};
SET @TurbineTime = {{{payloadTurbineTime}}};

INSERT INTO dbo.time_series_data

Thats a pretty big example, here is another smaller one;

DECLARE @TurbineMotor VARCHAR(16)
SET @TurbineMotor = ‘{{{payload}}}’;

INSERT INTO dbo.turbine_state_data

Ok, so lets try and break it down, keeping in mind, I have no idea what I am talking about :wink:
I don’t believe that you need the DECLARE if you have already made the db.
Not sure what the SET command is doing…
Then you have the INSERT statement, his db is called dbo (for database Opto) and the table is called turbine_state_data.
The single var is TurbineMotor.
He is also putting a timestamp along with the data variable, but this is optional.

So, INSERT the name of the variable with the VALUES of xxxx.x.

Hope that helps.
Lets know either way.

P.S Our MSSQL guy should be in the office shortlylish and we can ask him what the SET command is doing.

Yeah my original SQL Node is set like that but in the database I came up with null values. As you also did originally, I set all my values as VARCHAR for ease in testing. Here’s my version

DECLARE @description VARCHAR(50);
DECLARE @weather VARCHAR(50);
DECLARE @humidity VARCHAR(50);
DECLARE @windspeed VARCHAR(50);
DECLARE @winddirection VARCHAR(50);
DECLARE @location VARCHAR(50);
DECLARE @forecast VARCHAR(50);

SET @tempc = '{{{payload[1]}}}';
SET @tempf = '{{{payload[2]}}}';
SET @tempk = '{{{payload[3]}}}';
SET @description = '{{{payload[4]}}}';
SET @weather = '{{{payload[5]}}}';
SET @humidity = '{{{payload[6]}}}';
SET @windspeed = '{{{payload[7]}}}';
SET @winddirection = '{{{payload[8]}}}';
SET @location = '{{{payload[9]}}}';
SET @forecast = '{{{payload[10]}}}';
SET @epoch = '{{{payload[11]}}}';

INSERT INTO dbo.Table1 (epoch,
VALUES (@epoch,

Your code looks different…
Are you in a Function node or a MSSQL node?

Are you sure you’re getting nulls and not empty strings?

If you are getting nulls, I’m at a loss, but if you are getting empty strings, then your payload isn’t returning anything, and ‘’ is getting assigned to the SQL variables.

Your SQL looks okay. The SET is just the Transact-SQL assignment command, you could assign the payload entries directly into the insert and remove all that if you wanted.

Disclaimer: I know SQL, but I haven’t used node-RED.

Well yeah I’m getting empty strings, but the payload is returning values. I get this array when I look at the debug of the opto node.

[ 0, 0, 0, 1, 0, 0, 0, 0, -1.35009766, -6.25124931, “NaN”, “NaN”, -32768, -2496, “NaN”, “NaN”, -0.00120019913, -0.0199890137, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …]

So my set should be setting those variables to the 1-11 of this payload or otherwise:
0, 0, 1, 0, 0, 0, 0, -1.35009766, -6.25124931, “NaN”, “NaN”

I’ve tested out returning those values in a javascript function and it works. It just doesn’t work when I try to send it to the database. So I assume my syntax is wrong somewhere… :confused:

And it’s cool that you don’t know node-red I think the problem is somewhere on the sql side…

Well, your SQL looks fine, other than the {{{payload[1]}}} stuff, which I am assuming is a placeholder for the data the node injects.
Do you have a link to the docs for the mssql node you are using so I can get smarter about it?

The {{{payload[1]}}} references the second value in the msg.payload array. msg.payload is what most nodes output. So in my array that should give back the value “0”.

Have you tried using msg.payload instead of just payload?

Yes I have. I have tried with msg.payload and payload. I’ve used and I have even split the array into it’s own separate strings, variables etc.

When I change it from VARCHAR to INT or FLOAT it gives back all 0’s in the database. So I’m pretty sure it’s just the syntax. I must be referencing the values incorrectly some how.

What is the node preceding the MSSQL node? In other words, the node that is creating the array? Would you mind pasting it into a reply?

For more detail on what’s occurring in the entire flow (in reference to Ben’s reply above from my MSSQL node), here’s my SNAP PAC data into MSSQL flow. Paste the below into a new flow in Node-RED:

[{"id":"d3f5910f.d5aa1","type":"MSSQL","z":"65b7d3c9.5053ec","mssqlCN":"c7b6d806.c101b8","name":"Send data to Amazon RDS","query":"DECLARE @TurbineRPM FLOAT\nDECLARE @TurbineTemp FLOAT\nDECLARE @TurbinePitch FLOAT\nDECLARE @TurbineYaw FLOAT\nDECLARE @WindSpeed FLOAT\nDECLARE @AmbientTemp FLOAT\nDECLARE @SpotPrice FLOAT\nDECLARE @RunPrice FLOAT\nDECLARE @TurbinePower FLOAT\nDECLARE @TurbineTime VARCHAR(255)\n\nSET @TurbineRPM = {{{payloadTurbineRPM}}};\nSET @TurbineTemp = {{{payloadTurbineTemp}}};\nSET @TurbinePitch = {{{payloadTurbinePitch}}};\nSET @TurbineYaw = {{{payloadTurbineYaw}}};\nSET @WindSpeed = {{{payloadWindSpeed}}};\nSET @AmbientTemp = {{{payloadAmbientTemp}}};\nSET @SpotPrice = {{{payloadSpotPrice}}};\nSET @RunPrice = {{{payloadRunPrice}}};\nSET @TurbinePower = {{{payloadTurbinePower}}};\nSET @TurbineTime = {{{payloadTurbineTime}}};\n\nINSERT INTO dbo.time_series_data\n          ( \n            TurbineRPM,\n            TurbineTemp,\n            TurbinePitch,\n            TurbineYaw,\n            WindSpeed,\n            AmbientTemp,\n            SpotPrice,\n            RunPrice,\n            TurbinePower,\n            TurbineTime\n          ) \n     VALUES \n          ( \n\t\t\t@TurbineRPM,\n\t\t\t@TurbineTemp,\n\t\t\t@TurbinePitch,\n\t\t\t@TurbineYaw,\n\t\t\t@WindSpeed,\n\t\t\t@AmbientTemp,\n\t\t\t@SpotPrice,\n\t\t\t@RunPrice,\n\t\t\t@TurbinePower,\n\t\t\t@TurbineTime\n          ) ","outField":"payload","x":880,"y":520,"wires":[[]]},{"id":"627cf563.c0e72c","type":"inject","z":"65b7d3c9.5053ec","name":"5 seconds","topic":"","payload":"","payloadType":"date","repeat":"5","crontab":"","once":false,"x":170,"y":80,"wires":[["bc2ca963.6d4418"]]},{"id":"6dc5c0ba.66c4e","type":"pac-read","z":"65b7d3c9.5053ec","device":"8595f2fa.90abb","dataType":"float-variable","tagName":"groov_spot_price_turn_on_turbine","tableStartIndex":"","tableLength":"","value":"payloadRunPrice","valueType":"msg","topic":"","topicType":"none","name":"Turbine Run Price","x":590,"y":480,"wires":[["eb710c36.28de9"]]},{"id":"5bbcb394.d462ec","type":"pac-read","z":"65b7d3c9.5053ec","device":"8595f2fa.90abb","dataType":"float-variable","tagName":"turbine_rpm","tableStartIndex":"","tableLength":"","value":"payloadTurbineRPM","valueType":"msg","topic":"","topicType":"none","name":"Turbine RPM","x":570,"y":200,"wires":[["6b71d825.ccba18"]]},{"id":"6b71d825.ccba18","type":"pac-read","z":"65b7d3c9.5053ec","device":"8595f2fa.90abb","dataType":"float-variable","tagName":"groov_main_turbine_temperature","tableStartIndex":"","tableLength":"","value":"payloadTurbineTemp","valueType":"msg","topic":"","topicType":"none","name":"Main Turbine Temp","x":590,"y":240,"wires":[["9a87bd10.6bf41"]]},{"id":"9a87bd10.6bf41","type":"pac-read","z":"65b7d3c9.5053ec","device":"8595f2fa.90abb","dataType":"float-variable","tagName":"turbine_blade_pitch_output","tableStartIndex":"","tableLength":"","value":"payloadTurbinePitch","valueType":"msg","topic":"","topicType":"none","name":"Turbine Blade Pitch","x":590,"y":280,"wires":[["8371dd38.8c8a8"]]},{"id":"8371dd38.8c8a8","type":"pac-read","z":"65b7d3c9.5053ec","device":"8595f2fa.90abb","dataType":"ana-input","tagName":"yaw_position_sensor_in_deg_from_north","tableStartIndex":"","tableLength":"","value":"payloadTurbineYaw","valueType":"msg","topic":"","topicType":"none","name":"Turbine Yaw","x":570,"y":320,"wires":[["c0d37c86.70712"]]},{"id":"c0d37c86.70712","type":"pac-read","z":"65b7d3c9.5053ec","device":"8595f2fa.90abb","dataType":"float-table","tagName":"ftWeatherData","tableStartIndex":"3","tableLength":"1","value":"payloadWindSpeed","valueType":"msg","topic":"","topicType":"none","name":"Wind Speed","x":570,"y":360,"wires":[["f2d6a769.293e78"]]},{"id":"71568ebe.81da7","type":"pac-read","z":"65b7d3c9.5053ec","device":"8595f2fa.90abb","dataType":"float-variable","tagName":"groov_spot_price_of_electricity","tableStartIndex":"","tableLength":"","value":"payloadSpotPrice","valueType":"msg","topic":"","topicType":"none","name":"Spot Price","x":570,"y":440,"wires":[["6dc5c0ba.66c4e"]]},{"id":"f2d6a769.293e78","type":"pac-read","z":"65b7d3c9.5053ec","device":"8595f2fa.90abb","dataType":"float-table","tagName":"ftWeatherData","tableStartIndex":"0","tableLength":"1","value":"payloadAmbientTemp","valueType":"msg","topic":"","topicType":"none","name":"Ambient Temp","x":580,"y":400,"wires":[["71568ebe.81da7"]]},{"id":"469eb17b.75fd1","type":"pac-read","z":"65b7d3c9.5053ec","device":"8595f2fa.90abb","dataType":"dig-output","tagName":"turbine_motor_power_control","tableStartIndex":"","tableLength":"","value":"","valueType":"msg.payload","topic":"","topicType":"none","name":"Turbine Motor","x":580,"y":120,"wires":[["4522e4a6.07c83c"]]},{"id":"4522e4a6.07c83c","type":"switch","z":"65b7d3c9.5053ec","name":"Turbine On?","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"}],"checkall":"true","outputs":1,"x":570,"y":160,"wires":[["5bbcb394.d462ec"]]},{"id":"eb710c36.28de9","type":"pac-read","z":"65b7d3c9.5053ec","device":"8595f2fa.90abb","dataType":"float-variable","tagName":"groov_turbine_power","tableStartIndex":"","tableLength":"","value":"payloadTurbinePower","valueType":"msg","topic":"","topicType":"none","name":"Turbine Power","x":582,"y":520,"wires":[["d3f5910f.d5aa1"]]},{"id":"bc2ca963.6d4418","type":"moment","z":"65b7d3c9.5053ec","name":"UTC Time","topic":"","input":"","inputType":"msg","inTz":"UTC","adjAmount":"7","adjType":"hours","adjDir":"subtract","format":"","locale":"C","output":"payloadTurbineTime","outputType":"msg","outTz":"UTC","x":370,"y":80,"wires":[["5862c3d2.01b23c"]]},{"id":"5862c3d2.01b23c","type":"function","z":"65b7d3c9.5053ec","name":"Convert UTC String for MS SQL","func":"msg.payloadTurbineTime = \"'\" + msg.payloadTurbineTime + \"'\"\nreturn msg;","outputs":1,"noerr":0,"x":630,"y":80,"wires":[["469eb17b.75fd1"]]},{"id":"c7b6d806.c101b8","type":"MSSQL-CN","z":"","name":"Amazon RDS - OptoTurbine Data","server":"","encyption":false,"database":"optoturbine"},{"id":"8595f2fa.90abb","type":"pac-device","z":"","address":"","protocol":"http"}]

This may help you see how the data is flowing into the MSSQL node.

Let me know if this helps. I’m happy to clarify and troubleshoot as necessary.


[{"id":"6bbd8649.a17708","type":"pac-read","z":"1dd2b39c.d413cc","device":"69f0b0d0.7074f","dataType":"float-table","tagName":"fTbl_IO","tableStartIndex":"","tableLength":"","value":"","valueType":"msg.payload","topic":"","topicType":"none","name":"","x":1842,"y":1122,"wires":[["20a91361.1f0cdc","a09f3ef8.c628"]]},{"id":"a09f3ef8.c628","type":"MSSQL","z":"1dd2b39c.d413cc","mssqlCN":"487081be.a2734","name":"weathertestroyaloak","query":"\n\nDECLARE @tempc INT; /*VARCHAR(50);*/\nDECLARE @tempf FLOAT;\nDECLARE @tempk FLOAT;\nDECLARE @description FLOAT;\nDECLARE @weather FLOAT;\nDECLARE @humidity FLOAT;\nDECLARE @windspeed FLOAT;\nDECLARE @winddirection FLOAT;\nDECLARE @location FLOAT;\nDECLARE @forecast FLOAT;\nDECLARE @epoch FLOAT;\n\n\nSET @tempc = {{{payload[1]}}};\nSET @tempf = '{{{payload[2]}}}';\nSET @tempk = '{{{msg.payload[3]}}}';\nSET @description = '{{{payload[4]}}}';\nSET @weather = '{{{payload[5]}}}';\nSET @humidity = '{{{payload[6]}}}';\nSET @windspeed = '{{{payload[7]}}}';\nSET @winddirection = '{{{payload[8]}}}';\nSET @location = '{{{payload[9]}}}';\nSET @forecast = '{{{payload[10]}}}';\nSET @epoch = '{{{payload[11]}}}';\n\nINSERT INTO dbo.Table1 (epoch,\n description,\n weather,\n tempc,\n tempf,\n tempk,\n humidity,\n windspeed,\n winddirection,\n location,\n forecast)\nVALUES (@epoch,\n @description, \n @weather,\n @tempc, \n @tempf, \n @tempk, \n @humidity,\n @windspeed,\n @winddirection,\n @location,\n @forecast);\n /*('{{{msg.payload[1]}}}',\n'{{{msg.payload[2]}}}',\n'{{{msg.payload[3]}}}',\n'{{{msg.payload[4]}}}',\n'{{{msg.payload[5]}}}',\n'{{{msg.payload[6]}}}',\n'{{{msg.payload[7]}}}',\n'{{{msg.payload[8]}}}',\n'{{{msg.payload[9]}}}',\n'{{{msg.payload[10]}}}',\n'{{{msg.payload[11]}}}');*/\n ","outField":"payload","x":2148,"y":1129,"wires":[[]]},{"id":"ab88ed1.0562d1","type":"inject","z":"1dd2b39c.d413cc","name":"RoyalOakDB Inject","topic":"","payload":"","payloadType":"date","repeat":"300","crontab":"","once":false,"x":1605,"y":1141,"wires":[["6bbd8649.a17708"]]},{"id":"20a91361.1f0cdc","type":"debug","z":"1dd2b39c.d413cc","name":"","active":true,"console":"false","complete":"payload","x":2084,"y":990,"wires":[]},{"id":"69f0b0d0.7074f","type":"pac-device","z":"","address":"","protocol":"http"},{"id":"487081be.a2734","type":"MSSQL-CN","z":"","name":"testopto","server":"","encyption":true,"database":"testingdb"}]

The problem is that the mustache template engine doesn’t support array indices []. If you output just payload to a varchar, you will get a result like 1,2,3,4,5,6.

You probably will want a function node prior to the mssql node that can assign the individual indexes to named variables in the msg object.

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]; = 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,
VALUES ({{{payload.epoch}}},

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!