[SOLVED]The dreaded "query not defined as a string" mysql

General
1

I am fighting to insert into MySQL. I tried format node without success, and have worked my way to a function node.

Just for perspective, I have several hours of Node-Red experience and followed MySQL example to chart data

var data2 = Math.round(Math.random()*100);
var newMsg =  {payload: "INSERT INTO 'TestData' VALUES ( " + data1 + ", " + data2 + " , " +msg.payload + ")"};
return newMsg;```

Gets me 
'''msg.topic : the query is not defined as a string'''
2

Hi @lamachine

In your code you are setting msg.payload as the query to run. The node's help, and the error message, show it expects the query to be under msg.topic.

So where you do: var newMsg = {payload: .... replace payload with topic.

1 Like
3

knolleary,

I thought that was correct, but I kept getting other errors. To my inexperienced eye, I was closer with payload. Here is a more complete picture

When I use {topic:

10/30/2018, 4:52:21 PMnode: f498377.f49c648
msg.payload : number
1540943541605
10/30/2018, 4:52:21 PMnode: e83b091f.982f3INSERT INTO 'TestData' VALUES ( 51, 55 , 1540943541605) : msg.payload : undefined
undefined
10/30/2018, 4:52:22 PMnode: MYSQLmsg : error
"Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''TestData' VALUES ( 51, 55 , 1540943541605)' at line 1"

and when I use payload

 10/30/2018, 4:59:15 PM[node: f498377.f49c648](http://localhost:1880/#)

msg.payload : number

1540943955167

10/30/2018, 4:59:15 PM[node: e83b091f.982f3](http://localhost:1880/#)msg.payload : string[55]

"INSERT INTO 'TestData' VALUES ( 46, 43 , 1540943955167)"

10/30/2018, 4:59:15 PM[node: MYSQL](http://localhost:1880/#)msg : string[48]

"msg.topic : the query is not defined as a string"
4

As Nick told you - the sql query has to be in msg.topic. If that causes other issues, what are they?

1 Like
5

when you used msg.topic, the error said you had a problem with the sql syntax.

When you used msg.payload the error said msg.topic: the query is not defined - in other words, it can't find the query you want to run.

So the problem is something in the specific syntax of the query you are passing in (with msg.topic).
I suggest you use a debug node to view the exact msg.topic you are passing to the mysql node. Then carefully check its syntax.

Final note - well done for use three backticks to format your code blocks. But, they must be on a line of their own. I've edited your previous post to fix up the markup.

2 Likes
6

Thanks you both! This boils down to "Don't Panic" if the error message gets bigger and more red, and RTF(debug message). If there is a CLOSED or SOLVED flag, please add it to this threak.

This one can be marked as closed. FYI, the unix vs SQL time was not doing me any favors, so I just dropped it and let the database insert a timestamp().

Here is the code that worked.

var data1 = Math.round(Math.random()*100);
var data2 = Math.round(Math.random()*100);
var newMsg = {topic:  "INSERT INTO `TestData`(`data1`, `data2`) VALUES ("+ data1 +","+ data2 +")"};
return newMsg;