Sending multiple modbus data values to SQL Server (Azure) in one stored procedure

General
1

Hello friends,

I have prepared one flow where I am reading Energy Meter data through TCP.

I have used Modbus node and sending data to Azure Database. with MSSQL-UCG node.

I am reading total 11 parameters from the meter. When I tried individual parameters with different inject node and different table in Database, it works. But I want to get all the 11 parameter at same time and in on table only.

When i used only one inject node for all the parameters, when node start inject at particular time, it saves only one parameter to the table and all other parameters shows Null value.

Can anybody suggest what should I do to save all 11 parameters at a same time and in single table.

[{"id":"38e77ba6.f6e664","type":"tab","label":"Line_2","disabled":false,"info":""},{"id":"debdf0d1.3e5b2","type":"inject","z":"38e77ba6.f6e664","name":"","topic":"","payload":"","payloadType":"date","repeat":"60","crontab":"","once":false,"onceDelay":0.1,"x":110,"y":380,"wires":[["18124f38.7264f1","c5df9173.ec296","df127eec.906ca","2a92a902.761716","f54eba2d.e62878","d3bfe2ef.2ac28","6551e332.a3fc3c","71129627.de3808","471883e8.0abcbc","77041a1e.ba6564","f4e1e5d7.0de9f8"]]},{"id":"df127eec.906ca","type":"function","z":"38e77ba6.f6e664","name":"Modbus Message","func":"msg.payload ={\n    'fc':3,\n    'unitid':11,\n    'address':147,\n    'quantity':2\n}\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":120,"wires":[["a276c548.102068"]]},{"id":"a276c548.102068","type":"modbus-flex-getter","z":"38e77ba6.f6e664","name":"Modbus","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"server":"6865214d.7a7c","useIOFile":false,"ioFile":"","useIOForPayload":false,"x":620,"y":120,"wires":[["d97fea33.e04338"],[]]},{"id":"2e032a7f.788ee6","type":"MSSQL-UCG","z":"38e77ba6.f6e664","mssqlCN":"c1ab4256.91de3","name":"SQL Query for Remote server","query":"DECLARE @KW FLOAT\nSET @KW = {{{payload}}};\n\nINSERT INTO Line_2\n    (\n    KW,\n    Timestamp\n    )\nVALUES\n    (\n    @KW,\n    CURRENT_TIMESTAMP\n    )","outField":"payload","x":1050,"y":120,"wires":[[]]},{"id":"d97fea33.e04338","type":"function","z":"38e77ba6.f6e664","name":"Realtime_KW","func":"var rawData = new ArrayBuffer(4);\nvar intView = new Uint16Array(rawData);\nvar fltView = new Float32Array(rawData);\n\nintView[1] = msg.payload[1]; //low\nintView[0] = msg.payload[0]; //high\n\nmsg.payload = parseFloat(fltView[0].toFixed(1));\nmsg.topic = \"KW\";\n\nnode.status({fill:\"blue\",shape:\"ring\",text:msg.topic + \":\" + msg.payload});    \n\nreturn msg;","outputs":1,"noerr":0,"x":800,"y":120,"wires":[["2e032a7f.788ee6"]]},{"id":"5aa8df5c.4e205","type":"function","z":"38e77ba6.f6e664","name":"Energy_KWh","func":"var rawData = new ArrayBuffer(4);\nvar intView = new Uint16Array(rawData);\nvar fltView = new Float32Array(rawData);\n\nintView[1] = msg.payload[1]; //low\nintView[0] = msg.payload[0]; //high\n\nmsg.payload = parseFloat(fltView[0].toFixed(1));\nmsg.topic = \"KWh\";\n\nnode.status({fill:\"blue\",shape:\"ring\",text:msg.topic + \":\" + msg.payload});    \n\nreturn msg;","outputs":1,"noerr":0,"x":790,"y":180,"wires":[["d45c4930.213f88"]]},{"id":"18124f38.7264f1","type":"function","z":"38e77ba6.f6e664","name":"Modbus Message","func":"msg.payload ={\n    'fc':3,\n    'unitid':11,\n    'address':223,\n    'quantity':2\n}\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":180,"wires":[["5bb4ea32.c10114"]]},{"id":"5bb4ea32.c10114","type":"modbus-flex-getter","z":"38e77ba6.f6e664","name":"Modbus","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"server":"6865214d.7a7c","useIOFile":false,"ioFile":"","useIOForPayload":false,"x":620,"y":180,"wires":[["5aa8df5c.4e205"],[]]},{"id":"d45c4930.213f88","type":"MSSQL-UCG","z":"38e77ba6.f6e664","mssqlCN":"c1ab4256.91de3","name":"SQL Query for Remote server","query":"DECLARE @KWh FLOAT\nSET @KWh = {{{payload}}};\n\nINSERT INTO Line_2\n    (\n    KWh,\n    Timestamp\n    )\nVALUES\n    (\n    @KWh,\n    CURRENT_TIMESTAMP\n    )\n    \n    \n    ","outField":"payload","x":1050,"y":180,"wires":[[]]},{"id":"978c8c31.31454","type":"comment","z":"38e77ba6.f6e664","name":"66 KV Switchyard Line-2","info":"","x":450,"y":60,"wires":[]},{"id":"3dce24fc.ffb22c","type":"function","z":"38e77ba6.f6e664","name":"Real_PF","func":"var rawData = new ArrayBuffer(4);\nvar intView = new Uint16Array(rawData);\nvar fltView = new Float32Array(rawData);\n\nintView[1] = msg.payload[1]; //low\nintView[0] = msg.payload[0]; //high\n\nmsg.payload = parseFloat(fltView[0].toFixed(1));\nmsg.topic = \"PF\";\n\nnode.status({fill:\"blue\",shape:\"ring\",text:msg.topic + \":\" + msg.payload});    \n\nreturn msg;","outputs":1,"noerr":0,"x":780,"y":240,"wires":[["c7ea221e.9d132"]]},{"id":"2a92a902.761716","type":"function","z":"38e77ba6.f6e664","name":"Modbus Message","func":"msg.payload ={\n    'fc':3,\n    'unitid':11,\n    'address':139,\n    'quantity':2\n}\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":240,"wires":[["e24a91ea.49e4d"]]},{"id":"e24a91ea.49e4d","type":"modbus-flex-getter","z":"38e77ba6.f6e664","name":"Modbus","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"server":"6865214d.7a7c","useIOFile":false,"ioFile":"","useIOForPayload":false,"x":620,"y":240,"wires":[["3dce24fc.ffb22c"],[]]},{"id":"c7ea221e.9d132","type":"MSSQL-UCG","z":"38e77ba6.f6e664","mssqlCN":"c1ab4256.91de3","name":"SQL Query for Remote server","query":"DECLARE @PF FLOAT\nSET @PF = {{{payload}}};\n\nINSERT INTO Line_2\n    (\n    PF,\n    Timestamp\n    )\nVALUES\n    (\n    @PF,\n    CURRENT_TIMESTAMP\n    )","outField":"payload","x":1050,"y":240,"wires":[[]]},{"id":"bbec893c.dc63c8","type":"function","z":"38e77ba6.f6e664","name":"Energy_KVAh","func":"var rawData = new ArrayBuffer(4);\nvar intView = new Uint16Array(rawData);\nvar fltView = new Float32Array(rawData);\n\nintView[1] = msg.payload[1]; //low\nintView[0] = msg.payload[0]; //high\n\nmsg.payload = parseFloat(fltView[0].toFixed(1));\nmsg.topic = \"KVAh\";\n\nnode.status({fill:\"blue\",shape:\"ring\",text:msg.topic + \":\" + msg.payload});    \n\nreturn msg;","outputs":1,"noerr":0,"x":800,"y":300,"wires":[[]]},{"id":"c5df9173.ec296","type":"function","z":"38e77ba6.f6e664","name":"Modbus Message","func":"msg.payload ={\n    'fc':3,\n    'unitid':11,\n    'address':225,\n    'quantity':2\n}\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":300,"wires":[["9ec20f0.b422ef"]]},{"id":"9ec20f0.b422ef","type":"modbus-flex-getter","z":"38e77ba6.f6e664","name":"Modbus","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"server":"6865214d.7a7c","useIOFile":false,"ioFile":"","useIOForPayload":false,"x":620,"y":300,"wires":[["bbec893c.dc63c8"],[]]},{"id":"8d8718a.ce739e8","type":"MSSQL-UCG","z":"38e77ba6.f6e664","mssqlCN":"c1ab4256.91de3","name":"SQL Query for Remote server","query":"DECLARE @KVAh FLOAT\nSET @KVAh = {{{payload}}};\n\nINSERT INTO Line_2\n    (\n    KVAh,\n    Timestamp\n    )\nVALUES\n    (\n    @KVAh,\n    CURRENT_TIMESTAMP\n    )","outField":"payload","x":1050,"y":300,"wires":[[]]},{"id":"eb7b01d4.b3255","type":"function","z":"38e77ba6.f6e664","name":"Temperature","func":"var rawData = new ArrayBuffer(4);\nvar intView = new Uint16Array(rawData);\nvar fltView = new Float32Array(rawData);\n\nintView[1] = msg.payload[1]; //low\nintView[0] = msg.payload[0]; //high\n\nmsg.payload = parseFloat(fltView[0].toFixed(1));\nmsg.topic = \"Temp\";\n\nnode.status({fill:\"blue\",shape:\"ring\",text:msg.topic + \":\" + msg.payload});    \n\nreturn msg;","outputs":1,"noerr":0,"x":790,"y":360,"wires":[["88453b5f.a31528"]]},{"id":"f54eba2d.e62878","type":"function","z":"38e77ba6.f6e664","name":"Modbus Message","func":"msg.payload ={\n    'fc':3,\n    'unitid':11,\n    'address':175,\n    'quantity':2\n}\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":360,"wires":[["d29e3f99.84784"]]},{"id":"d29e3f99.84784","type":"modbus-flex-getter","z":"38e77ba6.f6e664","name":"Modbus","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"server":"6865214d.7a7c","useIOFile":false,"ioFile":"","useIOForPayload":false,"x":620,"y":360,"wires":[["eb7b01d4.b3255"],[]]},{"id":"88453b5f.a31528","type":"MSSQL-UCG","z":"38e77ba6.f6e664","mssqlCN":"c1ab4256.91de3","name":"SQL Query for Remote server","query":"DECLARE @Temperature FLOAT\nSET @Temperature = {{{payload}}};\n\nINSERT INTO Line_2\n    (\n    Temperature,\n    Timestamp\n    )\nVALUES\n    (\n    @Temperature,\n    CURRENT_TIMESTAMP\n    )","outField":"payload","x":1050,"y":360,"wires":[[]]},{"id":"2c85caed.9140f6","type":"function","z":"38e77ba6.f6e664","name":"V-THD-R","func":"var rawData = new ArrayBuffer(4);\nvar intView = new Uint16Array(rawData);\nvar fltView = new Float32Array(rawData);\n\nintView[1] = msg.payload[1]; //low\nintView[0] = msg.payload[0]; //high\n\nmsg.payload = parseFloat(fltView[0].toFixed(1));\nmsg.topic = \"VTHD-R\";\n\nnode.status({fill:\"blue\",shape:\"ring\",text:msg.topic + \":\" + msg.payload});    \n\nreturn msg;","outputs":1,"noerr":0,"x":780,"y":420,"wires":[["30d66d7d.4880f2"]]},{"id":"d3bfe2ef.2ac28","type":"function","z":"38e77ba6.f6e664","name":"Modbus Message","func":"msg.payload ={\n    'fc':3,\n    'unitid':11,\n    'address':177,\n    'quantity':2\n}\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":420,"wires":[["5d556345.81146c"]]},{"id":"5d556345.81146c","type":"modbus-flex-getter","z":"38e77ba6.f6e664","name":"Modbus","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"server":"6865214d.7a7c","useIOFile":false,"ioFile":"","useIOForPayload":false,"x":620,"y":420,"wires":[["2c85caed.9140f6"],[]]},{"id":"30d66d7d.4880f2","type":"MSSQL-UCG","z":"38e77ba6.f6e664","mssqlCN":"c1ab4256.91de3","name":"SQL Query for Remote server","query":"DECLARE @VTHDR FLOAT\nSET @VTHDR = {{{payload}}};\n\nINSERT INTO Line_2\n    (\n    VTHDR,\n    Timestamp\n    )\nVALUES\n    (\n    @VTHDR,\n    CURRENT_TIMESTAMP\n    )","outField":"payload","x":1050,"y":420,"wires":[[]]},{"id":"cc835742.345c08","type":"function","z":"38e77ba6.f6e664","name":"V-THD-Y","func":"var rawData = new ArrayBuffer(4);\nvar intView = new Uint16Array(rawData);\nvar fltView = new Float32Array(rawData);\n\nintView[1] = msg.payload[1]; //low\nintView[0] = msg.payload[0]; //high\n\nmsg.payload = parseFloat(fltView[0].toFixed(1));\nmsg.topic = \"VTHD-Y\";\n\nnode.status({fill:\"blue\",shape:\"ring\",text:msg.topic + \":\" + msg.payload});    \n\nreturn msg;","outputs":1,"noerr":0,"x":780,"y":474,"wires":[["1f08dfe9.cef13"]]},{"id":"6551e332.a3fc3c","type":"function","z":"38e77ba6.f6e664","name":"Modbus Message","func":"msg.payload ={\n    'fc':3,\n    'unitid':11,\n    'address':179,\n    'quantity':2\n}\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":474,"wires":[["8e2fb450.6c79d8"]]},{"id":"8e2fb450.6c79d8","type":"modbus-flex-getter","z":"38e77ba6.f6e664","name":"Modbus","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"server":"6865214d.7a7c","useIOFile":false,"ioFile":"","useIOForPayload":false,"x":620,"y":474,"wires":[["cc835742.345c08"],[]]},{"id":"1f08dfe9.cef13","type":"MSSQL-UCG","z":"38e77ba6.f6e664","mssqlCN":"c1ab4256.91de3","name":"SQL Query for Remote server","query":"DECLARE @VTHDY FLOAT\nSET @VTHDY = {{{payload}}};\n\nINSERT INTO Line_2\n    (\n    VTHDY,\n    Timestamp\n    )\nVALUES\n    (\n    @VTHDY,\n    CURRENT_TIMESTAMP\n    )","outField":"payload","x":1050,"y":474,"wires":[[]]},{"id":"7dbdddd9.b83ff4","type":"function","z":"38e77ba6.f6e664","name":"V-THD-B","func":"var rawData = new ArrayBuffer(4);\nvar intView = new Uint16Array(rawData);\nvar fltView = new Float32Array(rawData);\n\nintView[1] = msg.payload[1]; //low\nintView[0] = msg.payload[0]; //high\n\nmsg.payload = parseFloat(fltView[0].toFixed(1));\nmsg.topic = \"VTHD-B\";\n\nnode.status({fill:\"blue\",shape:\"ring\",text:msg.topic + \":\" + msg.payload});    \n\nreturn msg;","outputs":1,"noerr":0,"x":780,"y":540,"wires":[["57ca43fb.6487cc"]]},{"id":"71129627.de3808","type":"function","z":"38e77ba6.f6e664","name":"Modbus Message","func":"msg.payload ={\n    'fc':3,\n    'unitid':11,\n    'address':181,\n    'quantity':2\n}\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":540,"wires":[["db86a9ae.784188"]]},{"id":"db86a9ae.784188","type":"modbus-flex-getter","z":"38e77ba6.f6e664","name":"Modbus","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"server":"6865214d.7a7c","useIOFile":false,"ioFile":"","useIOForPayload":false,"x":620,"y":540,"wires":[["7dbdddd9.b83ff4"],[]]},{"id":"57ca43fb.6487cc","type":"MSSQL-UCG","z":"38e77ba6.f6e664","mssqlCN":"c1ab4256.91de3","name":"SQL Query for Remote server","query":"DECLARE @VTHDB FLOAT\nSET @VTHDB = {{{payload}}};\n\nINSERT INTO Line_2\n    (\n    VTHDB,\n    Timestamp\n    )\nVALUES\n    (\n    @VTHDB,\n    CURRENT_TIMESTAMP\n    )","outField":"payload","x":1050,"y":540,"wires":[[]]},{"id":"e37a70de.8a3f","type":"function","z":"38e77ba6.f6e664","name":"I-THD-R","func":"var rawData = new ArrayBuffer(4);\nvar intView = new Uint16Array(rawData);\nvar fltView = new Float32Array(rawData);\n\nintView[1] = msg.payload[1]; //low\nintView[0] = msg.payload[0]; //high\n\nmsg.payload = parseFloat(fltView[0].toFixed(1));\nmsg.topic = \"ITHD-R\";\n\nnode.status({fill:\"blue\",shape:\"ring\",text:msg.topic + \":\" + msg.payload});    \n\nreturn msg;","outputs":1,"noerr":0,"x":780,"y":600,"wires":[["51d96671.a8c748"]]},{"id":"471883e8.0abcbc","type":"function","z":"38e77ba6.f6e664","name":"Modbus Message","func":"msg.payload ={\n    'fc':3,\n    'unitid':11,\n    'address':183,\n    'quantity':2\n}\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":600,"wires":[["2ebc8116.8d838e"]]},{"id":"2ebc8116.8d838e","type":"modbus-flex-getter","z":"38e77ba6.f6e664","name":"Modbus","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"server":"6865214d.7a7c","useIOFile":false,"ioFile":"","useIOForPayload":false,"x":620,"y":600,"wires":[["e37a70de.8a3f"],[]]},{"id":"51d96671.a8c748","type":"MSSQL-UCG","z":"38e77ba6.f6e664","mssqlCN":"c1ab4256.91de3","name":"SQL Query for Remote server","query":"DECLARE @ITHDR FLOAT\nSET @ITHDR = {{{payload}}};\n\nINSERT INTO Line_2\n    (\n    ITHDR,\n    Timestamp\n    )\nVALUES\n    (\n    @ITHDR,\n    CURRENT_TIMESTAMP\n    )","outField":"payload","x":1050,"y":600,"wires":[[]]},{"id":"29c5f4ae.1692dc","type":"function","z":"38e77ba6.f6e664","name":"I-THD-Y","func":"var rawData = new ArrayBuffer(4);\nvar intView = new Uint16Array(rawData);\nvar fltView = new Float32Array(rawData);\n\nintView[1] = msg.payload[1]; //low\nintView[0] = msg.payload[0]; //high\n\nmsg.payload = parseFloat(fltView[0].toFixed(1));\nmsg.topic = \"ITHD-Y\";\n\nnode.status({fill:\"blue\",shape:\"ring\",text:msg.topic + \":\" + msg.payload});    \n\nreturn msg;","outputs":1,"noerr":0,"x":780,"y":660,"wires":[["b602408f.66154"]]},{"id":"77041a1e.ba6564","type":"function","z":"38e77ba6.f6e664","name":"Modbus Message","func":"msg.payload ={\n    'fc':3,\n    'unitid':11,\n    'address':185,\n    'quantity':2\n}\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":660,"wires":[["868829b1.0d48c8"]]},{"id":"868829b1.0d48c8","type":"modbus-flex-getter","z":"38e77ba6.f6e664","name":"Modbus","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"server":"6865214d.7a7c","useIOFile":false,"ioFile":"","useIOForPayload":false,"x":620,"y":660,"wires":[["29c5f4ae.1692dc"],[]]},{"id":"b602408f.66154","type":"MSSQL-UCG","z":"38e77ba6.f6e664","mssqlCN":"c1ab4256.91de3","name":"SQL Query for Remote server","query":"DECLARE @ITHDY FLOAT\nSET @ITHDY = {{{payload}}};\n\nINSERT INTO Line_2\n    (\n    ITHDY,\n    Timestamp\n    )\nVALUES\n    (\n    @ITHDY,\n    CURRENT_TIMESTAMP\n    )","outField":"payload","x":1050,"y":660,"wires":[[]]},{"id":"a962cf34.2819f","type":"function","z":"38e77ba6.f6e664","name":"I-THD-B","func":"var rawData = new ArrayBuffer(4);\nvar intView = new Uint16Array(rawData);\nvar fltView = new Float32Array(rawData);\n\nintView[1] = msg.payload[1]; //low\nintView[0] = msg.payload[0]; //high\n\nmsg.payload = parseFloat(fltView[0].toFixed(1));\nmsg.topic = \"ITHD-B\";\n\nnode.status({fill:\"blue\",shape:\"ring\",text:msg.topic + \":\" + msg.payload});    \n\nreturn msg;","outputs":1,"noerr":0,"x":780,"y":720,"wires":[["e145b5bd.a50fa8"]]},{"id":"f4e1e5d7.0de9f8","type":"function","z":"38e77ba6.f6e664","name":"Modbus Message","func":"msg.payload ={\n    'fc':3,\n    'unitid':11,\n    'address':187,\n    'quantity':2\n}\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":720,"wires":[["cb67652d.fd5af8"]]},{"id":"cb67652d.fd5af8","type":"modbus-flex-getter","z":"38e77ba6.f6e664","name":"Modbus","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"server":"6865214d.7a7c","useIOFile":false,"ioFile":"","useIOForPayload":false,"x":620,"y":720,"wires":[["a962cf34.2819f"],[]]},{"id":"e145b5bd.a50fa8","type":"MSSQL-UCG","z":"38e77ba6.f6e664","mssqlCN":"c1ab4256.91de3","name":"SQL Query for Remote server","query":"DECLARE @ITHDB FLOAT\nSET @ITHDB = {{{payload}}};\n\nINSERT INTO Line_2\n    (\n    ITHDB,\n    Timestamp\n    )\nVALUES\n    (\n    @ITHDB,\n    CURRENT_TIMESTAMP\n    )","outField":"payload","x":1050,"y":720,"wires":[[]]},{"id":"6865214d.7a7c","type":"modbus-client","z":"","name":"24Hrs Snap","clienttype":"tcp","bufferCommands":true,"stateLogEnabled":false,"tcpHost":"192.168.1.9","tcpPort":"502","tcpType":"DEFAULT","serialPort":"/dev/ttyUSB","serialType":"RTU-BUFFERD","serialBaudrate":"9600","serialDatabits":"8","serialStopbits":"1","serialParity":"none","serialConnectionDelay":"100","unit_id":1,"commandDelay":1,"clientTimeout":1000,"reconnectOnTimeout":true,"reconnectTimeout":2000,"parallelUnitIdsAllowed":true},{"id":"c1ab4256.91de3","type":"MSSQL-UCG-CN","z":"","name":"Azure Line_2","server":"aissqltemp.database.windows.net","port":"","encyption":true,"database":"AHU_1"}]
2

This is what I would do...

image
image1579Ă—842 131 KB

  • Read all bytes in one hit. This has 2 benefits -
    • data is consistent (grabbed at same point in time)
    • approximately 10 times faster than 11 individual polls
  • use node-red-contrib-buffer-parser to convert data into Floats in one go (see screen shot below)
  • (optional) use mssql-plus (beta version) and use typed parameters (see screen shot below)

Buffer Parser...

image
image1038Ă—778 56 KB

MSSQL-PLUS (beta version)...

image
image525Ă—835 23.6 KB

Edit in case it's unclear...

The single flow at the bottom of the 1st screenshot replaces everything you did. I.e. if you use the 5 node flow I added, the 45 node flow you did becomes redundant/unneeded

1 Like
3

hi,

Thanks a lot...I am trying this solution. Will update once i do it!!!

Thanks again!!!

4

HI,

I have done everything as per your instruction. I am able to read many parameters in single go. But I am stuck at MSSQl plus. I dont find any node as you have shown in pic. can you please help me out for that?

My Updated code is as below.

[{"id":"24d65292.6e22ce","type":"tab","label":"Realtime data","disabled":false,"info":""},{"id":"f63c7f61.312f4","type":"inject","z":"24d65292.6e22ce","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":100,"wires":[["9ebfa216.5598c"]]},{"id":"9ebfa216.5598c","type":"function","z":"24d65292.6e22ce","name":"Modbus Message prepare","func":"msg.payload ={\n    'fc':3,\n    'unitid':6,\n    'address':101,\n    'quantity':80\n}\nreturn msg;","outputs":1,"noerr":0,"x":200,"y":160,"wires":[["7bb80026.fc1a6"]]},{"id":"7bb80026.fc1a6","type":"modbus-flex-getter","z":"24d65292.6e22ce","name":"Modbus","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"server":"6279bb5d.f04424","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"x":360,"y":100,"wires":[["a1fb227e.1cf94","395c3aa0.3dedf6"],[]]},{"id":"e182fe09.28da4","type":"comment","z":"24d65292.6e22ce","name":"Reading Voltage from Meter and store in SQL","info":"","x":210,"y":40,"wires":[]},{"id":"7ea6e9c9.ba0e98","type":"debug","z":"24d65292.6e22ce","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":770,"y":100,"wires":[]},{"id":"a1fb227e.1cf94","type":"buffer-parser","z":"24d65292.6e22ce","name":"","data":"payload","dataType":"msg","specification":"spec","specificationType":"ui","items":[{"type":"floatle","name":"AvVolt","offset":"8","length":"1","offsetbit":"0","mask":""},{"type":"floatle","name":"AvPF","offset":"76","length":"1","offsetbit":"0","mask":""},{"type":"floatle","name":"Hz","offset":"140","length":"1","offsetbit":"0","mask":""},{"type":"floatle","name":"Temp","offset":"148","length":"1","offsetbit":"0","mask":""},{"type":"floatle","name":"VTHDR","offset":"152","length":"1","offsetbit":"0","mask":""},{"type":"floatle","name":"Ph3 volt","offset":"12","length":"1","offsetbit":"0","mask":""}],"swap1":"swap16","swap2":"","swap3":"","swap1Type":"swap","swap2Type":"swap","swap3Type":"swap","msgProperty":"payload","msgPropertyType":"str","resultType":"value","resultTypeType":"output","multipleResult":true,"setTopic":true,"x":540,"y":100,"wires":[["7ea6e9c9.ba0e98"]]},{"id":"395c3aa0.3dedf6","type":"debug","z":"24d65292.6e22ce","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":530,"y":200,"wires":[]},{"id":"6279bb5d.f04424","type":"modbus-client","z":"","name":"Mac_MFMF","clienttype":"tcp","bufferCommands":false,"stateLogEnabled":false,"queueLogEnabled":false,"tcpHost":"192.168.0.16","tcpPort":"502","tcpType":"DEFAULT","serialPort":"/dev/ttyUSB","serialType":"RTU-BUFFERD","serialBaudrate":"9600","serialDatabits":"8","serialStopbits":"1","serialParity":"none","serialConnectionDelay":"100","unit_id":"6","commandDelay":"1","clientTimeout":"1000","reconnectOnTimeout":true,"reconnectTimeout":"2000","parallelUnitIdsAllowed":true}]

I have uploaded a screenshot of my debug window.
(upload://uQFQOR5Nat2JYZYUsNpiML3CDWj.png)

5

Hi again, few things...

  1. please go back and edit your post by clicking the pencil icon image - the flow you have posted is unusable due to fourm formatting (it must be surrounded with ```) - see here for how to format code/flow

  2. you can simply copy / paste an image or snipped image straight into a reply (no need to upload or attach)

  3. The picture I showed is a beta version

I would be most grateful if you became a tester (I have 2 testers so far & the beta version is so far 100% stable) - it would help me get some confidence to release the beta as a new version.

It is not so difficult to install - please do this...

  1. stop node-red
  2. open a command / terminal window
  3. cd your .node-red directory e.g...
  4. cd c:\Users\vstoresft2\.node-red
    or
  5. cd ~/.node-red
  6. npm uninstall node-red-contrib-mssql-plus
  7. npm install node-red-contrib-mssql-plus@beta
  8. start node-red

now you will have the beta version with all the new features

please let me know if you can or cannot do this.

6

Also, to further help you (as I dont have your device or data, could you capture a sample of data from your modbus node and paste it in a reply?

Do this...

  1. add a debug node to the output of the modbus node (set the debug to output complete message)
  2. deploy the change
  3. trigger a modbus read
  4. locate the debug message - hover over it and click the copy button
  5. paste the copied debug msg between backticks...

```
paste code like this
```

here is what i mean...

O0o8n6JWfJ
O0o8n6JWfJ834Ă—586 309 KB

7

HI, I tried this steps, uninstalled previous node and installed beta but I think there was no change in MSSQL plus node.

I have modified my query and pasted my code properly.

[{"id":"24d65292.6e22ce","type":"tab","label":"Realtime data","disabled":false,"info":""},{"id":"f63c7f61.312f4","type":"inject","z":"24d65292.6e22ce","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":100,"wires":[["9ebfa216.5598c"]]},{"id":"9ebfa216.5598c","type":"function","z":"24d65292.6e22ce","name":"Modbus Message prepare","func":"msg.payload ={\n    'fc':3,\n    'unitid':6,\n    'address':101,\n    'quantity':80\n}\nreturn msg;","outputs":1,"noerr":0,"x":200,"y":160,"wires":[["7bb80026.fc1a6"]]},{"id":"7bb80026.fc1a6","type":"modbus-flex-getter","z":"24d65292.6e22ce","name":"Modbus","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"server":"6279bb5d.f04424","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"x":360,"y":100,"wires":[["a1fb227e.1cf94","395c3aa0.3dedf6"],[]]},{"id":"e182fe09.28da4","type":"comment","z":"24d65292.6e22ce","name":"Reading Voltage from Meter and store in SQL","info":"","x":210,"y":40,"wires":[]},{"id":"7ea6e9c9.ba0e98","type":"debug","z":"24d65292.6e22ce","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":770,"y":100,"wires":[]},{"id":"a1fb227e.1cf94","type":"buffer-parser","z":"24d65292.6e22ce","name":"","data":"payload","dataType":"msg","specification":"spec","specificationType":"ui","items":[{"type":"floatle","name":"AvVolt","offset":"8","length":"1","offsetbit":"0","mask":""},{"type":"floatle","name":"AvPF","offset":"76","length":"1","offsetbit":"0","mask":""},{"type":"floatle","name":"Hz","offset":"140","length":"1","offsetbit":"0","mask":""},{"type":"floatle","name":"Temp","offset":"148","length":"1","offsetbit":"0","mask":""},{"type":"floatle","name":"VTHDR","offset":"152","length":"1","offsetbit":"0","mask":""},{"type":"floatle","name":"Ph3 volt","offset":"12","length":"1","offsetbit":"0","mask":""}],"swap1":"swap16","swap2":"","swap3":"","swap1Type":"swap","swap2Type":"swap","swap3Type":"swap","msgProperty":"payload","msgPropertyType":"str","resultType":"value","resultTypeType":"output","multipleResult":true,"setTopic":true,"x":540,"y":100,"wires":[["7ea6e9c9.ba0e98"]]},{"id":"395c3aa0.3dedf6","type":"debug","z":"24d65292.6e22ce","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":530,"y":200,"wires":[]},{"id":"6279bb5d.f04424","type":"modbus-client","z":"","name":"Mac_MFMF","clienttype":"tcp","bufferCommands":false,"stateLogEnabled":false,"queueLogEnabled":false,"tcpHost":"192.168.0.16","tcpPort":"502","tcpType":"DEFAULT","serialPort":"/dev/ttyUSB","serialType":"RTU-BUFFERD","serialBaudrate":"9600","serialDatabits":"8","serialStopbits":"1","serialParity":"none","serialConnectionDelay":"100","unit_id":"6","commandDelay":"1","clientTimeout":"1000","reconnectOnTimeout":true,"reconnectTimeout":"2000","parallelUnitIdsAllowed":true}]
8

HI,

I have added debug node as per your pic with completer message. and I have copy-paste it herer

{"_msgid":"f186b1df.de83a","topic":"7bb80026.fc1a6","payload":[17847,15897,51842,15736,43616,17064,44963,17276,26151,15805,48253,17276,16190,14988,4278,15076,53897,14942,45005,14694,59496,14879,10591,47819,59496,14879,45005,47718,6145,47695,2852,14872,0,16256,0,16256,0,16256,0,16256,18770,14621,0,0,0,0,18770,14621,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,6554,16968,25120,15685,0,16948,34079,16299,0,0],"messageId":"5f1ff9a63a32ab78116079a5","responseBuffer":{"data":[17847,15897,51842,15736,43616,17064,44963,17276,26151,15805,48253,17276,16190,14988,4278,15076,53897,14942,45005,14694,59496,14879,10591,47819,59496,14879,45005,47718,6145,47695,2852,14872,0,16256,0,16256,0,16256,0,16256,18770,14621,0,0,0,0,18770,14621,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,65535,6554,16968,25120,15685,0,16948,34079,16299,0,0],"buffer":[69,183,62,25,202,130,61,120,170,96,66,168,175,163,67,124,102,39,61,189,188,125,67,124,63,62,58,140,16,182,58,228,210,137,58,94,175,205,57,102,232,104,58,31,41,95,186,203,232,104,58,31,175,205,186,102,24,1,186,79,11,36,58,24,0,0,63,128,0,0,63,128,0,0,63,128,0,0,63,128,73,82,57,29,0,0,0,0,0,0,0,0,73,82,57,29,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,25,154,66,72,98,32,61,69,0,0,66,52,133,31,63,171,0,0,0,0]},"input":{"topic":"7bb80026.fc1a6","payload":{"unitid":6,"fc":3,"address":101,"quantity":80,"messageId":"5f1ff9a63a32ab78116079a5","emptyMsgOnFail":false},"unitId":6}}
9

Are you certain you were in the .node-red folder?
Are you certain you included the @beta part (npm install node-red-contrib-mssql-plus@beta)

After install, did you re-start node red AND refresh browser?

10

So I have used your dummy data and added a second buffer-parser to demonstrate how you can use it...

image
image736Ă—159 11.1 KB

the first one (that you had in your flow) outputs individual messages - this is ideal for updating an MQTT broker etc however as you intend on sending the data to a SQL Stored Procedure, i setup the 2nd buffer-parser to disable multiple results and to return key/value
image

now the debug look like this...

image

That is half the solution to getting your data into SQL DB in one (consistent) hit.

Now try to get the beta version of SQL node installed & i will show you how to hook up the buffer-parser output to the SQL parameters.

11

This is what happening when i type aommands. ( screen shot)
Screenshot 2020-07-28 at 4.10.40 PM

Screenshot 2020-07-28 at 4.10.40 PM
Screenshot 2020-07-28 at 4.10.40 PM976Ă—505 13.4 KB

12

That looks ok. Now restart node-red and refresh your browser.

Ps, please copy the node-red startup messages so I can see what is happening.

13

Yes I got SQL plus beta now

Welcome to Node-RED
===================

28 Jul 16:13:08 - [info] Node-RED version: v1.0.3
28 Jul 16:13:08 - [info] Node.js  version: v12.14.1
28 Jul 16:13:08 - [info] Windows_NT 10.0.18363 x64 LE
28 Jul 16:13:09 - [info] Loading palette nodes
28 Jul 16:13:15 - [info] Dashboard version 2.22.1 started at /ui
28 Jul 16:13:15 - [warn] ------------------------------------------------------
28 Jul 16:13:15 - [warn] [node-red-contrib-mssql/mssql] Type already registered
28 Jul 16:13:15 - [warn] [node-red-node-sqlite/sqlite] Type already registered
28 Jul 16:13:15 - [warn] [postgrestor/postgrestor] Type already registered
28 Jul 16:13:15 - [warn] [node-red-contrib-postgrestor-next/postgrestor] Type already registered
28 Jul 16:13:15 - [warn] ------------------------------------------------------
28 Jul 16:13:15 - [info] Settings file  : \Users\mash\.node-red\settings.js
28 Jul 16:13:15 - [info] Context store  : 'default' [module=memory]
28 Jul 16:13:15 - [info] User directory : \Users\mash\.node-red
28 Jul 16:13:15 - [warn] Projects disabled : editorTheme.projects.enabled=false
28 Jul 16:13:15 - [info] Flows file     : \Users\mash\.node-red\flows_MASH9A25.json
28 Jul 16:13:15 - [info] Server now running at http://127.0.0.1:1880/
28 Jul 16:13:15 - [warn]

---------------------------------------------------------------------
Your flow credentials file is encrypted using a system-generated key.

If the system-generated key is lost for any reason, your credentials
file will not be recoverable, you will have to delete it and re-enter
your credentials.

You should set your own key using the 'credentialSecret' option in
your settings file. Node-RED will then re-encrypt your credentials
file using your chosen key the next time you deploy a change.
---------------------------------------------------------------------

28 Jul 16:13:15 - [info] Starting flows
28 Jul 16:13:15 - [info] Started flows
1 Like
14

Ok so see if you can figure it out & let me know how you get on.

Ps, I recommend you remove none ASCII characters from the item names in the buffer parser (will make things easier)

top tip...
Use the "copy path" button that appears under your mouse cursor when you hover over the item in the debug window - then you can paste the path copied directly into the SQL param value field.

e.g.

WgfDg2ew1K
WgfDg2ew1K848Ă—563 138 KB

final note: stored proc parameters names entered must match the name of the parameter in your sql server db

15

Do you have an other mssql node installed? they can cause a conflict!

look in the pallete and remove any others

image

16

Thank you so much!! let me try this and I will revert !!!

Thanks again

17

OMG...

I cant believe this!!!!!!!! It works.......

I have tried with three nos. of parameters. I will make this app as per my actual requirement and will let you know how does it works....

Once again thanks a lot for all your contributions!!! I am more than happy with this solution. thanks

1 Like
18

Thanks - please do feedback - its all i ask for my support.

I wish to get the MSSQL-PLUS changes out of beta and into the main release cycle

Your feedback will be valuable.

Regards, Steve.

19

Hi,,, I am sorry but I didn't get where should I do feedback. Can you please guide? this was my first post!!!!

20

just let me know here in this thread - even if it is to say ALL OK.

Thanks.