Query via Modbus TCP -> MSSQL - zero values

That isnt really what I said.

Here is something for you to try.

It tries to grab as many values as possible in as few ModBus reads as possible. This keeps the data as consistent as possible, while speeds up the whole process, reduces comms and reduces failure points, removes JOIN node & avoids out of sequence values by using named properties instead of an array.

Streamlined flow - all in series

Doing SQL properly...

Use the tools provided! Once you have sensible data in the debug window, use the "Copy Path" to get exact path of the item then paste it into the parameters source

chrome_FAnr9KwT3n

The flow JSON (use CTRL+I to import)

[{"id":"783094c6dca1b4ea","type":"modbus-getter","z":"edf82570772f4a51","name":"Nibe (FC4) Address 1 ~ 11","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"unitid":"1","dataType":"InputRegister","adr":"11","quantity":"1","server":"5b6855b757688e02","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"keepMsgProperties":false,"x":400,"y":660,"wires":[[],["9a014463e9531943"]]},{"id":"9a014463e9531943","type":"buffer-parser","z":"edf82570772f4a51","name":"Parse BT1 ~ BT11 -> BTvalues","data":"payload.buffer","dataType":"msg","specification":"spec","specificationType":"ui","items":[{"type":"int16be","name":"BT1","offset":0,"length":1,"offsetbit":0,"scale":"/10","mask":""},{"type":"int16be","name":"BT7","offset":16,"length":1,"offsetbit":0,"scale":"/10","mask":""},{"type":"int16be","name":"BT6","offset":18,"length":1,"offsetbit":0,"scale":"/10","mask":""},{"type":"int16be","name":"BT10","offset":20,"length":1,"offsetbit":0,"scale":"/10","mask":""},{"type":"int16be","name":"BT11","offset":22,"length":1,"offsetbit":0,"scale":"/10","mask":""}],"swap1":"","swap2":"","swap3":"","swap1Type":"swap","swap2Type":"swap","swap3Type":"swap","msgProperty":"BTvalues","msgPropertyType":"str","resultType":"object","resultTypeType":"return","multipleResult":false,"fanOutMultipleResult":false,"setTopic":true,"outputs":1,"x":710,"y":660,"wires":[["7fc7723a3fc50271"]]},{"id":"7fc7723a3fc50271","type":"modbus-getter","z":"edf82570772f4a51","name":"Nibe (FC4) Address 1102 ~ 1104","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"unitid":"1","dataType":"InputRegister","adr":"1102","quantity":"3","server":"5b6855b757688e02","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"keepMsgProperties":true,"x":410,"y":740,"wires":[[],["cf4b16f8ab487ec8"]]},{"id":"cf4b16f8ab487ec8","type":"buffer-parser","z":"edf82570772f4a51","name":"Parse GP1, GP2 -> msg.GPvalues","data":"payload.buffer","dataType":"msg","specification":"spec","specificationType":"ui","items":[{"type":"int16be","name":"GP1","offset":0,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"int16be","name":"GP2","offset":4,"length":1,"offsetbit":0,"scale":"1","mask":""}],"swap1":"","swap2":"","swap3":"","swap1Type":"swap","swap2Type":"swap","swap3Type":"swap","msgProperty":"GPvalues","msgPropertyType":"str","resultType":"object","resultTypeType":"return","multipleResult":false,"fanOutMultipleResult":false,"setTopic":true,"outputs":1,"x":720,"y":740,"wires":[["33b9ec78f61537c2","a9f2b33ee83bb283","2de3aa13c4714b86"]]},{"id":"c7c1d863f45f992b","type":"inject","z":"edf82570772f4a51","name":"Start (5min)","props":[{"p":"payload"}],"repeat":"300","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":170,"y":660,"wires":[["783094c6dca1b4ea"]]},{"id":"33b9ec78f61537c2","type":"MSSQL","z":"edf82570772f4a51","mssqlCN":"9d74c3ddb1dc754c","name":"MesswerteDB_Heatpump","outField":"payload","returnType":"0","throwErrors":1,"query":"INSERT INTO [dbo].[Heatpump] (\r\n    datetime, BT1, BT10, BT11, BT7, BT6, GP2, GP1\r\n) VALUES (\r\n    @timestamp, @BT1, @BT10, @BT11, @BT7, @BT6, @GP2, @GP1\r\n)","modeOpt":"queryMode","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","params":[{"output":false,"name":"timestamp","type":"DateTime","valueType":"datetime","value":"0","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"BT1","type":"int","valueType":"msg","value":"BTvalues.BT1","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"BT10","type":"int","valueType":"msg","value":"BTvalues.BT10","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"BT11","type":"int","valueType":"msg","value":"BTvalues.BT11","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"BT7","type":"int","valueType":"msg","value":"BTvalues.BT7","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"BT6","type":"int","valueType":"msg","value":"BTvalues.BT6","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"GP2","type":"int","valueType":"msg","value":"GPvalues.GP2","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"GP1","type":"int","valueType":"msg","value":"GPvalues.GP1","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":1050,"y":740,"wires":[[]]},{"id":"a9f2b33ee83bb283","type":"debug","z":"edf82570772f4a51","name":"Check me: msg.BTvalues","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"BTvalues","targetType":"msg","statusVal":"","statusType":"auto","x":930,"y":820,"wires":[]},{"id":"2de3aa13c4714b86","type":"debug","z":"edf82570772f4a51","name":"Check me: msg.GPvalues","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"GPvalues","targetType":"msg","statusVal":"","statusType":"auto","x":930,"y":860,"wires":[]},{"id":"5b6855b757688e02","type":"modbus-client","name":"Nibe S1255-6PC","clienttype":"tcp","bufferCommands":true,"stateLogEnabled":false,"queueLogEnabled":false,"failureLogEnabled":true,"tcpHost":"10.80.10.4","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":"1","clientTimeout":"1000","reconnectOnTimeout":true,"reconnectTimeout":"2000","parallelUnitIdsAllowed":true},{"id":"9d74c3ddb1dc754c","type":"MSSQL-CN","tdsVersion":"7_4","name":"intra-mssql","server":"172.46.100.23","port":"1433","encyption":false,"trustServerCertificate":false,"database":"MesswerteDB","useUTC":false,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true}]
1 Like