I want to save the PLC output data to MySQL with mqtt broker, is my flow correct? Because from a week ago the error continues and the data does not enter the database. Or does anyone have a reference related to this?
I dont see how it works without a join node.
what is in your function node?
also, the mysql node is not configured correctly, (red triangle seen on the node)
here is the content of the function node :
var speed = "";
var printing = "";
var tensionUW = "";
var tensionIF = "";
var tensionOF = "";
var tensionRW = "";
var uw = "";
var rw = "";
var productAAXISCNT = "";
var productBAXISCNT = "";
var productTotalAXISCNT = "";
// Cari nilai yang sesuai dengan pola topik MQTT
msg.payload.forEach(function(item) {
switch (item.topic) {
case '/speed/data1/ALTPRG01':
speed = item.payload;
break;
case '/printing/data2/ALTPRG01':
printing = item.payload;
break;
case '/tensionuw/data3/ALTPRG01':
tensionUW = item.payload;
break;
case '/tensionif/data4/ALTPRG01':
tensionIF = item.payload;
break;
case '/tensionof/data5/ALTPRG01':
tensionOF = item.payload;
break;
case '/tensionrw/data6/ALTPRG01':
tensionRW = item.payload;
break;
case '/uw/data7/ALTPRG01':
uw = item.payload;
break;
case '/rw/data8/ALTPRG01':
rw = item.payload;
break;
case '/productaaxiscnt/data13/ALTPRG01':
productAAXISCNT = item.payload;
break;
case '/productbaxiscnt/data14/ALTPRG01':
productBAXISCNT = item.payload;
break;
case '/productotalaxiscnt/data15/ALTPRG01':
productTotalAXISCNT = item.payload;
break;
}
});
// Buat objek data
var data = {
"Speed 3": speed,
"Printing 3": printing,
"Tension UW (N)": tensionUW,
"Tension IF (N)": tensionIF,
"Tension Of (N)": tensionOF,
"Tension RW (N)": tensionRW,
"UW (mm)": uw,
"RW (mm)": rw,
"Product A-AXIS CNT (m)": productAAXISCNT,
"Product B-AXIS CNT (m)": productBAXISCNT,
"Product Total AXIS CNT (m)": productTotalAXISCNT
};
msg.payload = data;
return msg;
For the mysql node, it can be connected but when the flow is run, it continues to error again, sorry, I took the photo when I hadn't connected it, but it was actually connected, but when I ran the node, it became an error.
Can you explain why you are passing data through mqtt-in and mqtt-out nodes rather than wiring things directly together?
Which Mysql node do you have?
You don't seem to have an SQL INSERT statement.
For node-red-node-mysql it should be in msg.topic.
If you did have an INSERT statement, you would be creating a database record every time a message arrived on any of the inputs.
Is that what you intend, or do you expect it to wait until all of your variables are populated from different messages?
I suspect, as @smanjunath211 says, you are missing a join node to combine all these messages into one.
Maybe you can show us an example of your input data and the structure of the database table[s] you want to populate.
Looking at the red triangle for the Mysql node in your first post you didn't configure the node correct or there's another issue with this node.
how are you going to get the data in reality ?
is it a timeseries database ? I neither see a datetime field nor an unique id field
If you send a message like
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
in a msg.topic , it will update the database, provided you have a valid connection status through mysql node..
[{"id":"f714f726d5fcdb6b","type":"template","z":"c08cea72100e425d","name":"Insert into Database","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO table_name (column1, column2, column3)\nVALUES ({{msg.payload1}}, {{msg.payload2}}, {{msg.payload3}});","output":"str","x":430,"y":2200,"wires":[[]]}]
firstly, you are misunderstanding how node-red works.
In short, your flow will never work because each wire that sends a msg does so at a different time.
Messages will NEVER reach the input of a function (or any node) at the same time. That means your function will NEVER have all of the values in your DB will fill up with striped data or will simply fail to make any entries (depending on your schema)
So, you could use a join node to make a single msg with all your data, but there are other fundamentals you need to consider, like network traffic, data consistency etc.
Data consistency:
If the values you are reading from the PLC are in the same PLC address range, I strongly recommend you read them all in one go. Here is a doc that hopefully explains why this is important: Modernize your legacy industrial data. Part 2. • FlowFuse
You have given us a screen capture of phpMyAdmin so let's look at your database structure.
As others have noted, there is no indication of the time when a record is created.
I don't use phpMyAdmin, preferring the Mariadb/Mysql command line.
Nor am I a fan of mixed case, spaces, brackets etc in my fieldnames.
I would define a table like this (loosely based on your data)
create table realtimeexample (id INT(11) NOT NULL AUTO_INCREMENT,
timestamp DATETIME NOT NULL DEFAULT now(),
speed INT(11) ,
printing TEXT(100),
tensionuw INT(11),
PRIMARY KEY (id));
Now each record has both an ID and a timestamp.
Note that these are populated automatically when you INSERT.
The table has a primary key too (id).
An example flow to insert the data:
[{"id":"58c6e0663cc05ed8","type":"function","z":"215da6ba2a558d28","name":"function 58","func":"const speed = 123\nconst printing = \"You need to populate these values from your input data\"\nconst tensionuw = 42\n\n// msg.payload contains the data fields\nmsg.payload = { \n\"speed\": speed, \"printing\": printing, \"tensionuw\": tensionuw\n}\n\n// msg.topic contains the SQL\nmsg.topic = \"INSERT INTO realtimeexample (speed, printing, tensionuw)\"\nmsg.topic += \" VALUES (:speed, :printing, :tensionuw)\"\n\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":390,"y":180,"wires":[["04d2f717eff7cce8"]]},{"id":"d21bebdce2a14427","type":"inject","z":"215da6ba2a558d28","name":"Go","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":230,"y":180,"wires":[["58c6e0663cc05ed8"]]},{"id":"04d2f717eff7cce8","type":"debug","z":"215da6ba2a558d28","name":"To your database","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":590,"y":180,"wires":[]}]
You didn't answer all my questions or show a sample of your input data.
I see no point in giving you hints based on guesswork about getting all the input data into a single record.
I'm sorry about this, but not yet running
what have you tried from the suggestions given to you and details asked from you
It is interesting that the only SQL @Sobri has shown us inserts empty strings in every field yet the phpMyAdmin screendump shows zero in every field apart from '$ProductName' in ProductName.
I don't believe that the SQL shown can possibly create the records shown. I would expect an SQL error attempting to write a string to a numeric field.
@Sobri: I suggest that you create a new data table with just a few fields and a simple flow to insert records.
If you still get problems you have to share the flow, the input data, the database schema and the records created. Then there is a chance we might be able to help you fix it.
I've tried but haven't been able to
Does that mean I have to recreate the column in the database?
