Send data from s7 to mysql

Hello my friends. I am working with mysql and s7. I was able to communicate and write values ​​in my database. I'm not able to write the data I get from the PLC to mysql. I would like to save every two minutes of the dice. Can you help me

My friends, I feel I'm on the right path. But I still can't

Check the query syntax.
I'm guessing you have numerical values to be pushed into the database.

A number does not need the single quotes ' '.
Those are needed only if the value is a string, that should be inserted into a column that is expecting a data type string.

For example:
Table created in MySQL database, has 4 columns.
Column1 & 4 data type = TEXT
Column 2 & 3 data type = INT

The query inside the function node will look something like:
msg.topic = "INSERT INTO oee_tal (column1,column2,column3,column4) VALUES ( ' " +var1+ " ' ," +var2+ "," +var3+ ", ' " +var4+ " ' )"

I have spaced out the single quotes, only to make it more readable

It's better (and safer against SQL injections) to use parameters and let the MySQL library do the escaping.

Just use ? as placeholder in your query, and pass your values as an array in msg.payload.

msg.topic = "INSERT INTO oee_tal (column1,column2,column3,column4) VALUES (?, ?, ?, ?)";

msg.payload = [var1, var2, var3, var4 ];

Hi, the data are values real and int. I still can't. Do I need to declare these variables in the function?

Are you getting an error? What is the error? Show us the value going in to your function, show us the value coming out of your function (use debug nodes before and after)

And lastly, share your full function code (use the </> code button then paste function code inside)

Im doing a test.See my function.

var disp = flow.get('disponibility')
var per = flow.get('performance')
var qua = flow.get('quality')
var oee = flow.get('oee')
var goodparts = flow.get('goodparts')
var notgoodparts = flow.get('notgoodparts')

msg.topic = "INSERT INTO oee_tal (disponibility) VALUES ("+disp+")";

return msg

I don't know what to test anymore

thanks my friends

See mysql. If i write for exemple

msg.topic = "INSERT INTO oee_tal (disponibility) VALUES (48)";

the code work very well

What do you see if go to the Context Data tab in the editor’s right sidebar and click the refresh button. Does an entry for disponibility show up?

My question is ‘does it exist?’ I don’t see you setting the flow variable in the flow you show.

should i declare the variable? How do I do this? Thanks

You cannot grab values from flow context if you don't put them in context in the first place.

You have choices...

  1. Get all values from PLC that you want to put in 1 table at the same time.
  2. Get values individually (like you do now) & store each value in context (use the change node)

Option 1 ensures data integrity (since all values are read on same plc scan)

Forgive me for saying but you appear to be fairly green with node-red and it's concepts so 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.

1 Like

Hello my friends. Thanks for you help. After transforming the variables I needed to a flow, I was able to carry out the process.

var disp = flow.get("disponibility");
var qua = flow.get("quality");
var per = flow.get("performance");
var oee = flow.get("oee");
var gp = flow.get("goodparts");
var ngp = flow.get("notgoodparts");
var pt = flow.get("partnumber");
var date = flow.get("date");
var tempo = flow.get("tempo");
var status = flow.get("status");

if (tempo == true){
msg.topic = "INSERT INTO oee_tal (disponibility,quality,performance,oee,goodparts,notgoodparts,`partnumber`,`Date`,`StatusMachine`) VALUES ("+disp+","+qua+","+per+","+oee+","+gp+","+ngp+",'"+pt+"','"+date+"','"+status+"')";

return msg;

Now, my challenge is how to automatically change two tabs in my dashboard. Do you have any ideas

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