# Spliting Payload to Insert into Mysql

**URL:** <https://discourse.nodered.org/t/spliting-payload-to-insert-into-mysql/24193>\
**Category:** General\
**Created:** [3 April 2020 22:45 UTC](https://discourse.nodered.org/t/spliting-payload-to-insert-into-mysql/24193 "2020-04-03T22:45:57Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![nksky](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/nksky/32/9173_2.png) [@nksky](https://discourse.nodered.org/u/nksky)\
**Post date:** [3 April 2020 22:45 UTC](https://discourse.nodered.org/t/spliting-payload-to-insert-into-mysql/24193/1 "2020-04-03T22:45:57Z")

</div>

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 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/1/f1457a95fa9e885f981b5450c97b54b4642acfb5.png)

and this is my function node

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

---

<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:** [3 April 2020 23:16 UTC](https://discourse.nodered.org/t/spliting-payload-to-insert-into-mysql/24193/2 "2020-04-03T23:16:00Z")

</div>

> [@nksky](#):
>
> This is my payload ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/1/f1457a95fa9e885f981b5450c97b54b4642acfb5.png)

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

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

---

<div class="post-metadata">

**Author:** ![nksky](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/nksky/32/9173_2.png) [@nksky](https://discourse.nodered.org/u/nksky)\
**Post date:** [3 April 2020 23:20 UTC](https://discourse.nodered.org/t/spliting-payload-to-insert-into-mysql/24193/3 "2020-04-03T23:20:10Z")

</div>

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.

---

<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:** [3 April 2020 23:22 UTC](https://discourse.nodered.org/t/spliting-payload-to-insert-into-mysql/24193/4 "2020-04-03T23:22:15Z")

</div>

One tip,

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

---

<div class="post-metadata">

**Author:** ![nksky](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/nksky/32/9173_2.png) [@nksky](https://discourse.nodered.org/u/nksky)\
**Post date:** [3 April 2020 23:38 UTC](https://discourse.nodered.org/t/spliting-payload-to-insert-into-mysql/24193/5 "2020-04-03T23:38:08Z")

</div>

> [@Steve-Mcl](#):
>
> JSON validators

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/0/405ecc6f4fc61ae8c34737e1e27074673b7e48cb.png)

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

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

```

---

<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:** [3 April 2020 23:46 UTC](https://discourse.nodered.org/t/spliting-payload-to-insert-into-mysql/24193/6 "2020-04-03T23:46:24Z")

</div>

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](https://nodered.org/docs/user-guide/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.

---

<div class="post-metadata">

**Author:** ![nksky](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/nksky/32/9173_2.png) [@nksky](https://discourse.nodered.org/u/nksky)\
**Post date:** [4 April 2020 00:23 UTC](https://discourse.nodered.org/t/spliting-payload-to-insert-into-mysql/24193/7 "2020-04-04T00:23:39Z")

</div>

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

---

<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:** [18 April 2020 00:23 UTC](https://discourse.nodered.org/t/spliting-payload-to-insert-into-mysql/24193/8 "2020-04-18T00:23:41Z")

</div>

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