@Steve-Mcl - I do want to try to use the Bulk insert - (which is working like a dream, thank you !), but not using the "Parameters Editor" - but by pointing msg.queryParams at it. Could you show me an example of what that would look like? If its on the node docs, I apologise, no finding it.
Steve, yes - I got it working the way it shows on screen, but from reading the help, it looks like their is a way to - for the columns - not use the Editor, but rather - on the drop-down of Columns, choose msg.QueryParams - then push into the msg.queryparams the same info per the Editor - but from a message - I cannot find what such syntax would look like?
The built in help shows you..
However it is not a full example. If you attach a debug (set for full message) to the demo node you will see the full format of the parameter object...
.. but it needs cleaning up before use, like this ...
msg.queryParams = [
{
"output": false,
"name": "IP",
"type": "NVarChar(200)",
"options": {
"nullable": true,
"primary": false,
"identity": false,
"readOnly": false
}
}, {
"output": false,
"name": "Name",
"type": "NVarChar(200)",
"options": {
"nullable": true,
"primary": false,
"identity": false,
"readOnly": false
}
}, {
"output": false,
"name": "Timestamp",
"type": "NVarChar(200)",
"options": {
"nullable": true,
"primary": false,
"identity": false,
"readOnly": false
}
}, {
"output": false,
"name": "Param_Name",
"type": "NVarChar(200)",
"options": {
"nullable": true,
"primary": false,
"identity": false,
"readOnly": false
}
}, {
"output": false,
"name": "Param_Value",
"type": "NVarChar(200)",
"options": {
"nullable": true,
"primary": false,
"identity": false,
"readOnly": false
}
}
];
return msg;
Here is the same demo using msg.queryParams
[{"id":"7aea13edb79ff608","type":"inject","z":"1683bd9a.5e0a02","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"rows","payload":"1000","payloadType":"num","x":1360,"y":1200,"wires":[["ca0236291516ce3b"]]},{"id":"ca0236291516ce3b","type":"function","z":"1683bd9a.5e0a02","name":"generate data","func":"// Table fields...\n// IP, Name, Timestamp, Param_Name, Param_Value\n\nlet IP = \"1.2.3.4\";\nlet Name = \"fake_device\";\nlet now = (new Date()).toISOString(); //generate a timestamp\nmsg.bulk_data_to_insert = []; //the msg property we will use to pass the data array to the MSSQL node\n\n//generate fake data rows of data in the following format...\n/*\n[\n [col1, col2, coln... ], //row 1\n [col1, col2, coln... ], //row 2\n [col1, col2, coln... ], //row n\n]\n*/\nif (msg.topic == \"rows\") {\n for (let x = 1; x <= msg.payload; x++) {\n msg.bulk_data_to_insert.push([\n IP, Name, now, `P${x + 1000}`, Math.random()\n ]);\n }\n} else {\n for (let x = 1; x <= msg.payload; x++) {\n msg.bulk_data_to_insert.push({\n IP: IP, \n Name: Name, \n Timestamp: now, \n Param_Name: `P${x + 1000}`,\n Param_Value: Math.random()\n });\n }\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1580,"y":1200,"wires":[["d1f0832aa0fe0553"]]},{"id":"d8f9d170679713bd","type":"debug","z":"1683bd9a.5e0a02","name":"Bulk Data to insert","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"bulk_data_to_insert","targetType":"msg","statusVal":"","statusType":"auto","x":2010,"y":1200,"wires":[]},{"id":"9be796c66a852efd","type":"MSSQL","z":"1683bd9a.5e0a02","mssqlCN":"8d9d212a.cea03","name":"bulk insert -> demo_device_parameters_table","outField":"payload","returnType":"0","throwErrors":1,"query":"demo_device_parameters_table","modeOpt":"","modeOptType":"bulk","queryOpt":"","queryOptType":"editor","paramsOpt":"queryParams","paramsOptType":"msg","rows":"bulk_data_to_insert","rowsType":"msg","params":[],"x":1720,"y":1240,"wires":[["12a579dd8898e035"]]},{"id":"12a579dd8898e035","type":"debug","z":"1683bd9a.5e0a02","name":"Bulk Insert Result","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1890,"y":1280,"wires":[]},{"id":"6fdb4dcd8b97e9f4","type":"inject","z":"1683bd9a.5e0a02","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"rows","payload":"10000","payloadType":"num","x":1370,"y":1240,"wires":[["ca0236291516ce3b"]]},{"id":"110f5a0c8051eaab","type":"inject","z":"1683bd9a.5e0a02","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"objects","payload":"2000","payloadType":"num","x":1370,"y":1280,"wires":[["ca0236291516ce3b"]]},{"id":"53cfe74263fe056e","type":"MSSQL","z":"1683bd9a.5e0a02","mssqlCN":"8d9d212a.cea03","name":"Create demo_device_parameters_table","outField":"payload","returnType":"1","throwErrors":1,"query":"\r\nSET ANSI_NULLS ON\r\nSET QUOTED_IDENTIFIER ON\r\n\r\nCREATE TABLE [dbo].[demo_device_parameters_table](\r\n [ID] int not null identity(1,1),\t\r\n [IP] nvarchar(200) NULL,\r\n [Name] nvarchar(200) NULL,\r\n [Timestamp] nvarchar(200) NULL,\r\n [Param_Name] nvarchar(200) NULL,\r\n [Param_Value] float NULL\r\n) ON [PRIMARY]","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"none","rows":"rows","rowsType":"msg","params":[],"x":1600,"y":1120,"wires":[["a46ed483d0141f3a"]]},{"id":"26274d41cfe53f24","type":"inject","z":"1683bd9a.5e0a02","name":"Start","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"str","x":1350,"y":1120,"wires":[["53cfe74263fe056e"]]},{"id":"a46ed483d0141f3a","type":"debug","z":"1683bd9a.5e0a02","name":"Table creation","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1880,"y":1120,"wires":[]},{"id":"7e2fd2b76e5faa86","type":"comment","z":"1683bd9a.5e0a02","name":"1. Open the MSSQL node and set up the connection to your SQL Server","info":"","x":1530,"y":900,"wires":[]},{"id":"0d365aa11edfeae6","type":"comment","z":"1683bd9a.5e0a02","name":"2. Click the \"Start\" Inject to create the table","info":"","x":1440,"y":940,"wires":[]},{"id":"52923bffa155a6f9","type":"comment","z":"1683bd9a.5e0a02","name":"3. Click any of the \"rows: xxxx\" Injects to perform a Bulk Insert","info":"","x":1500,"y":980,"wires":[]},{"id":"7e8b55a14d84ac04","type":"comment","z":"1683bd9a.5e0a02","name":"4. Click \"Get Data\" Inject to select data from the table","info":"","x":1470,"y":1020,"wires":[]},{"id":"516770ee011ea9fd","type":"inject","z":"1683bd9a.5e0a02","name":"Get top 1000 with ID >= 200","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"1000","payload":"200","payloadType":"num","x":1420,"y":1360,"wires":[["bef9b49212f0cdd8"]]},{"id":"bef9b49212f0cdd8","type":"MSSQL","z":"1683bd9a.5e0a02","mssqlCN":"8d9d212a.cea03","name":"Select Data","outField":"payload","returnType":"0","throwErrors":1,"query":"PRINT 'Selecting TOP {{{topic}}} from demo_device_parameters_table'\r\nPRINT 'Where ID if > or = to '\r\nPRINT @ID\r\nSELECT TOP {{{topic}}} * \r\nFROM [dbo].[demo_device_parameters_table]\r\nWHERE ID >= @ID\r\nPRINT @@ROWCOUNT\r\n\r\n--Print info will appear in msg.sqlInfo","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","params":[{"output":false,"name":"ID","type":"int","valueType":"msg","value":"payload"}],"x":1670,"y":1380,"wires":[["bf168e2cc2a4c4e9"]]},{"id":"bf168e2cc2a4c4e9","type":"debug","z":"1683bd9a.5e0a02","name":"Selected Data","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1880,"y":1380,"wires":[]},{"id":"07f20cc163b440ba","type":"inject","z":"1683bd9a.5e0a02","name":"Clean up","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"str","x":1360,"y":1480,"wires":[["fe3226e2bdbee79a"]]},{"id":"fe3226e2bdbee79a","type":"MSSQL","z":"1683bd9a.5e0a02","mssqlCN":"8d9d212a.cea03","name":"Drop demo_device_parameters_table","outField":"payload","returnType":"1","throwErrors":1,"query":"\r\nDROP TABLE [dbo].[demo_device_parameters_table]\r\n","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"none","rows":"rows","rowsType":"msg","params":[],"x":1610,"y":1480,"wires":[["b5cd717fe420b6c0"]]},{"id":"b5cd717fe420b6c0","type":"debug","z":"1683bd9a.5e0a02","name":"Clean up result","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1880,"y":1480,"wires":[]},{"id":"ae2feb21292bb5c9","type":"inject","z":"1683bd9a.5e0a02","name":"Get top 50 with ID >= 1000","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"50","payload":"1000","payloadType":"num","x":1410,"y":1400,"wires":[["bef9b49212f0cdd8"]]},{"id":"399d5de29539a4ac","type":"comment","z":"1683bd9a.5e0a02","name":"5. Click \"Clean up\" Inject to drop the table","info":"","x":1440,"y":1060,"wires":[]},{"id":"d1f0832aa0fe0553","type":"function","z":"1683bd9a.5e0a02","name":"set queryParams","func":"msg.queryParams = [\n {\n \"output\": false,\n \"name\": \"IP\",\n \"type\": \"NVarChar(200)\",\n \"valueType\": \"msg\",\n \"options\": {\n \"nullable\": true,\n \"primary\": false,\n \"identity\": false,\n \"readOnly\": false\n }\n }, {\n \"output\": false,\n \"name\": \"Name\",\n \"type\": \"NVarChar(200)\",\n \"options\": {\n \"nullable\": true,\n \"primary\": false,\n \"identity\": false,\n \"readOnly\": false\n }\n }, {\n \"output\": false,\n \"name\": \"Timestamp\",\n \"type\": \"NVarChar(200)\",\n \"options\": {\n \"nullable\": true,\n \"primary\": false,\n \"identity\": false,\n \"readOnly\": false\n }\n }, {\n \"output\": false,\n \"name\": \"Param_Name\",\n \"type\": \"NVarChar(200)\",\n \"options\": {\n \"nullable\": true,\n \"primary\": false,\n \"identity\": false,\n \"readOnly\": false\n }\n }, {\n \"output\": false,\n \"name\": \"Param_Value\",\n \"type\": \"NVarChar(200)\",\n \"options\": {\n \"nullable\": true,\n \"primary\": false,\n \"identity\": false,\n \"readOnly\": false\n }\n }\n];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1790,"y":1200,"wires":[["d8f9d170679713bd","9be796c66a852efd"]]},{"id":"8d9d212a.cea03","type":"MSSQL-CN","tdsVersion":"7_4","name":"My DB","server":"192.168.1.38","port":"1433","encyption":false,"trustServerCertificate":true,"database":"","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true}]
Thank you! Powerful !!
Hi Steve-Mcl,
I tried to reproduce your example but unfortunately I was failing several times.
Creating the table works fine, but inserting the data using Bulk Insert does not work !
I am doing nothing else than just executing your example step by step.
But there seems to be any kind of formatting error.
Could you please have a look - this is preventing me from finishing a big project 
BR
G
I think there is a bug in underlying tedious library recently introduced that is affecting many downstream libraries (including this one).
I will look at how to get it working later this evening and let you know.
That would be great 
As I am using this library as a part of my daily business in the office I would appreciate to honor your work with a few coins from the company purse Is there a way to "thank you" in an official way for making life a bit easier for us ?
BR
G
As of last night, the underlying lib (tedious) that caused this issue was published (V11.4.0) and any new install of node-red-contrib-mssql-plus will get the updated packages.
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
