# ISSUE with storing MQTT values to mySQL database : the values store as "undefined". Any guidence is much appreciated

**URL:** <https://discourse.nodered.org/t/issue-with-storing-mqtt-values-to-mysql-database-the-values-store-as-undefined-any-guidence-is-much-appreciated/59811>\
**Category:** General\
**Tags:** database\
**Created:** [13 March 2022 19:22 UTC](https://discourse.nodered.org/t/issue-with-storing-mqtt-values-to-mysql-database-the-values-store-as-undefined-any-guidence-is-much-appreciated/59811 "2022-03-13T19:22:08Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Evan7](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evan7/32/64141_2.png) [@Evan7](https://discourse.nodered.org/u/Evan7)\
**Post date:** [13 March 2022 19:22 UTC](https://discourse.nodered.org/t/issue-with-storing-mqtt-values-to-mysql-database-the-values-store-as-undefined-any-guidence-is-much-appreciated/59811/1 "2022-03-13T19:22:08Z")

</div>

## DEBUG WINDOW:

INSERT INTO readingsreceived(`ssid`,`rssi`,`scan_mac`,`sensor_mac`)VALUES('undefined','NaN','undefined','undefined'); : msg.payload : ResultSetHeader

"[object Object]"

* * *

## FUNCTION NODE QUERY:

ssid = msg.payload.ssid;  
rssi = parseInt(msg.payload.rssi);  
scan\_mac = msg.payload.scan\_mac;  
sensor\_mac = msg.payload.sensor\_mac;

insert = "INSERT INTO readingsreceived(`ssid`,`rssi`,`scan_mac`,`sensor_mac`)" +  
"VALUES('"+ssid+"','"+rssi+"','"+scan\_mac+"','"+sensor\_mac+"');";

msg.topic = insert;  
return msg;

 ![flow-pic](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/f/2f8f65c858a67071dc09b2449e9debb2d4c9fc14.png)

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [13 March 2022 19:44 UTC](https://discourse.nodered.org/t/issue-with-storing-mqtt-values-to-mysql-database-the-values-store-as-undefined-any-guidence-is-much-appreciated/59811/2 "2022-03-13T19:44:32Z")

</div>

Firstly, welcome to the forum. However, could you please read up on how to include code in a post? Thanks, it makes things much easier to read.

Now to your problem. The flow you've shown doesn't quite do what you think. Those separate change nodes all need to be in a single node because the result of your current flow is that you are sending 4 separate messages to the function node with each message only containing a single value not all of them as you are assuming.

A couple of other points. You can use back-tick quotes in your insert to make things rather easier to read:

```auto
insert = `INSERT INTO readingsreceived(ssid,rssi,scan_mac,sensor_mac) VALUES("${ssid}", "${rssi}", "${scan_mac}", "${sensor_mac}")`

```

Secondly, it is wise to use a "prepared statement" rather than a manually constructed insert statement. Those are more efficient but more importantly, they are less susceptible to SQL attacks.

---

<div class="post-metadata">

**Author:** ![Evan7](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evan7/32/64141_2.png) [@Evan7](https://discourse.nodered.org/u/Evan7)\
**Post date:** [14 March 2022 06:12 UTC](https://discourse.nodered.org/t/issue-with-storing-mqtt-values-to-mysql-database-the-values-store-as-undefined-any-guidence-is-much-appreciated/59811/3 "2022-03-14T06:12:02Z")

</div>

Hi, thank you for the prompt response, sorry about the delay to reply. So as you suggested I started with bringing all four change node values to one node, But I am getting an error.

 ![round2-flow-2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/9/698c92324361e3c8db847b9882d90e8f3c135400.png)  
 ![round2-flow](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/3/130ead4c39462c0b3ecc54aebf3f15b25f8d42ba.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:** [14 March 2022 08:16 UTC](https://discourse.nodered.org/t/issue-with-storing-mqtt-values-to-mysql-database-the-values-store-as-undefined-any-guidence-is-much-appreciated/59811/4 "2022-03-14T08:16:05Z")

</div>

What does the data coming from your json node look like - is it one message with all four elements in it, or do they arrive as four seperate messages?

---

<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:** [14 March 2022 08:28 UTC](https://discourse.nodered.org/t/issue-with-storing-mqtt-values-to-mysql-database-the-values-store-as-undefined-any-guidence-is-much-appreciated/59811/5 "2022-03-14T08:28:43Z")

</div>

This is the problem...

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/3/c3412811c8657bd60fd88146d4a58786b3152e09.png)

The thing is - you dont even need to do this at all!

### The better solution...

In your function where you build the query, simply use `:named_parameters` and the values will be picked out of the payload...

```auto
msg.payload.rssi = parseInt(msg.payload.rssi);
msg.topic = "INSERT INTO readingsreceived(ssid,rssi,scan_mac,sensor_mac) VALUES (:ssid,:rssi,:scan_mac,:sensor_mac);"
return msg;

```

REF: See the [readme](https://flows.nodered.org/node/node-red-node-mysql), section "Prepared Queries".

### Added bonus for free

Using prepared statements will also help you avoid SQL injection issues.

---

<div class="post-metadata">

**Author:** ![Evan7](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evan7/32/64141_2.png) [@Evan7](https://discourse.nodered.org/u/Evan7)\
**Post date:** [14 March 2022 12:20 UTC](https://discourse.nodered.org/t/issue-with-storing-mqtt-values-to-mysql-database-the-values-store-as-undefined-any-guidence-is-much-appreciated/59811/6 "2022-03-14T12:20:05Z")

</div>

It's a JSON, where the key and value are identified and highlighted. slightly different to what you see in the debug window in the image I have shown.

---

<div class="post-metadata">

**Author:** ![Evan7](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evan7/32/64141_2.png) [@Evan7](https://discourse.nodered.org/u/Evan7)\
**Post date:** [14 March 2022 12:33 UTC](https://discourse.nodered.org/t/issue-with-storing-mqtt-values-to-mysql-database-the-values-store-as-undefined-any-guidence-is-much-appreciated/59811/7 "2022-03-14T12:33:18Z")

</div>

Hey Steve, your solution worked , thank you very much. For my knowledge please elaborate what was actually happening to the values , as in why were they not going to the database.

---

<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:** [14 March 2022 12:33 UTC](https://discourse.nodered.org/t/issue-with-storing-mqtt-values-to-mysql-database-the-values-store-as-undefined-any-guidence-is-much-appreciated/59811/8 "2022-03-14T12:33:21Z")

</div>

Since all the related data arrives in a single message, as Steve says, you don't need the change node at all.

And you may not need the json node, if your mqtt-in is set to receive a parsed JSON object (depending what else is connected to mqtt-in)

---

<div class="post-metadata">

**Author:** ![Evan7](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evan7/32/64141_2.png) [@Evan7](https://discourse.nodered.org/u/Evan7)\
**Post date:** [14 March 2022 13:02 UTC](https://discourse.nodered.org/t/issue-with-storing-mqtt-values-to-mysql-database-the-values-store-as-undefined-any-guidence-is-much-appreciated/59811/9 "2022-03-14T13:02:07Z")

</div>

I still used the JSON node , but did away with the change nodes. Its working fine now, thanks for helping out.

 ![round3-flow](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/d/5d78559c88fd33f0a315034ef8625730b4842bc7.png)

---

<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:** [14 March 2022 13:08 UTC](https://discourse.nodered.org/t/issue-with-storing-mqtt-values-to-mysql-database-the-values-store-as-undefined-any-guidence-is-much-appreciated/59811/10 "2022-03-14T13:08:52Z")

</div>

> [@Evan7](#):
>
> For my knowledge please elaborate what was actually happening to the values , as in why were they not going to the database

Did this not make sense 👇

> [@Steve-Mcl](#):
>
> This is the problem...
> 
> ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/3/c3412811c8657bd60fd88146d4a58786b3152e09.png)

---

<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:** [13 May 2022 13:08 UTC](https://discourse.nodered.org/t/issue-with-storing-mqtt-values-to-mysql-database-the-values-store-as-undefined-any-guidence-is-much-appreciated/59811/11 "2022-05-13T13:08:55Z")

</div>

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