Data Logging using node red

[thakurshubham319]

sir i have installed mysql server
i have created database , schema as well as table.
please help me out what code i can use in function block to insert that modbus data in mysql table

[Jakes]

Hi there,

It does not matter if you use MySQL or MSSQL (I use MSSQL like you originally had and it works perfectly) it all depends on what you are comfortable with.

You need to SPLIT your data coming from the modbus node using the split node to simplify it into a readable format for MSSQL in the payload. I found (for learning purposes) that getting a single “clean” value from whatever device you are connecting to is the easiest way to get it to work. Once that works you can play with arrays and then splitting the data in some other clever manner.

The clever guys out there will easily explain to you how you can place the array into your MSSQL database using something like this (the SET function is very important):

SET @val1= ‘{{{payload[0]}}}’;
SET @val2= ‘{{{payload[1]}}}’;

But for learning purposes and “Node Red street cred” I would split the data and separate it.

What queries have you written in your MSSQL node? Once you get your data “cleaned” up from the modbus node you must use MSSQL commands to INSERT that data into the database.

I would try this:

Step 1 - create a MSSQL node and get test data from Node Red pushing into the database so you know that works.

use something like this remove the < > when you fill in your column and table name:

DECLARE @<insertyourcolumnnamehere> VARCHAR(255)
SET @<insertyourcolumnnamehere>= {{{msg.payload}}};
INSERT INTO <YOURTABLENAMEHERE> (<insertyourcolumnnamehere>)
VALUES(@<insertyourcolumnnamehere>)

Step 2: Clean the data from the Modbus Node (or not ) then push it to SQL using the MSSQL node.

Good luck

[thakurshubham319]

thank you sir

data is not posting into the database
flow is not giving any error
this time i am using single value from modbus
but data not coming to the database.

here is the flow
[{“id”:“1d060b.2739c9f6”,“type”:“tab”,“label”:“Flow 1”,“disabled”:false,“info”:""},{“id”:“42a78b37.345c24”,“type”:“tab”,“label”:“Flow 2”,“disabled”:false,“info”:""},{“id”:“bd4599f5.a17f08”,“type”:“tab”,“label”:“Flow 2”,“disabled”:false,“info”:""},{“id”:“6376afc2.0b53c”,“type”:“tab”,“label”:“Flow 2”,“disabled”:false,“info”:""},{“id”:“be23b5c9.c09e98”,“type”:“tab”,“label”:“Flow 2”,“disabled”:false,“info”:""},{“id”:“76bbf728.bfdc68”,“type”:“tab”,“label”:“Flow 2”,“disabled”:false,“info”:""},{“id”:“380a7e52.021592”,“type”:“modbustcp-server”,“z”:"",“name”:“poll”,“host”:“127.0.0.1”,“port”:“502”,“unit_id”:“1”,“reconnecttimeout”:""},{“id”:“229db2f7.7641ae”,“type”:“MSSQL-CN”,“z”:"",“name”:“pme-57”,“server”:“DESKTOP-KSETL7L”,“port”:"",“encyption”:false,“database”:“shubham1”,“useUTC”:false,“connectTimeout”:"",“requestTimeout”:"",“cancelTimeout”:"",“pool”:""},{“id”:“fc3bfbe2.9856f8”,“type”:“debug”,“z”:“76bbf728.bfdc68”,“name”:"",“active”:true,“tosidebar”:true,“console”:false,“tostatus”:false,“complete”:“payload”,“targetType”:“msg”,“x”:650,“y”:80,“wires”:[]},{“id”:“d47a3ac1.0ccbd8”,“type”:“MSSQL”,“z”:“76bbf728.bfdc68”,“mssqlCN”:“229db2f7.7641ae”,“name”:"",“query”:“DECLARE @val1 NVARCHAR(MAX)\nSET @val1= ‘{{{modbus.msg.payload[1]}}}’;\nINSERT INTO Table_1\n (\n tag1\n )\n VALUES\n (\n @val1\n )\n”,“outField”:“payload”,“x”:490,“y”:80,“wires”:[[“fc3bfbe2.9856f8”]]},{“id”:“746d3c55.2186e4”,“type”:“inject”,“z”:“76bbf728.bfdc68”,“name”:"",“topic”:"",“payload”:"",“payloadType”:“date”,“repeat”:"",“crontab”:"",“once”:false,“onceDelay”:0.1,“x”:200,“y”:80,“wires”:[[“3babc768.4ac978”]]},{“id”:“3babc768.4ac978”,“type”:“modbustcp-read”,“z”:“76bbf728.bfdc68”,“name”:“shubham”,“topic”:“modbus”,“dataType”:“HoldingRegister”,“adr”:“1”,“quantity”:“1”,“rate”:“3”,“rateUnit”:“ms”,“server”:“380a7e52.021592”,“ieeeType”:“off”,“ieeeBE”:true,“x”:340,“y”:80,“wires”:[[“d47a3ac1.0ccbd8”]]}]

[Beno]

Line 2 of your MSSQL looks wrong I think.
The debug is throwing an error (so yes, you do have an error). The error is ‘modbus.msg.payload:undefined’. That’s your flow saying you have an error in line 2.

The path should be copied from your ModbusTCP node.
Connect another debug node to the output of the ModbusTCP node and look at its data again.
When you see the tag, click on the little >_ icon to copy the path, like this;

Then open your MSSQL node and paste that path into line 2 between the brackets.

[thakurshubham319]

thank u sir
sir i did the same but in sql database empty data is inserting.
like in given pictures…

[Beno]

Line 2 is still incorrect.
Your debug tab shows ‘undefined’. Nothing is going into your database.
Try msg.payload[0]

[thakurshubham319]

sir i did the same…

[Jakes]

you’re almost there, now click on the arrow next to your [ 25926 ] array payload and then copy that path into the mustache brackets of line 2 like Ben mentioned in the post above

[thakurshubham319]

doing same sir but its not working…

[Jakes]

send me a private message and I can perhaps log onto your pc with teamviewer and help where I can

[philip]

Mustache array references don’t use brackets. You need to use a period followed by the index. So instead of {{payload[0]}} use {{payload.0}}

[philip]

If you are inserting integers into your database, then why are you writing it to an nvarchar? You don’t need to store it in an SQL variable at all for this.

Just go direct:
insert into Table_2 (tag1) values ({{{payload.0}}});

[Jakes]

Well spotted as always Philip… you really are good
I eventually had a remote session with him and noticed this on his database and then fixed his error by changing it to INT. Once that was done voila… all good.

“edit based on Ben’s feedback”

[Beno]

Thanks to both of you for helping out.

BTW, we know the company they are with and its all good. They buy a fair bit of Opto hardware.
Really appreciate the help