Send data to MYSQL

[younesDV]

Hello I'm trying to send data to a database in MySQL, My Problem is that I can't insert data in the same time, the first type of data is a string and the second is a float. here's my nodes :

the first function :

and the second :

when I try to do Insert for the two data in same time it doesn't insert the data of num_AX correctly.
Thanks for your help!

[OriolFM]

Hi,

When you have non-strings and non-integers mixed in with strings and integers, it's often much easier to insert the data prepared as follows:

// Prepare the query

var settings_changed = 0;

if (msg.schmid.settings_changed){
   settings_changed = 1;
}

msg.payload = [msg.schmid.process_name, msg.schmid.status.log, settings_changed, JSON.stringify(msg.schmid)];

msg.topic="INSERT INTO unit_1.schmid_log (log_id, log_ts, process_name, log, settings_changed, schmid_obj) VALUES (uuID(), current_timestamp(), ?, ?, ?, ?);";

return msg;

As you can see, I prepare the payload as an array of the variables I want to insert at the DB. In the actual query, I add "?" as placeholders for the variables in the array. The MySQL substitutes each "?" for the variable in the array (in order).

Whenever I had issues with inserting variables in the MySQL and could not do it directly on the string, this way helped.

[younesDV]

thanks for answering, so I use only one function for the float and the string ?
and should I delete the node Json ?

[OriolFM]

Well, how is the variable that you are getting? is it a string, or does it come already as a JSON?

You can see I use stringify to convert the JSON to a string before inserting it in the DB.

If you're already getting the JSON as a string and you want to store it in the DB, there's no need to convert twice (to JSON first and then to string) unless you need to edit something in it.

If it's just read it and store it, I'd say just pass it as it is.

[jbudd]

@younesDV

My Problem is that I can't insert data in the same time

Do you mean that you want to INSERT a single record with both consigne_vitesse and Num_AX?

If so, you need a Join node setup so that both values are available in the same message.

There is an alternative prepared query syntax something like this, I find it clearer than the ?, ? version.

msg.topic = "INSERT INTO data1 ( consigne_vitesse, Num_AX) VALUES ( :consigne_vitesse, :Num_AX)
msg.payload = {
"Num_AX": msg.Num_Ax,
"consigne_vitesse": msg.consigne_vitesse
}

[younesDV]

Yes I want to Insert a single record.
What is the configuration of join ? Is it Automatically or manuely ?

[Steve-Mcl]

See this article in the cookbook for an example of how to join messages into one object.

[Steve-Mcl]

in short:

• in one flow message
  • set msg.topic to Num_Ax
  • set msg.payload to the value
• in other flow message
  • set msg.topic to consigne_vitesse
  • set msg.payload to the value
• set join to manual mode & select 2 items
• add a debug after join node to verify you now have a payload with BOTH values in there
  • e.g { payload: { Num_Ax: 12345, consigne_vitesse: "whatever" } }
• use that payload along with your SQL in msg.topic
  • e.g. INSERT INTO data1 ( consigne_vitesse, Num_AX) VALUES ( :consigne_vitesse, :Num_AX)

TIP:

Use injects and debugs firstly to make sure you end up with the msg:

topic: "INSERT INTO data1 ( consigne_vitesse, Num_AX) VALUES ( :consigne_vitesse, :Num_AX)"
payload: { Num_Ax: 12345, consigne_vitesse: "whatever" }

[OriolFM]

Oh, right, I didn't understand that part.

A few considerations:

• These look like S7 nodes to me. Are you reading 2 variables from the same PLC? If so, it's better to read them with just one node. You'll have them both on the same message.
• If they aren't on the same PLC, and therefore you can't read them together but the interval for reading them is synced, it's better to join the two messages as one with the Join node, then write to DB, as Steve pointed out.
• If they aren't on the same PLC and the reading isn't synced, it's better to either use context to store the most updated values in a structure, and write to DB everytime the context is updated, or keep them updated and write at regular intervals.
• There's also the option of sending the variables separately to the DB and updating just the column you need, depending on the frequency of update, it might work well.

All in all, I'd say that using context and writing to the DB every time a value has changed is the optimal route.

[younesDV]

It's finally working, thank you all, here's the solution :

[younesDV]

I have another problem, I added a boolean node START, and while this node is true I want to INSERT data, I tried this but it's not working.

Thanks

[jbudd]

You seem to have missed the important point that the msg object has a fleeting existence.

A node only knows about the current message it is processing.
msg.payload.consigne_vitesse does not hang around until you want to use it, like a variable in a more traditional programming language.

So as we explained above, all of the data you want to work with has to be in a single message.
That's why you have a join node, so that consigne_vitesse and Num_AX are both in the same message.

You need to get msg.START into the same message as the other data.

[dynamicdave]

Another method is to use a flow variable to capture the state of 'serial START'.

If you connect your Serial START node to a 'change' node you can store its value.

Setting in 'change' node is this...

Then in 'function 10' change the Javascript to this...

let serial_start = flow.get("serial_start)") || false;
if (serial_start === true) {
    msg.topic = ........
    return msg;
}
else {
    return null;
}

[Steve-Mcl]

@dynamicdave

Then in 'function 10' change the Javascript to this...

let serial_start = flow.get("serial_start)") || false;
if (serial_start === true) {
    msg.topic = ........
    return msg;
}
else {
    return null;
}

Or, add a simple switch as a PASS/NO-PASS gate

property: flow.serial_start
item1: "is true"

[younesDV]

My database is deconnected now
here's my debug :

[younesDV]

you were righr Steve I added a switch and it works, thank you all !

[jbudd]

@younesDV

My database is deconnected now

Can you explain what you mean?

You seem to have chosen the suggestion of a flow context variable for serial_start.
In that case the variable is available to your function node without joining it into the message.

It is a perfectly viable approach but be aware that once you set flow.serial_start to true, that value stays the same until you change it, whatever messages arrive in your flow.

[dynamicdave]

Just to clarify... in the suggestion I sent you - you do not need to connect the 'set flow.serial_start' node to anything. Just leave the output port/pin unconnected. The flow variable will be available to all other nodes in your flow - for example the 'function 10' node.

Note:
If you had shared your Node-RED flow (rather than posting screenshots) then people could have modified your flow and posted it back (which might have avoid some of the confusion).