# Msg split, choose option and MySQL select

**URL:** <https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831>\
**Category:** General\
**Created:** [17 February 2020 07:44 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831 "2020-02-17T07:44:43Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![gbuurman](https://avatars.discourse-cdn.com/v4/letter/g/22d042/32.png) [@gbuurman](https://discourse.nodered.org/u/gbuurman)\
**Post date:** [17 February 2020 07:44 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/1 "2020-02-17T07:44:43Z")

</div>

# Need extra help for the following

## MSG Split

I want to split a msg based on the 0x20 character. The msg is a standard text string coming from a soapserver

## Choose

Next to that, I need to choose a route based on the first item in the previous split

### MySQL

Finally, based on the content from the choose option performing a select, upgrade, insert or drop

Many thanks, upfront  
Ger

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [17 February 2020 08:20 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/2 "2020-02-17T08:20:58Z")

</div>

Feed the message into a debug node and show us exactly what you have.

---

<div class="post-metadata">

**Author:** ![gbuurman](https://avatars.discourse-cdn.com/v4/letter/g/22d042/32.png) [@gbuurman](https://discourse.nodered.org/u/gbuurman)\
**Post date:** [17 February 2020 10:39 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/3 "2020-02-17T10:39:04Z")

</div>

In the meanwhile I have made some interesting steps but am now confronted with the next error

"ReferenceError: payload is not defined (line 2, col 21)"

Before I want to execute the sql cmd I need to extract the "  
The string entered the remove characters looks like:

array[3]

0: "SINGLEINSERT"

1: "INSERT"

2: "INTO TABLE BIG\_DATA\_COLLECTION (\_ID, \_DIGID) VALUES (11111,554544454554)"

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [17 February 2020 12:03 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/4 "2020-02-17T12:03:53Z")

</div>

> [@gbuurman](#):
>
> "ReferenceError: payload is not defined (line 2, col 21)"

Is that coming from a function node? If so then it is difficult to diagnose the problem without seeing what you have put in the node. However, using my telepathic I can see that you have tried to use `payload` when you probably meant `msg.payload`.

> [@gbuurman](#):
>
> I need to extract the ...

I don't see the special character in the strings you have posted. Perhaps a screenshot would be better if the forum won't display it.

---

<div class="post-metadata">

**Author:** ![gbuurman](https://avatars.discourse-cdn.com/v4/letter/g/22d042/32.png) [@gbuurman](https://discourse.nodered.org/u/gbuurman)\
**Post date:** [17 February 2020 13:32 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/5 "2020-02-17T13:32:37Z")

</div>

Colin, the special characters are:

[  
]  
"

MySQL don't like these at the beginning of the cmd string and at the end.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [17 February 2020 13:36 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/6 "2020-02-17T13:36:10Z")

</div>

But those don't appear in the strings. Are you passing it the whole array or something. What exact query, for the example data you posted, are you trying to get?

---

<div class="post-metadata">

**Author:** ![gbuurman](https://avatars.discourse-cdn.com/v4/letter/g/22d042/32.png) [@gbuurman](https://discourse.nodered.org/u/gbuurman)\
**Post date:** [17 February 2020 13:55 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/7 "2020-02-17T13:55:35Z")

</div>

Youre right, what I offered the node-red package (sql connection) is the following string

["tag","cmd item","rest of command string"]

the tag is purely informational  
the cmd is for choosing the right way  
the rest of the cmdstring is for concatenation

so if I send ["MultiSelct","Select"," field1, field2, field3 from xyzzy where field1 = field2"]  
then the mysql connection result must be

Select field1, field2, field3 from xyzzy where field1 = field2 and so on

make this info it a little more clear?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [17 February 2020 14:02 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/8 "2020-02-17T14:02:47Z")

</div>

Do you mean you just want to concatenate the second element of the array with the third? If so then in a function node, assuming the result is to be in msg.payload, I don't know if that is right for the node you are using, then

```auto
msg.payload = msg.payload[1] + msg.payload[2]
return msg

```

---

<div class="post-metadata">

**Author:** ![gbuurman](https://avatars.discourse-cdn.com/v4/letter/g/22d042/32.png) [@gbuurman](https://discourse.nodered.org/u/gbuurman)\
**Post date:** [17 February 2020 14:27 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/9 "2020-02-17T14:27:08Z")

</div>

almost there Colin, I have still the " character I would like to remove

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [17 February 2020 14:36 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/10 "2020-02-17T14:36:05Z")

</div>

Do you mean that in a debug node you see that it says it is a string and shows the text `"Select ..."`? If so then the quotes are not in the string, they are just there to mark the start and end of the string.

If that isn't what you mean then show us what you are getting in a debug node, screenshot if possible please.

---

<div class="post-metadata">

**Author:** ![gbuurman](https://avatars.discourse-cdn.com/v4/letter/g/22d042/32.png) [@gbuurman](https://discourse.nodered.org/u/gbuurman)\
**Post date:** [17 February 2020 14:43 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/11 "2020-02-17T14:43:00Z")

</div>

17-2-2020 15:41:13[node: a94a5ff5.472a7](http://localhost:1880/#)msg.payload : string[100]

"["SingleInsert","Insert","into table big\_data\_collection (\_id, \_digid) values (11111,554544454554)"]"

17-2-2020 15:41:13[node: reformat](http://localhost:1880/#)function : (error)

"Function tried to send a message of type string"

---

<div class="post-metadata">

**Author:** ![gbuurman](https://avatars.discourse-cdn.com/v4/letter/g/22d042/32.png) [@gbuurman](https://discourse.nodered.org/u/gbuurman)\
**Post date:** [17 February 2020 14:45 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/12 "2020-02-17T14:45:38Z")

</div>

I changed a typo

17-2-2020 15:44:18[node: a94a5ff5.472a7](http://localhost:1880/#)msg.payload : string[100]

"["SingleInsert","Insert","into table big\_data\_collection (\_id, \_digid) values (11111,554544454554)"]"

17-2-2020 15:44:18[node: 826ae6d9.c1bfc](http://localhost:1880/#)INSERT INTO measureF (time, temp, hum) VALUES (CURRENT\_TIMESTAMP, , ) : msg.payload : string[79]

"INSERT INTO TABLE BIG\_DATA\_COLLECTION (\_ID, \_DIGID) VALUES (11111,554544454554)"

But I am still offering the " character to MySQL, she don't like it/

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [17 February 2020 14:53 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/13 "2020-02-17T14:53:53Z")

</div>

Show us the error you are getting from the sql node.

---

<div class="post-metadata">

**Author:** ![gbuurman](https://avatars.discourse-cdn.com/v4/letter/g/22d042/32.png) [@gbuurman](https://discourse.nodered.org/u/gbuurman)\
**Post date:** [17 February 2020 15:19 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/14 "2020-02-17T15:19:33Z")

</div>

Hi Colin, my last two replies are held bij the system. The error returned by mysql is:

ER\_PARSE\_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', )' at line 2

regards

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [17 February 2020 15:26 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/15 "2020-02-17T15:26:37Z")

</div>

That says it doesn't like `,)` in the query, so presumably it doesn't like `VALUES (CURRENT_TIMESTAMP, , )`. I don't use MSSQL but I guess you must provide values for all three columns.

---

<div class="post-metadata">

**Author:** ![gbuurman](https://avatars.discourse-cdn.com/v4/letter/g/22d042/32.png) [@gbuurman](https://discourse.nodered.org/u/gbuurman)\
**Post date:** [17 February 2020 15:35 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/16 "2020-02-17T15:35:34Z")

</div>

I don't use timestamp. Here is the cmd:  
"INSERT INTO TABLE PISS (\_ID, \_DIGID) VALUES (11111,554544454554)"

ER\_PARSE\_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', )' at line 2

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [17 February 2020 15:49 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/17 "2020-02-17T15:49:35Z")

</div>

I don't believe that error is coming from that query. Configure a debug node on the output of the sql node to Show Complete Message and also one on the input. Give them names so they are identifiable in the debug output. Run it and screenshot the result please. If you are using node-red-contrib-mssql-plus it should have the query in msg.query on the output.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [17 February 2020 18:13 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/18 "2020-02-17T18:13:37Z")

</div>

> [@gbuurman](#):
>
> 17-2-2020 15:44:18[node: 826ae6d9.c1bfc](http://localhost:1880/#)INSERT INTO measureF (time, temp, hum) VALUES (CURRENT\_TIMESTAMP, , ) : msg.payload : string[79]

This is the query that is giving the error. Looks like you need to provide values for 'temp' and 'hum'

---

<div class="post-metadata">

**Author:** ![gbuurman](https://avatars.discourse-cdn.com/v4/letter/g/22d042/32.png) [@gbuurman](https://discourse.nodered.org/u/gbuurman)\
**Post date:** [18 February 2020 08:30 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/19 "2020-02-18T08:30:23Z")

</div>

I tried several options and msg, but no success. (YET)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [18 February 2020 09:33 UTC](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831/20 "2020-02-18T09:33:30Z")

</div>

Like what?

[Next page](https://discourse.nodered.org/t/msg-split-choose-option-and-mysql-select/21831.md?page=2)