Insert Into MySQL Database

[swanside]

Hi All
I have now got a connection to work for a remote MySQL database and I have this in my function

Current = msg.payload.Current
Voltage = msg.payload.Voltage
msg.topic = "INSERT INTO Data(`Current`, `Voltage`) VALUES ('Current', 'Voltage')";
return msg;

But it actually put in the words Current and Voltage

But it does read the correct values, so can somebody please help me with the Insert IN to please so it puts the payload in?
Thank you

[knolleary]

Hi @swanside

The code doesn't know you want to insert the values of Current and Voltage into the string - it just sees the text Current and Voltage.

One way to do it is to build the string up by joining together the different parts:

var Current = msg.payload.Current
var Voltage = msg.payload.Voltage
msg.topic = "INSERT INTO Data(`Current`, `Voltage`) VALUES ('"+Current+"', '"+Voltage+"')";
return msg;

[knolleary]

Although I may have replied a bit too quickly as your screenshot of the messages shows you don't have msg.payload.Current and msg.payload.Voltage as your Function code assumes.

Instead you appear to have the two values arriving in different messages, with msg.topic identifying whether its Current or Voltage, and the corresponding value in msg.payload.

So you first need to get those two separate messages into one message so you can do a single insert with them both.

To do that, add a Join node, configured as follows:

[swanside]

Thanks.
Was just going to reply when you latest message popped up. Will give that a try. Thank you

[swanside]

Thanks It is giving an undefined in the database

[knolleary]

At the moment it is hard to see how all of those screenshots relate to each other.

What does your flow look like? Where are the Debug nodes you are showing the output of in relation to the Function node and the MySQL node? Where have you put the Join node?

The Debug screenshot shows you have a message where msg.payload in now an object - but we can't see what that object looks like. Expand that out to confirm the payload contains a Current and Voltage property.

[swanside]

This is my actual flow if that will help make sense of what I am trying to do. I have deleted the MySQl server just for security.
Thank you

flows(7).json (24.6 KB)

[knolleary]

Sorry, I'm not in a position to import a large flow containly lots of dashboard nodes and the like.

Can you just share a screenshot that shows the relationship between the Join, Function and MySQl nodes?

[swanside]

Oh Sorry about that. Yep here are some shots. Thank You.

[knolleary]

So you have the Join node combining the Voltage/Current messages (although you haven't expanded the Debug sidebar to confirm that fact).

But you aren't passing the output of the Join node to the Function node, so the data doesn't go anywhere.

[swanside]

Oh I see.
OK I will give that a try, go to go to work now but will try when I get home and update the post. Thank You

[swanside]

HI.
Right Sorry about the delay getting back. I have edited the Join mode to the below, so the first value is my Voltage and the second is my Current, and then it hits the Function it gives the error in the Insert INTO

[Colin]

Did you want an array from the Join node? If not then don't select it. I suspect you want Key/Value objects instead.

[swanside]

Thanks Colin.
I changed it to a key/value Object, but still shows an array. I will reboot the device

[swanside]

FANTASTIC GUYS.

Thank for all your help I have it sending my data to a remote MySQL database now.

[swanside]

Now while it is sending data it seems also send undefind data also, and it looks like it comes from the same Function Modual. Any reason why that would occur please

My mistake, It was sending data when received from the Modbus interface and I also had an inject on it alos, so it would send no data. All done now, Time to play with the PHP and hTML to make a nice datalogger. Thank again