Modbus to mySQL flow suggestions

[BambosD]

Hello again Mr. Steve,

change a little the plan, since each modbus poll could be up to 125 values according to the protocol , and some devices using many thousands in the modbus range, most probably we need 2 or 3 different polls from each device, which giving us 3-4 values from each poll and those values could be in 1 device (in case of a PLC that aggregates the values) or 3 to 4 devices if the field is not organized well. So instead of “device1, device2” before flex getters, we have just polls. poll1 , poll2 etc… now trying to read from the modbus field some real values, finally i have the structure below together with timestamp to make one complete record for the database.

i think now is more clear.

{"_msgid":"0945f017250cf55a","payload":[0,0,618,39543,0,0,0,0,0,0,0,0,0,0,0,1,0,1,0,0,2,2,3,0,0,0,0,6204,42455,0,0,6186,2219,0,0,0,0,0,0,620,30459,1,31027,0,141,0,4,0,0,0,17,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,65279,65535,65515,63971,50143,40959,0,130,0,0,0,0,0,0,0,0,0,0,0,0,0],"topic":"b65bd4a099fb7463","timestamp":"2025-08-06 23:27:15","settings":{"input":"2025-08-06T20:27:15.937Z","input_format":"","input_tz":"Europe/Athens","output_format":"YYYY-MM-DD HH:mm:ss","output_locale":"en-US","output_tz":"Europe/Athens"},"messageId":"6893baa3d349729cd64ef3fa","modbusRequest":{"unitid":100,"fc":3,"address":40521,"quantity":100,"emptyMsgOnFail":false,"keepMsgProperties":true,"messageId":"6893baa3d349729cd64ef3fa"},"responseBuffer":{"data":[0,0,618,39543,0,0,0,0,0,0,0,0,0,0,0,1,0,1,0,0,2,2,3,0,0,0,0,6204,42455,0,0,6186,2219,0,0,0,0,0,0,620,30459,1,31027,0,141,0,4,0,0,0,17,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,65279,65535,65515,63971,50143,40959,0,130,0,0,0,0,0,0,0,0,0,0,0,0,0],"buffer":[0,0,0,0,2,106,154,119,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,1,0,0,0,0,0,2,0,2,0,3,0,0,0,0,0,0,0,0,24,60,165,215,0,0,0,0,24,42,8,171,0,0,0,0,0,0,0,0,0,0,0,0,2,108,118,251,0,1,121,51,0,0,0,141,0,0,0,4,0,0,0,0,0,0,0,17,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,254,255,255,255,255,235,249,227,195,223,159,255,0,0,0,130,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]},"originalPayload":[0,0,618,39543,0,0,0,0,0,0,0,0,0,0,0,1,0,1,0,0,2,2,3,0,0,0,0,6204,42455,0,0,6186,2219,0,0,0,0,0,0,620,30459,1,31027,0,141,0,4,0,0,0,17,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,65279,65535,65515,63971,50143,40959,0,130,0,0,0,0,0,0,0,0,0,0,0,0,0],"specification":{"options":{"byteSwap":[],"resultType":"keyvalue","singleResult":true,"msgProperty":"poll3","setTopic":false},"items":[{"type":"uint32le","name":"dcin","offset":0,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":0,"scaler":{"operator":"*","operand":0.1},"value":0},{"type":"int32le","name":"activep","offset":4,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":1,"scaler":{"operator":"*","operand":0.1},"value":200660838.60000002},{"type":"int32le","name":"apparent","offset":8,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":2,"scaler":{"operator":"*","operand":0.1},"value":0},{"type":"int32le","name":"reactiveq","offset":26,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":3,"scaler":{"operator":"*","operand":0.1},"value":0},{"type":"int32le","name":"energy","offset":120,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":4,"scaler":{"operator":"*","operand":0.1},"value":0}]},"values":[0,200660838.60000002,0,0,0],"objectResults":{"dcin":{"type":"uint32le","name":"dcin","offset":0,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":0,"scaler":{"operator":"*","operand":0.1},"value":0},"activep":{"type":"int32le","name":"activep","offset":4,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":1,"scaler":{"operator":"*","operand":0.1},"value":200660838.60000002},"apparent":{"type":"int32le","name":"apparent","offset":8,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":2,"scaler":{"operator":"*","operand":0.1},"value":0},"reactiveq":{"type":"int32le","name":"reactiveq","offset":26,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":3,"scaler":{"operator":"*","operand":0.1},"value":0},"energy":{"type":"int32le","name":"energy","offset":120,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":4,"scaler":{"operator":"*","operand":0.1},"value":0}},"keyvalues":{"dcin":0,"activep":200660838.60000002,"apparent":0,"reactiveq":0,"energy":0},"arrayResults":[{"type":"uint32le","name":"dcin","offset":0,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":0,"scaler":{"operator":"*","operand":0.1},"value":0},{"type":"int32le","name":"activep","offset":4,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":1,"scaler":{"operator":"*","operand":0.1},"value":200660838.60000002},{"type":"int32le","name":"apparent","offset":8,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":2,"scaler":{"operator":"*","operand":0.1},"value":0},{"type":"int32le","name":"reactiveq","offset":26,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":3,"scaler":{"operator":"*","operand":0.1},"value":0},{"type":"int32le","name":"energy","offset":120,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":4,"scaler":{"operator":"*","operand":0.1},"value":0}],"buffer":[0,0,0,0,2,106,154,119,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,1,0,0,0,0,0,2,0,2,0,3,0,0,0,0,0,0,0,0,24,60,165,215,0,0,0,0,24,42,8,171,0,0,0,0,0,0,0,0,0,0,0,0,2,108,118,251,0,1,121,51,0,0,0,141,0,0,0,4,0,0,0,0,0,0,0,17,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,254,255,255,255,255,235,249,227,195,223,159,255,0,0,0,130,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0],"poll1":{"bom":37.800000000000004,"plane":0,"horizontal":692.2,"ambient":21.700000000000003},"poll2":{"setp":-12.9,"setq":-12.9},"poll3":{"dcin":0,"activep":200660838.60000002,"apparent":0,"reactiveq":0,"energy":0}}

So now, what is your suggestion to feed those into the database as 1 record ?
Is further aggregation needed ? or the values is usable for SQL insert as it is ?

Merge with function, template or change node ?

[Colin]

@BambosD

or the values is usable for SQL insert as it is

Are you planning to use a prepared query with the fields in properties in msg.payload as I suggested earlier?

[Edit] In case you missed it I posted:

@Colin

use a Prepared query with named parameters as described in the docs, then the query is fixed. You can use a Change node to build the required message.

[Steve-Mcl]

Why are you fixated on 1 record? That will be incredibly inflexible. Perhaps it would make more sense for your table to contain the an id column for the device id. That keeps it simple. Alternatively consider storing the values as JSON.

The issue with 1 row for 4 devices is if you need to add a 5th, you have to modify the table. Also Column names get difficult to work with bom1, bom2, bomn..., this1, this2, thisn..., that1, that2... Yuck.

I would consider the above. And so long as you are using node-red-contrib-mysql you can use named parameters fed into msg.payload object as per the readme: node-red-node-mysql (node) - Node-RED

[BambosD]

Hello Mr. Colin,

some working tests i made using hardcoded data and template node using topic / payload (topic for columns , payload for the data coming from another node) like below.

working with Hardcode in template:

INSERT INTO test (c1 , c2 ,c3) 
VALUES ("4.147" ,4.23,"4.125");

this is working with dynamic values coming from msg.payload :

INSERT INTO test (c2 , c1 ,c3)
VALUES (? , ? , ?);

for now, i’m using a manual template as i mentioned before:

{
"timestamp" : "{{timestamp}}",
"horizontal" : {{poll1.horizontal}},
"plane" : {{poll1.plane}},
"ambient" : {{poll1.ambient}},
"bom" : {{poll1.bom}},
"energy" : {{poll3.energy}},
"apparent" : {{poll3.apparent}},
"active" : {{poll3.activep}},
"reactive" : {{poll3.reactiveq}},
"dcpower" : {{poll3.dcin}},
"activesetp" : {{poll2.setp}},
"reactivesetp" : {{poll2.setq}}
}

the object result is :

{"timestamp":"2025-08-07 00:01:26",
"horizontal":0,
"plane":0,
"ambient":21.8,
"bom":37.800000000000004,
"energy":0,
"apparent":0,
"active":200660838.60000002,
"reactive":0,
"dcpower":0,
"activesetp":-12.9,
"reactivesetp":-12.9}

so that’s all the values formatted and aggregated. a single record to make the SQL insert statement. any suggestions appreciated.

[BambosD]

thank you for your comments and your help. now is fixed with names and timestamp on a single object, please see my previous post.

the specific table will be fixed with those values with no additions.

but is interesting how we can store the json object as a whole in the database. how mysql can do that ? do we have to set the type of column as blob or something ?
And does this mean that we can’t query values by the column name ?

[jbudd]

@BambosD

how we can store the json object as a whole in the database

This seems unwise.

Probably because I don't use Modbus, I'm struggling to understand your descriptions of the setup.

It looks like you need to make 3 requests for various data attributes, and want these joined into a single message.
The usual way to join messages is to use a Join node.
In this case you would ensure that each output from Modbus has it's own topic, and join in manual mode, combining 3 messages.

Your picture of the data above has a payload: an array of 100 elements as well as originalPayload, also 100 elements, objectResults and arrayResults, yet all of these properties seem to be ignored. It's most confusing.

[Colin]

@BambosD

any suggestions appreciated

Don't do it that way, use a prepared query with names properties. That protects against SQL injection and is much easier to implement.

[smanjunath211]

this thread may interest you...

Building a dynamic msg.topic to create a mysql insert statement

Yes, understood, really useful. I even got it to work without naming the field names before VALUES clause, as i am updating ALL the fields. msg.topic = "INSERT INTO cc VALUES (:rdate, :totalcases, :totalcl, :caseperman, :costpercase, :cpm, :cpc)"; msg.payload = { "totalcases": 1234, "rdate": "2025-07-03", "caseperman": 49, "totalcl": 25, "costpercase": 39, "cpc": 25, "cpm": 52, } EDIT: Extremely Sorry @fogmajor for hijacking your thread. I will stop.