Spliting Payload to Insert into Mysql

[nksky]

I'm getting stuck splitting a payload to insert it into MySql table, I know its something simple, but I cant figure it. Any help greatly appreciated.

This is my payload

and this is my function node

var myDataArray     = msg.payload.split(";");

var latitude        = myDataArray[1];
var longitude       = myDataArray[2];
var soilTemp        = myDataArray[3];
var deviceName      = myDataArray[4];
var batteryVoltage  = myDataArray[5];
var timestamp       = myDataArray[6];

msg.topic = "INSERT INTO mkrtrial (lat,lng,soil,device,batt,epoch) VALUES ('"+ msg.payload[latitude]+"','"+ msg.payload[longitude]+"','"+ msg.payload[soilTemp]+"','"+ msg.payload[batteryVoltage]+"','"+ msg.payload[deviceName]+"','"+ msg.payload[timestamp]+"')";
//msg.topic = "INSERT INTO mkrtrial (lat,lng,soil,device,batt,epoch) VALUES ('"+ msg.payload[1]+"','"+ msg.payload[2]+"','"+ msg.payload[3]+"','"+ msg.payload[4]+"','"+ msg.payload[5]+"','"+ msg.payload[6]+"')";

return msg;

I've tried the commented out line aswell as a few other possibilities, but I'm just getting 0 values in.

Thanks in advance...

[Steve-Mcl]

@nksky

This is my payload

Where does that payload get generated? It's almost (bit isn't) JSON

Might be better making the source of data valid JSON and then you can simply access properties rather than splitting on ;

Btw, to explain why it not working, add this just before the return....

msg.myDataArray = myDataArray;

Then connect a debug node after that node (with the option to show complete message set) then see what you get in the debug window. I think you will realise your spit doesn't work.

To clarify, try to correct the source data into valid JSON. It will simplify everything.

[nksky]

Thank you very much. The data is coming from an MKRGSM arduino device, I`ll need to change my string from that first.

Thanks for the pointer tho.

[Steve-Mcl]

One tip,

Copy and paste your generated string into one of the many online JSON validators - useful to test your transmission data.

[nksky]

@Steve-Mcl

JSON validators

Ok, Changed my string, and it validated, but still not entering... Am I splitting it right?

msg.topic = "INSERT INTO mkrtrial (lat,lng,soil,device,batt,epoch) VALUES ('"+latitude+"','"+longitude+"','"+soilTemp+"','"+batteryVoltage+"','"+deviceName+"','"+timestamp+"')";

[Steve-Mcl]

There is no need to split.

Feed the msg into a JSON node then feed that into a debug node (with show complete message option selected)

You will see the data becomes an object where you can access msg.payload.deviceName etc

I strongly recommend you read
Working with messages it shows you some nifty tips for this kind of thing.

Should only take you 5mins but you won't even need to ask questions like this one if you absorb that info.

[nksky]

Thanks Steve, got it all sorted, the JSON string from the arduino was throwing me off. Much appreciated.