# Inserting data from modbus into a SQL database

**URL:** <https://discourse.nodered.org/t/inserting-data-from-modbus-into-a-sql-database/42741>\
**Category:** General\
**Created:** [16 March 2021 17:44 UTC](https://discourse.nodered.org/t/inserting-data-from-modbus-into-a-sql-database/42741 "2021-03-16T17:44:40Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![santos](https://avatars.discourse-cdn.com/v4/letter/s/aca169/32.png) [@santos](https://discourse.nodered.org/u/santos)\
**Post date:** [16 March 2021 17:44 UTC](https://discourse.nodered.org/t/inserting-data-from-modbus-into-a-sql-database/42741/1 "2021-03-16T17:44:40Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/9/c96374cd9794cc71b5b65648fc2225457b868221.png)  
The problem is when inserting data from modbus into a SQL database. I use phpMyadmin to create the SQL database, that's right. Modbus and Database nodes work well, but I can't pass the array on the function nodes (conversion node) a variable. He dividido el array con el Split node pero no funciona, I would only need two variables but it divides them all. All values that are written to phpMyadmin are zero values, but do not pass the data.  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/e/ee83c2967b08a4826efe1679cc9bbe8060e7f396.png)  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/1/b12e72611106a9ab5ff2c09999f0d0d590b529ba.png)  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/4/549a3a9be09cb7090888fc580952863a3ba5c2ef.png)  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/6/9684180b7d891736083439263c25a5f5332913f7.png)

---

<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:** [16 March 2021 17:59 UTC](https://discourse.nodered.org/t/inserting-data-from-modbus-into-a-sql-database/42741/2 "2021-03-16T17:59:35Z")

</div>

Share your flow and copy the output from `debug 2` (use the copy button that appears under your mouse cursor when you hover over the debug message)

Please format the flow code and the debug message between three backticks

`````  
`[{ code here on a new line }]`  
`````

---

<div class="post-metadata">

**Author:** ![santos](https://avatars.discourse-cdn.com/v4/letter/s/aca169/32.png) [@santos](https://discourse.nodered.org/u/santos)\
**Post date:** [16 March 2021 18:26 UTC](https://discourse.nodered.org/t/inserting-data-from-modbus-into-a-sql-database/42741/3 "2021-03-16T18:26:04Z")

</div>

Debug 2 doesn't seem to perform any function, it doesn't work.

---

<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:** [16 March 2021 18:35 UTC](https://discourse.nodered.org/t/inserting-data-from-modbus-into-a-sql-database/42741/4 "2021-03-16T18:35:17Z")

</div>

Sorry, yes, attach debug 2 to the top output of the modbus. Also, set "debug 2" to "show complete message" ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/9/894db7a54cea9aa8737dcb6fd6c962e9758aa3ec.png)  
then capture the output as I said before.

I need this (and your flow) to be able to show you where you are going wrong.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/1/11230333fcae91578cdb0a5c783b89f7f1789e1d.jpeg)

---

<div class="post-metadata">

**Author:** ![santos](https://avatars.discourse-cdn.com/v4/letter/s/aca169/32.png) [@santos](https://discourse.nodered.org/u/santos)\
**Post date:** [16 March 2021 18:38 UTC](https://discourse.nodered.org/t/inserting-data-from-modbus-into-a-sql-database/42741/5 "2021-03-16T18:38:57Z")

</div>

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

---

<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:** [16 March 2021 18:42 UTC](https://discourse.nodered.org/t/inserting-data-from-modbus-into-a-sql-database/42741/6 "2021-03-16T18:42:08Z")

</div>

@santos I can do nothing with screen shots. I need your flow and I need some demo data from your real device!!!

Select your nodes (draw a box around them) then export your flow (CTRL+E) and paste the flow in a reply.

I also need you to capture the output of the Modbus Node (debug 2 - connected to top output and set to complete message) - THEN - as i said before - "use the copy button that appears under your mouse cursor when you hover over the debug message" then paste that too into a reply.

---

<div class="post-metadata">

**Author:** ![santos](https://avatars.discourse-cdn.com/v4/letter/s/aca169/32.png) [@santos](https://discourse.nodered.org/u/santos)\
**Post date:** [16 March 2021 19:06 UTC](https://discourse.nodered.org/t/inserting-data-from-modbus-into-a-sql-database/42741/8 "2021-03-16T19:06:24Z")

</div>

```auto
[{"id":"b0c1ce72.a3a62","type":"debug","z":"a04d7baf.796ac8","name":"Debug 1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":680,"y":240,"wires":[]},{"id":"a5a9c118.a7cf4","type":"mysql","z":"a04d7baf.796ac8","mydb":"3f07f41d.dbab3c","name":"Data","x":650,"y":360,"wires":[[]]},{"id":"d8670ee5.39841","type":"inject","z":"a04d7baf.796ac8","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":360,"y":240,"wires":[["8b46e5a.93d3818"]]},{"id":"b88d2ed.80bead","type":"modbus-read","z":"a04d7baf.796ac8","name":"Modbus","topic":"","showStatusActivities":false,"logIOActivities":false,"showErrors":false,"unitid":"","dataType":"InputRegister","adr":"0000","quantity":"1","rate":"10","rateUnit":"s","delayOnStart":false,"startDelayTime":"","server":"724fa2d9.5914bc","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"x":90,"y":360,"wires":[["f7649c87.95c0f"],["bf2a86d9.cb3bb8"]]},{"id":"bf2a86d9.cb3bb8","type":"function","z":"a04d7baf.796ac8","name":"conversion ","func":"msg.payload=new Array(8);\nmsg.payload[0]=Math.round(Math.random()*(40-30)+30);\nmsg.payload[1]=Math.round(Math.random()*(40-30)+30);\nmsg.payload[2]=Math.round(Math.random()*(40-30)+30);\nmsg.payload[3]=Math.round(Math.random()*(40-30)+30);\nmsg.payload[4]=Math.round(Math.random()*(40-30)+30);\nmsg.payload[5]=Math.round(Math.random()*(40-30)+30);\nmsg.payload[6]=Math.round(Math.random()*(40-30)+30);\nmsg.payload[7]=Math.round(Math.random()*(40-30)+30);\nmsg.fecha=new Date();\nmsg.hora=msg.fecha.getHours();\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":210,"y":440,"wires":[["9604e030.cc771"]]},{"id":"f7649c87.95c0f","type":"debug","z":"a04d7baf.796ac8","name":"Debug 2","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":240,"y":300,"wires":[]},{"id":"9604e030.cc771","type":"split","z":"a04d7baf.796ac8","name":"Split","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":370,"y":360,"wires":[["6767d632.b27598","8b46e5a.93d3818"]]},{"id":"6767d632.b27598","type":"debug","z":"a04d7baf.796ac8","name":"Debug 3","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":500,"y":440,"wires":[]},{"id":"8b46e5a.93d3818","type":"function","z":"a04d7baf.796ac8","name":"Insert in SQL","func":"var Current = msg.payload.Current\nvar Voltage = msg.payload.Voltage\nmsg.topic = \"INSERT INTO Data(`Current`, `Voltage`) VALUES ('\"+Current+\"', '\"+Voltage+\"')\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":530,"y":300,"wires":[["a5a9c118.a7cf4","b0c1ce72.a3a62"]]},{"id":"3f07f41d.dbab3c","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"Data","tz":"","charset":"UTF8"},{"id":"724fa2d9.5914bc","type":"modbus-client","name":"iologik","clienttype":"tcp","bufferCommands":true,"stateLogEnabled":false,"queueLogEnabled":false,"tcpHost":"192.168.127.250","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}]

```

---

<div class="post-metadata">

**Author:** ![santos](https://avatars.discourse-cdn.com/v4/letter/s/aca169/32.png) [@santos](https://discourse.nodered.org/u/santos)\
**Post date:** [16 March 2021 19:24 UTC](https://discourse.nodered.org/t/inserting-data-from-modbus-into-a-sql-database/42741/9 "2021-03-16T19:24:38Z")

</div>

Thanks, I hope this is the debug output

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

---

<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:** [16 March 2021 22:25 UTC](https://discourse.nodered.org/t/inserting-data-from-modbus-into-a-sql-database/42741/10 "2021-03-16T22:25:39Z")

</div>

Screen shot is not what I need.

For the third and last time...

I need you to capture the debug output produced by the ModBus node.

Look in the debug side bar where debug messages are sent, find the output of "debug 2", hover over the debug message & a button "copy value" will appear under your mouse. Click that and paste your value into a reply.

Like this....

![Vmz3Zy8Buu](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/9/392acfb68c5a638f774f86fda6d22fb96f0bab5f.gif)

---

<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:** [17 March 2021 00:57 UTC](https://discourse.nodered.org/t/inserting-data-from-modbus-into-a-sql-database/42741/11 "2021-03-17T00:57:38Z")

</div>

After looking at your flow for 2 minutes, it looks like you are trying to write `voltage` and `current` that you read from Modbus to a database.

Is that correct?

This is easy and I can show you how but first, please answer every question...

Q1. Which modbus address does `voltage` come from?

Q2. What is the data type and scaling of the `voltage` value (UINT? Float?)

Q3. Which modbus address does `current` come from?

Q4. What is the data type and scaling of the `current` value (UINT? Float?)

Q5. do you have a manual that explains what the modbus registers are?

---

<div class="post-metadata">

**Author:** ![santos](https://avatars.discourse-cdn.com/v4/letter/s/aca169/32.png) [@santos](https://discourse.nodered.org/u/santos)\
**Post date:** [17 March 2021 06:51 UTC](https://discourse.nodered.org/t/inserting-data-from-modbus-into-a-sql-database/42741/12 "2021-03-17T06:51:39Z")

</div>

Thank you very much for your interest, I will respond. First debug 2 output.

{"topic":"polling","payload":[12348],"responseBuffer":{"data":[12348],"buffer":[48,60]},"input":{"topic":"polling","from":"Modbus","payload":{"unitid":"","fc":4,"address":"0000","quantity":"1","messageId":"60519a31c5c24d8357604ea6"},"queueLengthByUnitId":{"unitId":1,"queueLength":1},"queueUnitId":1,"unitId":1},"sendingNodeId":"b88d2ed.80bead","\_msgid":"85302155.39e77"}

I'm doing some tests with this flow, to read variables and store them in SQL. I called voltage and current. But I don't know how to pass it on modbus.

The registers I am going to work with are sensor readings with the 4-20 mA standard. For now what I have is that, the modbus node does seem to be working fine, but there is something precisely in the conversion that I still do not know well.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [16 May 2021 06:52 UTC](https://discourse.nodered.org/t/inserting-data-from-modbus-into-a-sql-database/42741/13 "2021-05-16T06:52:12Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.