# Query via Modbus TCP -\> MSSQL - zero values

**URL:** <https://discourse.nodered.org/t/query-via-modbus-tcp-mssql-zero-values/71785>\
**Category:** General\
**Tags:** database, modbus\
**Created:** [6 December 2022 19:09 UTC](https://discourse.nodered.org/t/query-via-modbus-tcp-mssql-zero-values/71785 "2022-12-06T19:09:29Z")\
**Posts on this page:** 1\
**Showing post:** 9

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 December 2022 08:26 UTC](https://discourse.nodered.org/t/query-via-modbus-tcp-mssql-zero-values/71785/9 "2022-12-07T08:26:19Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/6/46fa82b2f19fbf8059b1cb4a34ba7d7a3a8cdc2e.png)

#### 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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/e/6e344e54f85c0506a8402f45a0ce82676396b3f9.png)

![chrome_FAnr9KwT3n](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/9/1996dafb2f3d3b907293e25e4a694288fabc63b1.gif)

#### The flow JSON (use `CTRL+I` to import)

```auto
[{"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}]

```

---

_[View the full topic](https://discourse.nodered.org/t/query-via-modbus-tcp-mssql-zero-values/71785)._