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

**URL:** <https://discourse.nodered.org/t/solved-the-dreaded-query-not-defined-as-a-string-mysql/4432>\
**Category:** General\
**Created:** [30 October 2018 21:45 UTC](https://discourse.nodered.org/t/solved-the-dreaded-query-not-defined-as-a-string-mysql/4432 "2018-10-30T21:45:49Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![lamachine](https://avatars.discourse-cdn.com/v4/letter/l/ba8739/32.png) [@lamachine](https://discourse.nodered.org/u/lamachine)\
**Post date:** [30 October 2018 21:45 UTC](https://discourse.nodered.org/t/solved-the-dreaded-query-not-defined-as-a-string-mysql/4432/1 "2018-10-30T21:45:49Z")

</div>

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](https://flows.nodered.org/flow/13c55d1aa11e864609e24fa534a1fa26)

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

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [30 October 2018 22:06 UTC](https://discourse.nodered.org/t/solved-the-dreaded-query-not-defined-as-a-string-mysql/4432/2 "2018-10-30T22:06:19Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![lamachine](https://avatars.discourse-cdn.com/v4/letter/l/ba8739/32.png) [@lamachine](https://discourse.nodered.org/u/lamachine)\
**Post date:** [31 October 2018 00:00 UTC](https://discourse.nodered.org/t/solved-the-dreaded-query-not-defined-as-a-string-mysql/4432/3 "2018-10-31T00:00:47Z")

</div>

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:

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

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

```

---

<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:** [31 October 2018 00:20 UTC](https://discourse.nodered.org/t/solved-the-dreaded-query-not-defined-as-a-string-mysql/4432/4 "2018-10-31T00:20:27Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [31 October 2018 01:08 UTC](https://discourse.nodered.org/t/solved-the-dreaded-query-not-defined-as-a-string-mysql/4432/5 "2018-10-31T01:08:07Z")

</div>

> [@lamachine](#):
>
> To my inexperienced eye, I was closer with payload. Here is a more complete picture

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.

---

<div class="post-metadata">

**Author:** ![lamachine](https://avatars.discourse-cdn.com/v4/letter/l/ba8739/32.png) [@lamachine](https://discourse.nodered.org/u/lamachine)\
**Post date:** [31 October 2018 16:23 UTC](https://discourse.nodered.org/t/solved-the-dreaded-query-not-defined-as-a-string-mysql/4432/6 "2018-10-31T16:23:54Z")

</div>

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.

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

```