SQL Receiving Only 0 or NULL from MSSQL-PLUS Node

[rjm21]

[{"id":"22e38406c0cea71d","type":"tab","label":"Flow 2","disabled":false,"info":"","env":[]},{"id":"b063d1b663175b51","type":"modbus-read","z":"22e38406c0cea71d","name":"LOCATION","topic":"","showStatusActivities":false,"logIOActivities":false,"showErrors":false,"showWarnings":true,"unitid":"","dataType":"HoldingRegister","adr":"1337","quantity":"64","rate":"10","rateUnit":"ms","delayOnStart":false,"startDelayTime":"","server":"50696885b27e228a","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"x":150,"y":60,"wires":[["d24aeacd13d3b3c8"],["6b43d3af2d21efa3"]]},{"id":"d24aeacd13d3b3c8","type":"debug","z":"22e38406c0cea71d","name":"PLC_LOCATION","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload[0]","targetType":"msg","statusVal":"payload[0]","statusType":"auto","x":450,"y":60,"wires":[]},{"id":"6b43d3af2d21efa3","type":"function","z":"22e38406c0cea71d","name":"F_LOCATION","func":"// Create new Buffer based on array bytes\nconst buf = Buffer.from(msg.payload.buffer);\n\n// Convert bytes to string\nconst value = buf.toString('ascii');\n\n// Save the value\nmsg.LOCATION = value;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":400,"y":120,"wires":[["cc1a4b2051fec5db","5f5477e7a4a80f4f","0445361bae43fad3"]]},{"id":"cc1a4b2051fec5db","type":"debug","z":"22e38406c0cea71d","name":"M_LOCATION","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"LOCATION","targetType":"msg","statusVal":"payload","statusType":"auto","x":680,"y":120,"wires":[]},{"id":"6f10a750c57931cd","type":"MSSQL","z":"22e38406c0cea71d","mssqlCN":"df8c0b88.91b0a8","name":"","outField":"payload","returnType":0,"throwErrors":1,"query":"INSERT INTO dbo.FATIGUE_TEST\r\n    (LOCATION, LIVE_LOAD_LBF) \r\nVALUES\r\n    (@LOCATION,@LIVE_LOAD_LBF)","modeOpt":"","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","parseMustache":true,"params":[{"output":false,"name":"LOCATION","type":"text","valueType":"msg","value":"LOCATION","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"LIVE_LOAD_LBF","type":"Float","valueType":"msg","value":"LIVE_LOAD_LBF","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":820,"y":260,"wires":[[]]},{"id":"847b3eaf2af8d794","type":"inject","z":"22e38406c0cea71d","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"true","payloadType":"bool","x":150,"y":260,"wires":[["5f5477e7a4a80f4f"]]},{"id":"5f5477e7a4a80f4f","type":"switch","z":"22e38406c0cea71d","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"}],"checkall":"true","repair":false,"outputs":1,"x":490.00000381469727,"y":260,"wires":[["6f10a750c57931cd"]]},{"id":"0a1555fe27cf8a85","type":"MSSQL","z":"22e38406c0cea71d","mssqlCN":"df8c0b88.91b0a8","name":"","outField":"payload","returnType":0,"throwErrors":1,"query":"INSERT INTO dbo.FATIGUE_TEST\r\n    (LOCATION, LIVE_LOAD_LBF) \r\nVALUES\r\n    ('{{{msg.LOCATION}}}','{{{msg.LIVE_LOAD_LBF}}}')","modeOpt":"","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"","paramsOptType":"none","rows":"rows","rowsType":"msg","parseMustache":true,"params":[{"output":false,"name":"LOCATION","type":"text","valueType":"global","value":"LOCATION","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":800,"y":340,"wires":[[]]},{"id":"6ba777bbac7f7846","type":"inject","z":"22e38406c0cea71d","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"true","payloadType":"bool","x":130,"y":340,"wires":[["0445361bae43fad3"]]},{"id":"0445361bae43fad3","type":"switch","z":"22e38406c0cea71d","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"}],"checkall":"true","repair":false,"outputs":1,"x":470.00000381469727,"y":340,"wires":[["0a1555fe27cf8a85"]]},{"id":"9757b6f4a81b8d39","type":"modbus-read","z":"22e38406c0cea71d","name":"PLC>LOAD_SCALED","topic":"","showStatusActivities":false,"logIOActivities":false,"showErrors":false,"showWarnings":true,"unitid":"","dataType":"HoldingRegister","adr":"304","quantity":"2","rate":"1","rateUnit":"ms","delayOnStart":false,"startDelayTime":"","server":"50696885b27e228a","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"x":840,"y":60,"wires":[["ecac32884385a366","1367854209fcb3e5"],[]]},{"id":"ecac32884385a366","type":"debug","z":"22e38406c0cea71d","name":"PLC_LOAD_SCALED","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1100,"y":60,"wires":[]},{"id":"1367854209fcb3e5","type":"function","z":"22e38406c0cea71d","name":"F_LIVE_LOAD","func":"let pay = msg.payload;\n\nconst buf = Buffer.allocUnsafe(8);\nbuf.writeUInt16BE(pay[0], 2);\nbuf.writeUInt16BE(pay[1], 0);\n\nmsg.LIVE_LOAD_LBF = buf.readFloatBE(0);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1040,"y":120,"wires":[["cf25442157169d43","5f5477e7a4a80f4f","0445361bae43fad3"]]},{"id":"cf25442157169d43","type":"debug","z":"22e38406c0cea71d","name":"M_LIVE_LOAD","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"LIVE_LOAD_LBF","targetType":"msg","statusVal":"payload","statusType":"auto","x":1220,"y":120,"wires":[]},{"id":"50696885b27e228a","type":"modbus-client","name":"PLC","clienttype":"tcp","bufferCommands":true,"stateLogEnabled":false,"queueLogEnabled":false,"failureLogEnabled":false,"tcpHost":"192.168.0.10","tcpPort":"502","tcpType":"DEFAULT","serialPort":"/dev/ttyUSB","serialType":"RTU-BUFFERD","serialBaudrate":"9600","serialDatabits":"8","serialStopbits":"1","serialParity":"none","serialConnectionDelay":"100","serialAsciiResponseStartDelimiter":"0x3A","unit_id":"1","commandDelay":"0","clientTimeout":"1000","reconnectOnTimeout":true,"reconnectTimeout":"2000","parallelUnitIdsAllowed":true},{"id":"df8c0b88.91b0a8","type":"MSSQL-CN","tdsVersion":"7_4","name":"JOI_MSSQL","server":"localhost","port":"1433","encyption":true,"trustServerCertificate":true,"database":"Fatigue_Test","useUTC":true,"connectTimeout":"","requestTimeout":"","cancelTimeout":"","pool":"","parseJSON":false,"enableArithAbort":true}]

New to Node-Red and SQL.

I am reading values from a PLC via MODBUS and attempting to insert into MSSQL on localhost. The MODBUS values are coming in correctly, and I am successfully connected to MSSQL.

As a test, I'm attempting to insert a text and a float into MSSQL. The first way using the paramaters in the MSSQL-PLUS node only inserts NULL. The second way using only the query inserts nothing for the text and 0 for the real (expecting the real to be -0.8xx).

[Steve-Mcl]

The most important thing to realise about node-red is that messages from separate wires never arrive at the same point in time.
To be clear, as you are getting values from 2 different modbus reads, the will never be in the same msg at the same time..

For this, you need to either...

1. Grab both values from modbus at the same time
2. Use a join node to join the messages into one
3. Use context to store the incoming values then build a single payload to send to the SQL node.

Option 1 is the best since you will get consistent data ( i.e. the values would be read in the same PLC scan)

Option 2 is the next best.

[rjm21]

I am still getting NULL or blank/0 values in my table. I've tried using flex getter, using a join node, and setting to global.

Am I missing something in the syntax of my MSSQL-PLUS nodes?

[Colin]

Feed the message you are sending to the mssql node into a debug node set to Output Complete Message and show us what is there.

[rjm21]

Debug with original flow

Debug with join node

Debug setting to global and changing the necessary MSSQL node parameters to global

I attempted to use the modbus flex getter, but I received a range error for the offset in the buffer parser as my modbus addresses are too far apart and at this point it's too late to change them all.

[Steve-Mcl]

Share you flow and I will show you.

To share a flow, highlight the nodes to export, press CTRL-E, paste the flow into a reply (surrounded by three backticks above and below)

[rjm21]

My original flow is shared in the original post. Here it is again with everything set to global.

[{"id":"b063d1b663175b51","type":"modbus-read","z":"22e38406c0cea71d","name":"LOCATION","topic":"","showStatusActivities":false,"logIOActivities":false,"showErrors":false,"showWarnings":true,"unitid":"","dataType":"HoldingRegister","adr":"1337","quantity":"64","rate":"10","rateUnit":"ms","delayOnStart":false,"startDelayTime":"","server":"50696885b27e228a","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"x":150,"y":60,"wires":[["d24aeacd13d3b3c8"],["6b43d3af2d21efa3"]]},{"id":"d24aeacd13d3b3c8","type":"debug","z":"22e38406c0cea71d","name":"PLC_LOCATION","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload[0]","targetType":"msg","statusVal":"payload[0]","statusType":"auto","x":450,"y":60,"wires":[]},{"id":"6b43d3af2d21efa3","type":"function","z":"22e38406c0cea71d","name":"F_LOCATION","func":"// Create new Buffer based on array bytes\nconst buf = Buffer.from(msg.payload.buffer);\n\n// Convert bytes to string\nconst value = buf.toString('ascii');\n\n// Save the value\nmsg.payload = value;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":400,"y":120,"wires":[["cc1a4b2051fec5db","51f97b18e1db0d91"]]},{"id":"cc1a4b2051fec5db","type":"debug","z":"22e38406c0cea71d","name":"M_LOCATION","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"LOCATION","targetType":"msg","statusVal":"payload","statusType":"auto","x":680,"y":120,"wires":[]},{"id":"6f10a750c57931cd","type":"MSSQL","z":"22e38406c0cea71d","mssqlCN":"df8c0b88.91b0a8","name":"","outField":"payload","returnType":0,"throwErrors":1,"query":"INSERT INTO dbo.FATIGUE_TEST\r\n    (LOCATION, LIVE_LOAD_LBF) \r\nVALUES\r\n    (@LOCATION,@LIVE_LOAD_LBF)","modeOpt":"","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","parseMustache":true,"params":[{"output":false,"name":"LOCATION","type":"text","valueType":"global","value":"LOCATION","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"LIVE_LOAD_LBF","type":"Float","valueType":"global","value":"LIVE_LOAD_LBF","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":820,"y":260,"wires":[["2845ba1b96e3e692"]]},{"id":"847b3eaf2af8d794","type":"inject","z":"22e38406c0cea71d","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"true","payloadType":"bool","x":150,"y":260,"wires":[["01decbfe4fa4789b"]]},{"id":"0a1555fe27cf8a85","type":"MSSQL","z":"22e38406c0cea71d","mssqlCN":"df8c0b88.91b0a8","name":"","outField":"payload","returnType":0,"throwErrors":1,"query":"INSERT INTO dbo.FATIGUE_TEST\r\n    (LOCATION, LIVE_LOAD_LBF) \r\nVALUES\r\n    ('{{{global.LOCATION}}}','{{{global.LIVE_LOAD_LBF}}}')","modeOpt":"","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"","paramsOptType":"none","rows":"rows","rowsType":"msg","parseMustache":true,"params":[{"output":false,"name":"LOCATION","type":"text","valueType":"global","value":"LOCATION","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":800,"y":340,"wires":[["fcac0ff785e796f5"]]},{"id":"6ba777bbac7f7846","type":"inject","z":"22e38406c0cea71d","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"true","payloadType":"bool","x":130,"y":340,"wires":[["0a1555fe27cf8a85"]]},{"id":"9757b6f4a81b8d39","type":"modbus-read","z":"22e38406c0cea71d","name":"PLC>LOAD_SCALED","topic":"","showStatusActivities":false,"logIOActivities":false,"showErrors":false,"showWarnings":true,"unitid":"","dataType":"HoldingRegister","adr":"304","quantity":"2","rate":"1","rateUnit":"ms","delayOnStart":false,"startDelayTime":"","server":"50696885b27e228a","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"x":840,"y":60,"wires":[["ecac32884385a366","1367854209fcb3e5"],[]]},{"id":"ecac32884385a366","type":"debug","z":"22e38406c0cea71d","name":"PLC_LOAD_SCALED","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1100,"y":60,"wires":[]},{"id":"1367854209fcb3e5","type":"function","z":"22e38406c0cea71d","name":"F_LIVE_LOAD","func":"let pay = msg.payload;\n\nconst buf = Buffer.allocUnsafe(8);\nbuf.writeUInt16BE(pay[0], 2);\nbuf.writeUInt16BE(pay[1], 0);\n\nmsg.payload = buf.readFloatBE(0);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1040,"y":120,"wires":[["cf25442157169d43","250a23f46d9b829b"]]},{"id":"cf25442157169d43","type":"debug","z":"22e38406c0cea71d","name":"M_LIVE_LOAD","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"LIVE_LOAD_LBF","targetType":"msg","statusVal":"payload","statusType":"auto","x":1280,"y":120,"wires":[]},{"id":"51f97b18e1db0d91","type":"change","z":"22e38406c0cea71d","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"LOCATION","tot":"global"}],"action":"","property":"","from":"","to":"","reg":false,"x":400,"y":160,"wires":[[]]},{"id":"cc3d38b7eefed76c","type":"debug","z":"22e38406c0cea71d","name":"debug 2","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":1100,"y":200,"wires":[]},{"id":"4e25f3a877e0c2d7","type":"debug","z":"22e38406c0cea71d","name":"debug 3","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":1100,"y":400,"wires":[]},{"id":"31b2ee3fdf5cfe94","type":"comment","z":"22e38406c0cea71d","name":"THIS ONE INJECTS NULL STRING AND NULL FLOAT","info":"","x":220,"y":200,"wires":[]},{"id":"9e6912080713eaed","type":"comment","z":"22e38406c0cea71d","name":"THIS ONE INJECTS BLANK STRING AND 0 FLOAT","info":"","x":210,"y":400,"wires":[]},{"id":"01decbfe4fa4789b","type":"switch","z":"22e38406c0cea71d","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"}],"checkall":"true","repair":false,"outputs":1,"x":450,"y":260,"wires":[["6f10a750c57931cd","cc3d38b7eefed76c"]]},{"id":"fcac0ff785e796f5","type":"debug","z":"22e38406c0cea71d","name":"debug 8","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1100,"y":360,"wires":[]},{"id":"2845ba1b96e3e692","type":"debug","z":"22e38406c0cea71d","name":"debug 9","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1100,"y":260,"wires":[]},{"id":"250a23f46d9b829b","type":"change","z":"22e38406c0cea71d","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"LIVE_LOAD_LBF","tot":"global"}],"action":"","property":"","from":"","to":"","reg":false,"x":1040,"y":160,"wires":[[]]},{"id":"50696885b27e228a","type":"modbus-client","name":"PLC","clienttype":"tcp","bufferCommands":true,"stateLogEnabled":false,"queueLogEnabled":false,"failureLogEnabled":false,"tcpHost":"192.168.0.10","tcpPort":"502","tcpType":"DEFAULT","serialPort":"/dev/ttyUSB","serialType":"RTU-BUFFERD","serialBaudrate":"9600","serialDatabits":"8","serialStopbits":"1","serialParity":"none","serialConnectionDelay":"100","serialAsciiResponseStartDelimiter":"0x3A","unit_id":"1","commandDelay":"0","clientTimeout":"1000","reconnectOnTimeout":true,"reconnectTimeout":"2000","parallelUnitIdsAllowed":true},{"id":"df8c0b88.91b0a8","type":"MSSQL-CN","tdsVersion":"7_4","name":"JOI_MSSQL","server":"localhost","port":"1433","encyption":true,"trustServerCertificate":true,"database":"Fatigue_Test","useUTC":true,"connectTimeout":"","requestTimeout":"","cancelTimeout":"","pool":"","parseJSON":false,"enableArithAbort":true}]

[Steve-Mcl]

Here is 2 versions.

The top flow is done in one go without any context and done sequentially to avoid comms issues.

The bottom flow is your flow fixed up.

[{"id":"b063d1b663175b51","type":"modbus-read","z":"682b33263388789e","name":"LOCATION","topic":"","showStatusActivities":false,"logIOActivities":false,"showErrors":false,"unitid":"","dataType":"HoldingRegister","adr":"1337","quantity":"64","rate":"2","rateUnit":"s","delayOnStart":false,"startDelayTime":"","server":"50696885b27e228a","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"x":230,"y":420,"wires":[["6b43d3af2d21efa3","d24aeacd13d3b3c8"],[]]},{"id":"6b43d3af2d21efa3","type":"function","z":"682b33263388789e","name":"LOCATION","func":"const buf = msg.responseBuffer.buffer\n// Convert bytes to string\nmsg.payload = buf.toString('ascii');\nmsg.topic = 'LOCATION'\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":470,"y":480,"wires":[["cc1a4b2051fec5db","15c896001299d304"]]},{"id":"cc1a4b2051fec5db","type":"debug","z":"682b33263388789e","name":"M_LOCATION","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":760,"y":480,"wires":[]},{"id":"6f10a750c57931cd","type":"MSSQL","z":"682b33263388789e","mssqlCN":"df8c0b88.91b0a8","name":"","outField":"payload","returnType":0,"throwErrors":1,"query":"INSERT INTO dbo.FATIGUE_TEST\r\n    (LOCATION, LIVE_LOAD_LBF) \r\nVALUES\r\n    (@LOCATION,@LIVE_LOAD_LBF)","modeOpt":"","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","params":[{"output":false,"name":"LOCATION","type":"text","valueType":"msg","value":"payload.LOCATION","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"LIVE_LOAD_LBF","type":"Float","valueType":"msg","value":"payload.LIVE_LOAD_LBF","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":1060,"y":680,"wires":[["2845ba1b96e3e692"]]},{"id":"9757b6f4a81b8d39","type":"modbus-read","z":"682b33263388789e","name":"PLC>LOAD_SCALED","topic":"","showStatusActivities":false,"logIOActivities":false,"showErrors":false,"unitid":"","dataType":"HoldingRegister","adr":"304","quantity":"2","rate":"2","rateUnit":"s","delayOnStart":false,"startDelayTime":"","server":"50696885b27e228a","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"x":920,"y":420,"wires":[["ecac32884385a366","1367854209fcb3e5"],[]]},{"id":"ecac32884385a366","type":"debug","z":"682b33263388789e","name":"PLC_LOAD_SCALED","active":false,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"","statusType":"counter","x":1180,"y":420,"wires":[]},{"id":"1367854209fcb3e5","type":"function","z":"682b33263388789e","name":"LIVE_LOAD_LBF","func":"const buf = msg.responseBuffer.buffer;\nmsg.payload = buf.readFloatBE(0);\nmsg.topic = 'LIVE_LOAD_LBF'\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1130,"y":480,"wires":[["cf25442157169d43","15c896001299d304"]]},{"id":"cf25442157169d43","type":"debug","z":"682b33263388789e","name":"M_LIVE_LOAD","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1360,"y":480,"wires":[]},{"id":"cc3d38b7eefed76c","type":"debug","z":"682b33263388789e","name":"Joined (LOOK AT ME)","active":false,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1300,"y":620,"wires":[]},{"id":"2845ba1b96e3e692","type":"debug","z":"682b33263388789e","name":"SQL Result","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1270,"y":680,"wires":[]},{"id":"15c896001299d304","type":"join","z":"682b33263388789e","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":830,"y":620,"wires":[["cc3d38b7eefed76c","6f10a750c57931cd"]]},{"id":"76e27bdae92c4912","type":"inject","z":"682b33263388789e","name":"V1","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"true","payloadType":"bool","x":190,"y":160,"wires":[["5c5af87559370c8f"]]},{"id":"35181f940b7be6b4","type":"link in","z":"682b33263388789e","name":"Get Modbus Data","links":[],"x":525,"y":80,"wires":[["b698d82684fdad7a"]]},{"id":"3144e9e23644bbaf","type":"link out","z":"682b33263388789e","name":"link out 1","mode":"return","links":[],"x":845,"y":80,"wires":[]},{"id":"b698d82684fdad7a","type":"modbus-flex-getter","z":"682b33263388789e","name":"","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"server":"50696885b27e228a","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"keepMsgProperties":true,"x":670,"y":80,"wires":[["3144e9e23644bbaf"],[]]},{"id":"5c5af87559370c8f","type":"function","z":"682b33263388789e","name":"Make req for FC3 1337[64]","func":"msg.payload = { \n    fc: 3,\n    unitid: 1,\n    address: 1337,\n    quantity: 64 \n} \nreturn msg\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":420,"y":160,"wires":[["9610f54a6225ffae"]]},{"id":"24139079193ac3c6","type":"function","z":"682b33263388789e","name":"parse LOCATION","func":"/** @type {Buffer} */\nconst buf = msg.responseBuffer.buffer;\n\n// Convert bytes to string\nconst value = buf.toString('ascii');\n\n// Save the value in msg.LOCATION\nmsg.LOCATION = value\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":850,"y":160,"wires":[["df223b8c86824574"]]},{"id":"9610f54a6225ffae","type":"link call","z":"682b33263388789e","name":"","links":["35181f940b7be6b4"],"linkType":"static","timeout":"5","x":650,"y":160,"wires":[["24139079193ac3c6"]]},{"id":"df223b8c86824574","type":"function","z":"682b33263388789e","name":"Make req for FC3 304[2]","func":"msg.payload = { \n    fc: 3,\n    unitid: 1,\n    address: 1337,\n    quantity: 64 \n} \nreturn msg\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":410,"y":220,"wires":[["03c1861acc2f1f81"]]},{"id":"03c1861acc2f1f81","type":"link call","z":"682b33263388789e","name":"","links":["35181f940b7be6b4"],"linkType":"static","timeout":"5","x":650,"y":220,"wires":[["e8c63e0f42cda8dc"]]},{"id":"5e052c036a63fe84","type":"MSSQL","z":"682b33263388789e","mssqlCN":"df8c0b88.91b0a8","name":"","outField":"payload","returnType":0,"throwErrors":1,"query":"INSERT INTO dbo.FATIGUE_TEST\r\n    (LOCATION, LIVE_LOAD_LBF) \r\nVALUES\r\n    (@LOCATION,@LIVE_LOAD_LBF)","modeOpt":"","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","params":[{"output":false,"name":"LOCATION","type":"text","valueType":"msg","value":"payload.LOCATION","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"LIVE_LOAD_LBF","type":"Float","valueType":"msg","value":"payload.LIVE_LOAD_LBF","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":1100,"y":220,"wires":[["b285bd79c7b0fdc0"]]},{"id":"e8c63e0f42cda8dc","type":"function","z":"682b33263388789e","name":"parse LIVE_LOAD_LBF","func":"/** @type {Buffer} */\nconst buf = msg.responseBuffer.buffer;\n// read from buffer as float, store in msg.LIVE_LOAD_LBF\nmsg.LIVE_LOAD_LBF = buf.readFloatBE(0)\nmsg.payload = {\n    LIVE_LOAD_LBF: msg.LIVE_LOAD_LBF,\n    LOCATION: msg.LOCATION\n}\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":870,"y":220,"wires":[["5e052c036a63fe84","dc4cabdde2f4590e"]]},{"id":"dc4cabdde2f4590e","type":"debug","z":"682b33263388789e","name":"SQL Input - LOOK AT msg.xxx values","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1170,"y":160,"wires":[]},{"id":"b285bd79c7b0fdc0","type":"debug","z":"682b33263388789e","name":"SQL Result","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1290,"y":220,"wires":[]},{"id":"d24aeacd13d3b3c8","type":"debug","z":"682b33263388789e","name":"PLC_LOCATION","active":false,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"","statusType":"counter","x":530,"y":420,"wires":[]},{"id":"50696885b27e228a","type":"modbus-client","name":"PLC","clienttype":"tcp","bufferCommands":true,"stateLogEnabled":false,"queueLogEnabled":false,"failureLogEnabled":false,"tcpHost":"0.0.0.0","tcpPort":"502","tcpType":"DEFAULT","serialPort":"/dev/ttyUSB","serialType":"RTU-BUFFERD","serialBaudrate":"9600","serialDatabits":"8","serialStopbits":"1","serialParity":"none","serialConnectionDelay":"100","serialAsciiResponseStartDelimiter":"0x3A","unit_id":"1","commandDelay":"0","clientTimeout":"1000","reconnectOnTimeout":true,"reconnectTimeout":"2000","parallelUnitIdsAllowed":true},{"id":"df8c0b88.91b0a8","type":"MSSQL-CN","tdsVersion":"7_4","name":"JOI_MSSQL","server":"localhost","port":"1433","encyption":true,"trustServerCertificate":true,"database":"Fatigue_Test","useUTC":true,"connectTimeout":"","requestTimeout":"","cancelTimeout":"","pool":"","parseJSON":false,"enableArithAbort":true}]

I am afraid I cannot list every issue because much was wrong. All I can say is ...

1. read the built in help of nodes like the join node.
2. Spend some time learning node red - there are a bunch of "helpers" built in to aid development.
3. Use the "Copy Path" buttons on the debug output.

canned text...

Joining

See this article in the cookbook for an example of how to join messages into one object.

Learning

I recommend watching this playlist: Node-RED Essentials. The videos are done by the developers of node-red. They're nice & short and to the point. You will understand a whole lot more in about 1 hour. A small investment for a lot of gain.

How to get path to any item - copy path

There’s a great page in the docs (Working with messages : Node-RED) that will explain how to use the debug panel to find the right path to any data item.

Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.

[rjm21]

Thank you Steve-Mcl.

Your top flow is not working for me. It does not insert anything into my table. It does not show any debug messages either.

The bottom flow is working. Now I just need to figure out a way to insert once on command instead of at every update interval. It seems when I add an inject node, that's when it starts to insert NULL values.

I'm surprised you said much was wrong with my flow. I had a similar one working with SQLite where I was able to successfully insert values on command using a boolean from the PLC. The move to MSSQL is where everything went wrong for me.

[Steve-Mcl]

@rjm21

Now I just need to figure out a way to insert once on command instead of at every update interval. It seems when I add an inject node, that's when it starts to insert NULL values.

If you you want to do this upon inject using my modified version of your original flow, you will need to store the values and recall them upon inject. However, your version is unnecessarily polling the modbus data continuously. See my notes below.

@rjm21

Your top flow is not working for me. It does not insert anything into my table. It does not show any debug messages either.

As you can see from the screenshot it does work, however without any additional info I cannot help you get that simplified version working.

Did you click the V1 inject?

@rjm21

I'm surprised you said much was wrong with my flow

ok, before I list some of the issues, let me explain why the V1 version I did was better.

1. it reads the values from the device in quick succession in an attempt to get "consistent data" (i.e. read from the device at as close as possible to the same point in time). This avoids issues with stale data and timeouts etc
2. If one read fails, the data is NOT logged to the DB (consistency)
3. It re-uses the modbus node via the Link Call to minimise repeated code/nodes
4. all values for the DB are present in the single msg (no context used)
5. You can simply trigger (press the inject named V1) at ANY TIME and get the immediate live values - you dont have to POLL the device forever and ever and ever unnecessarily.

Your original flow...

1. Using context is "asking for trouble" you will get stale and inconsistent values at random times.
2. You setup the change nodes incorrectly
3. Your functions are somewhat contrived
4. You are continuously polling the modbus at a very high rate but only ever capturing the data when you press inject (or some other event, unrelated to reading the modbus device values) - unnecessary polling.

Hope that helps.

[rjm21]

This is what I get for the V1 Version after clicking inject. It does not create a new row in the SQL table.

As far as your last note about using .readfloatBE, that does not return the correct value. I believe because I need to indicate that the two 16 bit words are unsigned.

I understand your point about unnecessary polling. But I was just rolling with whatever method was giving me results. If the V1 flow is what I need to do, then I'm committed to making it work.

[Steve-Mcl]

1. Attach a debug node to the "Link-in" node (the small grey one to the left of the Modbus Flex getter) - do you see a message when you inject?

2. open the Modbus Flex Getter - ensure "Keep Msg Properties" is ticked
   

3. attach a debug (set to show FULL msg) to the output of both "Get Modbus Data" "link-call" nodes - what do you see when you click inject?

4. open the 2 grey "link Call" nodes - ensure they are connected to the grey "link-in" node above (the small grey link-in node to the left of the Modbus Flex Getter)

[rjm21]

1. Debug 17 from the Link-In Node
   
   

2.Keep message properties was/is checked

3.Debug 12 and 15 are set to show complete message. They don't show anything.

4. The Link Call nodes are linked to the "Get Modbus Data" Link In node. The first Link Call node keeps timing out.

[Steve-Mcl]

So the modbus node is not responding. Can you verify the values in the payload are correct? E.g is your device unit: 1, is the address correct? Is the config "PLC" correct? How many configs appear in the drop down?

Maybe try deleting the modbus node & configs & re-add them?

[rjm21]

All values look correct. PLC is the only configured modbus device.

The single Modbus - Getter node is working fine.

I tried deleting the Link nodes and wiring the Flex Getter direct.

I don't know what to tell you.

[Steve-Mcl]

You could replace the link-call nodes with "modbus getter" nodes & delete the link-in-~flexgetter~mqtt-in

Just make sure you set the addresses in the 2 modbus getter nodes.