Msg split, choose option and MySQL select

General
1

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

2

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

3

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)"

4

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.

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.

5

Colin, the special characters are:

[
]
"

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

6

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?

7

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?

8

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

msg.payload = msg.payload[1] + msg.payload[2]
return msg
9

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

10

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.

11

17-2-2020 15:41:13node: a94a5ff5.472a7msg.payload : string[100]

"["SingleInsert","Insert","into table big_data_collection (_id, _digid) values (11111,554544454554)"]"

17-2-2020 15:41:13node: reformatfunction : (error)

"Function tried to send a message of type string"

12

I changed a typo

17-2-2020 15:44:18node: a94a5ff5.472a7msg.payload : string[100]

"["SingleInsert","Insert","into table big_data_collection (_id, _digid) values (11111,554544454554)"]"

17-2-2020 15:44:18node: 826ae6d9.c1bfcINSERT 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/

13

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

14

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

15

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.

16

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

17

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.

18

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

19

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

20

Like what?