# Send data to MYSQL

**URL:** <https://discourse.nodered.org/t/send-data-to-mysql/87606>\
**Category:** General\
**Created:** [30 April 2024 07:20 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606 "2024-04-30T07:20:13Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![younesDV](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/younesdv/32/88130_2.png) [@younesDV](https://discourse.nodered.org/u/younesDV)\
**Post date:** [30 April 2024 07:20 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/1 "2024-04-30T07:20:13Z")

</div>

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 :

 ![Capture](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/b/7b955bb02eadc73956f2332f389d0c06f2ca6b4c.png)  
the first function :  
 ![Fnct1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/3/43ce650af56d0851c139460d3f0a63c787eeecd1.png)  
and the second :  
 ![fct2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/6/66763c7db7f50b840a90df1a35a43cc9c0d7c166.png)  
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!

---

<div class="post-metadata">

**Author:** ![OriolFM](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/oriolfm/32/30710_2.png) [@OriolFM](https://discourse.nodered.org/u/OriolFM)\
**Post date:** [30 April 2024 07:51 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/2 "2024-04-30T07:51:44Z")

</div>

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:

```auto
// 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.

---

<div class="post-metadata">

**Author:** ![younesDV](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/younesdv/32/88130_2.png) [@younesDV](https://discourse.nodered.org/u/younesDV)\
**Post date:** [30 April 2024 07:56 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/3 "2024-04-30T07:56:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![OriolFM](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/oriolfm/32/30710_2.png) [@OriolFM](https://discourse.nodered.org/u/OriolFM)\
**Post date:** [30 April 2024 08:03 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/4 "2024-04-30T08:03:17Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [30 April 2024 08:08 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/5 "2024-04-30T08:08:05Z")

</div>

> [@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.

```auto
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
}

```

---

<div class="post-metadata">

**Author:** ![younesDV](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/younesdv/32/88130_2.png) [@younesDV](https://discourse.nodered.org/u/younesDV)\
**Post date:** [30 April 2024 08:50 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/6 "2024-04-30T08:50:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [30 April 2024 09:02 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/7 "2024-04-30T09:02:46Z")

</div>

See [this article in the cookbook](https://cookbook.nodered.org/basic/join-streams) for an example of how to join messages into one object.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [30 April 2024 09:09 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/8 "2024-04-30T09:09:26Z")

</div>

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:

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

```

---

<div class="post-metadata">

**Author:** ![OriolFM](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/oriolfm/32/30710_2.png) [@OriolFM](https://discourse.nodered.org/u/OriolFM)\
**Post date:** [30 April 2024 09:52 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/9 "2024-04-30T09:52:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![younesDV](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/younesdv/32/88130_2.png) [@younesDV](https://discourse.nodered.org/u/younesDV)\
**Post date:** [2 May 2024 08:04 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/10 "2024-05-02T08:04:45Z")

</div>

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

 ![capt1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/0/c0efc025f7c72bb7fc4717c2c23dedc083345aff.png)  
 ![Capture2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/0/10e6a1bf14288b9bacf4367b0bbe68496fdda223.png)  
 ![Capture3](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/4/d449bbe07d8e99af4a3678d74de3513764ea0316.png)

---

<div class="post-metadata">

**Author:** ![younesDV](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/younesdv/32/88130_2.png) [@younesDV](https://discourse.nodered.org/u/younesDV)\
**Post date:** [2 May 2024 09:20 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/11 "2024-05-02T09:20:22Z")

</div>

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.

 ![capture21](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/c/7cd9be3c775bf585fb3f3d56e2cd149d418b9a5a.png)  
 ![capture22](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/8/58a210c4fd41c638914e70df8f7f6a7276b28ec2.png)  
Thanks

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [2 May 2024 10:13 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/12 "2024-05-02T10:13:50Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [2 May 2024 10:35 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/13 "2024-05-02T10:35:03Z")

</div>

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.  
 ![thursday_mysql_A](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/2/925644ce5f7975e7190f7d1e675f675ab18a1d07.png)  
Setting in 'change' node is this...  
 ![thursday_mysql_B](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/a/9a0edb5fd012ca30482ad8235d47d35e5f1adaea.png)

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

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

```

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [2 May 2024 10:36 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/14 "2024-05-02T10:36:51Z")

</div>

> [@dynamicdave](#):
>
> Then in 'function 10' change the Javascript to this...
> 
> ```auto
> 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"

---

<div class="post-metadata">

**Author:** ![younesDV](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/younesdv/32/88130_2.png) [@younesDV](https://discourse.nodered.org/u/younesDV)\
**Post date:** [2 May 2024 12:10 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/15 "2024-05-02T12:10:50Z")

</div>

![capture331](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/7/771257b17741e50039657889fcb875ebbdba2e75.png)

 ![Capture32](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/1/a111f4d99596c46ed6c26516931ef807bd517344.png)  
My database is deconnected now  
here's my debug :  
 ![Capture33](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/1/b1eb7fbe880db243c586d850091cbfeb54d8f05d.png)

---

<div class="post-metadata">

**Author:** ![younesDV](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/younesdv/32/88130_2.png) [@younesDV](https://discourse.nodered.org/u/younesDV)\
**Post date:** [2 May 2024 12:22 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/16 "2024-05-02T12:22:51Z")

</div>

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

 ![capture41](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/b/fbe5abcb9216de32b26387050cd4683cf15d3980.png)  
 ![capture42](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/f/7f1f083ec84babc842d508632452176aa2becd93.png)

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [2 May 2024 12:25 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/17 "2024-05-02T12:25:42Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [2 May 2024 17:35 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/18 "2024-05-02T17:35:45Z")

</div>

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.  
 ![thursday_mysql_C](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/6/76e84b12e1b609e2eca2990d132ebc0e0eac0afa.png)  
**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).

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [16 May 2024 17:36 UTC](https://discourse.nodered.org/t/send-data-to-mysql/87606/19 "2024-05-16T17:36:12Z")

</div>

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