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

[Evan7]

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;

[TotallyInformation]

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:

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.

[Evan7]

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.

[jbudd]

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?

[Steve-Mcl]

This is the problem...

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

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, section "Prepared Queries".

Added bonus for free

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

[Evan7]

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.

[Evan7]

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.

[jbudd]

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)

[Evan7]

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

[Steve-Mcl]

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