PLC machine data sent Via MQTT to Node Red Then to SQL Database

General
21

Ok I got the join to add the three payloads to one but it appears that its adding the paths as part of the string in the message. I may have to remove that somehow or the IT guy may have to change the SQL script to ignore it.

joined
joined1468Ɨ209 40.8 KB

Would I use a FUNCTION node to insert to database? Im not seeing an INSERT node in my list.

I did use the function to test sending data to the database with an Inject node trigger but the script was just to insert values that were static in the script text.
EX: msg.topic = "INSERT INTO pd_record_set (rec_press, rec_datetime, rec_part, rec_stock, rec_stockdate, rec_operator, rec_heatcount, rec_transfertime, rec_curetime, rec_moldset, rec_moldread, rec_topset, rec_topread, rec_ramstatus, rec_potread, rec_idletime, rec_unloadtime, rec_moldsetter, rec_scrapheat, rec_scrapcat) VALUES (0, NOW(), 10005, 10000, '2024-08-23', 10162,0,0,0,0,0,0,0,0,0,0,0,10162,0,0)"
return msg;

22

It isn't part of the string. Thereā€™s a great page in the docs (Working with messages : Node-RED) that will explain how to use the debug panel to find the right path/value for any data item.

Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.

BX00Cy7yHi

You will need to exatract the properties you want and insert them into the INSERT statement.

I recommend watching this playlist: Node-RED Essentials. The videos are done by the developers of node-red. They're nice & short and to the point. You will understand a whole lot more in about 1 hour. A small investment for a lot of gain.

23

Great info everyone! I did get the join node to work properly, but was also able to work somethings around on the PLC side so it only sends one payload with all the data in it.
used a function node to insert the data from the payload to the corresponding rows and it was received into the table as it should.

SQL Test
SQL Test1696Ɨ484 105 KB

Here is the function code:

msg.topic = "INSERT INTO pd_record_set (rec_press, rec_part, rec_stock, rec_stockdate, rec_operator, rec_heatcount, rec_transfertime, rec_curetime, rec_moldset, rec_moldread, rec_topset, rec_topread, rec_ramstatus, rec_potread, rec_idletime, rec_unloadtime, rec_moldsetter, rec_scrapheat, rec_scrapcat) VALUES (" + msg.payload + ")";

return msg;

Issue I have now is the data that was sent was forced, meaning I can make it whatever I need it to be, on the PLC side. The guy that set up the SQL database says that anything that needs to be put into the table that is a string will need to have single quotes around it (EX. the stock ID: 'EP4150' & dates '2024-09-09')

How would I go about accomplishing this? Im trying to see if it can be done on the PLC side, if not Ill have to do it here.
Below are the rows of the table that need the quote marks added and a sample data point
rec_press '1'
rec_part '10370F'
rec_stock 'EP4150'
rec_stockdate '2024-09-09'

24

Your function node, which I have split into several lines for easier reading, looks like this

msg.topic = "INSERT INTO pd_record_set ("
msg.topic += "rec_press, rec_part, rec_stock, rec_stockdate, "
msg.topic += "rec_operator, rec_heatcount, rec_transfertime, "
msg.topic += "rec_curetime, rec_moldset, rec_moldread, rec_topset, "
msg.topic += "rec_topread, rec_ramstatus, rec_potread, rec_idletime, "
msg.topic += "rec_unloadtime, rec_moldsetter, rec_scrapheat, rec_scrapcat) "
msg.topic += "VALUES (" + msg.payload + ")";

return msg;

I think you are setting yourself up for database corruption.

Firstly it depends on msg.payload containing all the values, correctly comma sepraated and in the expected order.

Secondly it is considered bad practice to use this format of query because it is vulnerable to SQL injection attacks.

INSERT INTO table (field1, field2) VALUES (1600, "Pennsylvania Avenue")

Better to use a "prepared query", something like this

msg.topic = INSERT INTO table (field1, field2) VALUES (:number, :street)
msg.payload = {
"number": 1600,
"street": "Pennsylvania Avenue"
}

Of course that would also require you to move each sensor value into it's own property of msg.payload (msg.payload.number or msg.payload.street in the above example).
The upside to this extra effort is that it will save you hours of debugging and error correction when you need to tweek the data in 6 month's time.

25

Alright, wanted to update on the situation.

Seems to be operating great. Ive tried to make some errors that I feel would possibly come up in the field and have since been addressed and taken care of.

Now I could use some input on how to get things FROM an SQL database TO the PLC via MQTT.
Is it basically the same except in reverse? Im not sure how to initiate the transfer of data for starters. The SQL and MQTT out nodes need something in front of it, and the Inject node is a manual trigger or time based trigger rather than an event.

Plan would be to send a message VIA MQTT to the SQL requesting that recipe data be sent from a data table to the PLC via MQTT, then the PLC would unpack the payload and insert it in the proper fields.

26

Add an MQTT In node subscribing to the message coming in on MQTT, then use that message to trigger a mysql node to run the appropriate SELECT query.