Multiple insert statement into msg.query before passing to MSSQL nodes

[Luiey]

Guys,

I have tried and search method for insert multiple statement into a single msg.query at the end of flow before passing to mssql nodes. Assuming I have more than 1 nodes. If I do like below, yes it can save, but if I adding another one value to it, it will failed.

One flow to the SQL:

Two flow to the SQL:

Below are the function script to insert:

var q1 = "(dev_mac,dev_loc_code,temperature,[sensor_datetime],sensor_tag,sensor_value,name,dev_id) VALUES('" + dev_mac + "','" + dev_loc_code + "'," + dev_temp + ",'" + dev_datetime + "','" + dev_tag + "'," +  dev_value + ",'" + dev_name + "','" + dev_id + "')";
msg.payload = dev_mac + "," + dev_loc_code + "," + dev_temp + "," + dev_datetime + "," + dev_tag + "," +  dev_value + "," + dev_name + "," + dev_id;
msg.query1 = q1;
return msg;

and below is the function script that retrieve the msg and apply to the msg.query and failed:

msg.query = "INSERT INTO dbo.collmon " + msg.query1 + "," + msg.query2 + ";";
return msg;

the debug will return success if last function is:

msg.query = "INSERT INTO dbo.collmon " + msg.query1 + ";";
return msg;

and below is the debug panel error for combining 2 functions:

7/28/2016, 5:29:08 PM477eec22.e734d4msg : Object{ "payload": { "name": "RequestError", "message": "Incorrect syntax near 'undefined'.", "code": "EREQUEST", "number": 102, "lineNumber": 1, "state": 1, "class": 15, "serverName": "SMARTEM2016", "procName": "", "precedingErrors": [] }, "_msgid": "b1fba16e.4e046" }

7/28/2016, 5:29:08 PM477eec22.e734d4msg : Object{ "payload": { "name": "RequestError", "message": "Incorrect syntax near 'undefined'.", "code": "EREQUEST", "number": 102, "lineNumber": 1, "state": 1, "class": 15, "serverName": "SMARTEM2016", "procName": "", "precedingErrors": [] }, "_msgid": "41fb1d04.be04e4" }

Each function "Device A/B/C/D/E" is a dummy function that act as real to generate value for some data like real-production. The intention is to combine all insert statement into one query rather than having each function to connection to individual mssql nodes which not proper as SQL statement itself can add multiple data. I'm following below style but not found success.

msg.query = "INSERT INTO dbo.collmon [" + msg.query1 + "],[" + msg.query2 + "];";
return msg;

msg.query = "INSERT INTO dbo.collmon (" + msg.query1 + "),(" + msg.query2 + ");";
return msg;

msg.query = "INSERT INTO dbo.collmon " + msg.query1 + " " + msg.query2 + ";";
return msg;

msg.query = "INSERT INTO dbo.collmon '" + msg.query1 + "','" + msg.query2 + "';";
return msg;

Anyone have a thought of correct way of doing this in node-red?

[Dave C-J]

Do you have the latest version of the mysql node. It was only updated last week to allow multiple statements in one query.

[Nicholas O'Leary]

Dave - MSSQL not mysql

On 28 July 2016 at 14:42, Dave C-J <dce...@gmail.com> wrote:

Do you have the latest version of the mysql node. It was only updated last week to allow multiple statements in one query.

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.
For more options, visit https://groups.google.com/d/optout.

[Dave C-J]

Doh ! In which case.. does that node even allow multiple statements in one query... ?
(IE does it need to be enabled as per the mysql one?:-)

[Luiey]

Dave - I'm not sure if the node allow for multiple insert statement. If I insert manual into SQL Server Management Studio using below query, yes it can be inserted:-

INSERT INTO [datamon].[dbo].[collmon] (dev_mac,dev_loc_code,temperature,[sensor_datetime],sensor_tag,sensor_value,name,dev_id)
VALUES ('00:16:68:2B:40:90','A-43B',45.8785,'2016-07-29 09:27:30','J3',62.3758,'Kiln Machine','9bc17ef3867d51f984ef0b5e');
INSERT INTO [datamon].[dbo].[collmon] (dev_mac,dev_loc_code,temperature,[sensor_datetime],sensor_tag,sensor_value,name,dev_id)
VALUES ('73:81:5E:A6:3A:08','L-7C',77.0614,'2016-07-29 09:27:30','I7',25.7727,'Chemical Warehouse 1','cb20be7f27cbb093a3b15b3f');

or

INSERT INTO [datamon].[dbo].[collmon] (dev_mac,dev_loc_code,temperature,[sensor_datetime],sensor_tag,sensor_value,name,dev_id)
VALUES ('00:16:68:2B:40:90','A-43B',45.8785,'2016-07-29 09:27:30','J3',62.3758,'Kiln Machine','9bc17ef3867d51f984ef0b5e'),('73:81:5E:A6:3A:08','L-7C',77.0614,'2016-07-29 09:27:30','I7',25.7727,'Chemical Warehouse 1','cb20be7f27cbb093a3b15b3f');

If I hard-coded above query into msg.query, yes it success saving

msg.query = "INSERT INTO [datamon].[dbo].[collmon] (dev_mac,dev_loc_code,temperature,[sensor_datetime],sensor_tag,sensor_value,name,dev_id) VALUES ('00:16:68:2B:40:90','A-43B',45.8785,'2016-07-29 09:27:30','J3',62.3758,'Kiln Machine','9bc17ef3867d51f984ef0b5e');" + "INSERT INTO [datamon].[dbo].[collmon] (dev_mac,dev_loc_code,temperature,[sensor_datetime],sensor_tag,sensor_value,name,dev_id) VALUES ('73:81:5E:A6:3A:08','L-7C',77.0614,'2016-07-29 09:27:30','I7',25.7727,'Chemical Warehouse 1','cb20be7f27cbb093a3b15b3f');";

I'm now confuse on some part of the flow:-

[{"id":"477eec22.e734d4","type":"debug","z":"d177ee34.f7d5","name":"","active":true,"console":"false","complete":"payload","x":786.8957977294922,"y":642.8888854980469,"wires":[]},{"id":"3f62712d.e15bce","type":"function","z":"d177ee34.f7d5","name":"","func":"msg.payload = context.global.query1 + context.global.query2 + context.global.query3;\nreturn msg;","outputs":1,"noerr":0,"x":506,"y":639,"wires":[["477eec22.e734d4"]]},{"id":"ce588401.06a368","type":"function","z":"d177ee34.f7d5","name":"Device C","func":"function guid() {\n  function s4() {\n    return Math.floor((1 + Math.random()) * 0x1000)\n      .toString(16)\n      .substring(1);\n  }\n  return s4() + s4() + s4() + s4() + s4() + s4() + s4() + s4();\n}\nfunction generateRandomNumber() {\n    var min = 86.0000,\n        max = 89.9999,\n        highlightedNumber = Math.random() * (max - min) + min;\n    return highlightedNumber.toFixed(4);\n}\nfunction genSensor() {\n    var min = 35,\n        max = 39,\n        highlightedNumber = Math.random() * (max - min) + min;\n    return highlightedNumber.toFixed(4);\n}\nvar dev_mac = \"00:01:E3:64:DD:9B\";\nvar dev_loc_code = \"C-19F\";\nvar dev_temp = generateRandomNumber();\nvar dev_datetime = msg.payload;\nvar dev_tag = \"C2\";\nvar dev_value = genSensor();\nvar dev_name = \"Mixer Tower 1\";\nvar dev_id = \"19aef9ea81eafc8a7d6140b4\";\ncontext.global.query3 = \"INSERT INTO [datamon].[dbo].[collmon] (dev_mac,dev_loc_code,temperature,[sensor_datetime],sensor_tag,sensor_value,name,dev_id) VALUES ('\" + dev_mac + \"','\" + dev_loc_code + \"',\" + dev_temp + \",'\" + dev_datetime + \"','\" + dev_tag + \"',\" +  dev_value + \",'\" + dev_name + \"','\" + dev_id + \"');\";\n//msg.payload = dev_mac + \",\" + dev_loc_code + \",\" + dev_temp + \",\" + dev_datetime + \",\" + dev_tag + \",\" +  dev_value + \",\" + dev_name + \",\" + dev_id;\nreturn msg;","outputs":1,"noerr":0,"x":294.4397888183594,"y":657.0370483398438,"wires":[["3f62712d.e15bce"]]},{"id":"a4af0da1.31ea","type":"function","z":"d177ee34.f7d5","name":"Device B","func":"function guid() {\n  function s4() {\n    return Math.floor((1 + Math.random()) * 0x1000)\n      .toString(16)\n      .substring(1);\n  }\n  return s4() + s4() + s4() + s4() + s4() + s4() + s4() + s4();\n}\nfunction generateRandomNumber() {\n    var min = 55.0000,\n        max = 58.9999,\n        highlightedNumber = Math.random() * (max - min) + min;\n    return highlightedNumber.toFixed(4);\n}\nfunction genSensor() {\n    var min = 55,\n        max = 60,\n        highlightedNumber = Math.random() * (max - min) + min;\n    return highlightedNumber.toFixed(4);\n}\nvar dev_mac = \"00:01:E3:64:DD:9B\";\nvar dev_loc_code = \"B-13A\";\nvar dev_temp = generateRandomNumber();\nvar dev_datetime = msg.payload;\nvar dev_tag = \"B3\";\nvar dev_value = genSensor();\nvar dev_name = \"Boiler Room 1\";\nvar dev_id = \"d06d311d8e713dd28d7a6fa3\";\ncontext.global.query2 = \"INSERT INTO [datamon].[dbo].[collmon] (dev_mac,dev_loc_code,temperature,[sensor_datetime],sensor_tag,sensor_value,name,dev_id) VALUES ('\" + dev_mac + \"','\" + dev_loc_code + \"',\" + dev_temp + \",'\" + dev_datetime + \"','\" + dev_tag + \"',\" +  dev_value + \",'\" + dev_name + \"','\" + dev_id + \"');\";\n//msg.payload = dev_mac + \",\" + dev_loc_code + \",\" + dev_temp + \",\" + dev_datetime + \",\" + dev_tag + \",\" +  dev_value + \",\" + dev_name + \",\" + dev_id;\nreturn msg;","outputs":1,"noerr":0,"x":294.4397888183594,"y":620.0370483398438,"wires":[["3f62712d.e15bce"]]},{"id":"dd27549a.253928","type":"function","z":"d177ee34.f7d5","name":"Device A","func":"function generateRandomNumber() {\n    var min = 93.0000,\n        max = 95.9999,\n        highlightedNumber = Math.random() * (max - min) + min;\n    return highlightedNumber.toFixed(4);\n}\nfunction genSensor() {\n    var min = 43,\n        max = 46,\n        highlightedNumber = Math.random() * (max - min) + min;\n    return highlightedNumber.toFixed(4);\n}\nvar dev_mac = \"00:16:68:2B:40:90\";\nvar dev_loc_code = \"A-43B\";\nvar dev_temp = generateRandomNumber();\nvar dev_datetime = msg.payload;\nvar dev_tag = \"A1\";\nvar dev_value = genSensor();\nvar dev_name = \"Cement Crusher A1\";\nvar dev_id = \"bfa93b70ec2e0857b4f1ae81\";\ncontext.global.query1 = \"INSERT INTO [datamon].[dbo].[collmon] (dev_mac,dev_loc_code,temperature,[sensor_datetime],sensor_tag,sensor_value,name,dev_id) VALUES ('\" + dev_mac + \"','\" + dev_loc_code + \"',\" + dev_temp + \",'\" + dev_datetime + \"','\" + dev_tag + \"',\" +  dev_value + \",'\" + dev_name + \"','\" + dev_id + \"');\"; \n//msg.payload = dev_mac + \",\" + dev_loc_code + \",\" + dev_temp + \",\" + dev_datetime + \",\" + dev_tag + \",\" +  dev_value + \",\" + dev_name + \",\" + dev_id;\nreturn msg;","outputs":1,"noerr":0,"x":294.4397735595703,"y":582.0370483398438,"wires":[["3f62712d.e15bce"]]},{"id":"862656f4.b93f78","type":"function","z":"d177ee34.f7d5","name":"timestamp_gen","func":"function pad(val, padlength, specialchar) { //val: value, padlength: padding length, specialchar: padding other than number e.g. -#@!\n  specialchar = specialchar || '0';         //sample: pad(10,4) return 0010\n  val = val + '';                           //sample: pad(10,4,*) return ****10\n  return val.length >= padlength ? val : new Array(padlength - val.length + 1).join(specialchar) + val;\n}\nfunction getCurrDateTime(){\n    var time = new Date();\n    var year = time.getFullYear();\n    var month = pad(time.getMonth()+1,2);\n    var date1 = pad(time.getDate(),2);\n    var hour = pad(time.getHours(),2);\n    var minutes = pad(time.getMinutes(),2);\n    var seconds = pad(time.getSeconds(),2);\n    return year + \"-\" + month+\"-\"+date1+\" \"+hour+\":\"+minutes+\":\"+seconds;\n}\nmsg.payload = getCurrDateTime();\nreturn msg;","outputs":1,"noerr":0,"x":127.77313232421875,"y":508.88885498046875,"wires":[["dd27549a.253928","a4af0da1.31ea","ce588401.06a368","9215a37a.cc5d5","3fb73ca1.f3da24"]]},{"id":"ada59942.9e4a98","type":"inject","z":"d177ee34.f7d5","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":97.77314249674478,"y":623.3333328362191,"wires":[["862656f4.b93f78"]]}]

                                                                      --> Function 2 (Device A) ------->

Inject -> Function 1 (get current time)    --> Function 2 (Device B)  ------> Function 3 (combine all 3 function into single payload)  ----> Debug

                                                                      --> Function 2 (Device C) ------->

At function 3, I'm combining 3 statement on Device A,B and C into msg.payload to see the output. It should be display one line of combine query statement. But what happen is, it display 3 payload with each of it, combining 3 statement. So its like 3 times repetitive msg.payload. Example as below which showing 3 output for 1 inject:-

7/29/2016, 9:59:32 AM477eec22.e734d4msg.payload : string [754]INSERT INTO [datamon].[dbo].[collmon] (dev_mac,dev_loc_code,temperature,[sensor_datetime],sensor_tag,sensor_value,name,dev_id) VALUES ('00:16:68:2B:40:90','A-43B',94.6845,'2016-07-29 09:59:32','A1',44.1721,'Cement Crusher A1','bfa93b70ec2e0857b4f1ae81');INSERT INTO [datamon].[dbo].[collmon] (dev_mac,dev_loc_code,temperature,[sensor_datetime],sensor_tag,sensor_value,name,dev_id) VALUES ('00:01:E3:64:DD:9B','B-13A',56.6548,'2016-07-29 09:59:32','B3',55.4505,'Boiler Room 1','d06d311d8e713dd28d7a6fa3');INSERT INTO [datamon].[dbo].[collmon] (dev_mac,dev_loc_code,temperature,[sensor_datetime],sensor_tag,sensor_value,name,dev_id) VALUES ('00:01:E3:64:DD:9B','C-19F',86.4642,'2016-07-29 09:59:01','C2',35.4108,'Mixer Tower 1','19aef9ea81eafc8a7d6140b4');
Why is this happen? Something missing / lack on my handling?
Yesterday, it happen when I'm injecting to get query set of 10 devices. In SQL server, it showing 80 rows. 10 devices data with duplication up to 8 data.

[Luiey]

I think I just solve the duplication and the error things. I don't know why.

Compare to other node, I just unwired the other 9 function and set the query to context.global and when it succeed saving to MSSQL without duplication or repetitive data.

At function "Query multi", I write:-

var q1 = msg.payload.query1;
var q2 = context.global.query2;
var q3 = context.global.query3;
var q4 = context.global.query4;
var q5 = context.global.query5;
var q6 = context.global.query6;
var q7 = context.global.query7;
var q8 = context.global.query8;
var q9 = context.global.query9;
var q10 = context.global.query10;
msg={};
msg.query = q1 + " " + q2 + " " + q3 + " " + q4 + " " + q5 + " " + q6 + " " + q7 + " " + q8 + " " + q9 + " " + q10;
return msg;