Using msg.payload in a SQL query

General
1

This might be a simple one...

When I select a value from a dropdownlist the msg.payload is e.g. 1E6
Now I would like to use that msg.payload to make a query (mysql node)

However I cannot find a working function for this.

Currently I am at:

msg.topic = "SELECT *  FROM `NodeRed`.`7bchannels` WHERE `channel`= {{{msg.payload}}} "
return msg;

It has to be in the Syntax, but what am I doing wrong?

2

This should work.

t = "SELECT *  FROM NodeRed.7bchannels WHERE channel="+msg.payload
return {topic:t}

Probably need to format payload as a string:

t = "SELECT *  FROM NodeRed.7bchannels WHERE channel='"+msg.payload+"'"
return {topic:t}
2 Likes
3

We have a winner, simple, it did the job.

Would you mind explaining me why my trial didn`t work? What does the {topic:t} that made it work.

It is probably basic, but that is where I am in this steep learning curve :woozy_face:

4

We need to see the full code to determine why it didn't work. I don't know if you can use the mustache format in a function like that - {{{x}}}.

5

You can’t, but you can use ES6 template strings instead. The following would have worked too:

msg.topic = `SELECT * FROM \`NodeRed\`.\`7bchannels\` WHERE channel= ${msg.payload}`;
return msg;

Since template strings use backticks as surrounding quotes, you need to escape the backticks inside the string. To reference variables inside, you use the ${var} syntax.
If those backticks inside the query are optional which I don’t know/want to know (I can no longer do SQL, PTSD; it’s a long story), you could also leave them out as in @bakman2’s answer.

1 Like
6

Have you thought of using a template node and putting the MySQL query in there?
Just remember to set the property to 'msg.topic'.

ScreenShot030

ScreenShot028
You might have to alter {{payload}} to {{channel}} and put the channel number in msg.channel before the message enters the template node.

Here's an 'INSERT' example from one of my flows.

ScreenShot029
ScreenShot029.png1019Ă—304 9.31 KB

1 Like
7

Thank you @dynamicdave I will give it a try as well.

The issue is that the whole plan is in my head but as I am trying to understand javascript and the meanings of all these msg.options, msg.paylad,... it is a matter of keeping things as simple as possible.

Still trying to find the best way to learn all these things, specially writing functions. Like the "learn javascript for node-red" manual would be good. For now I learn most via this forum.

Thanks everyone for helping the beginners...

1 Like
8

For those using mariaDB I've create a 'select' function that works with msg.payload.
```var t = "SELECT DateAndTime, Temperature, Humidity, BarrPress FROM temp-at-interrupt where minute(Time)= '00' order by DateAndTime DESC LIMIT " + msg.payload

return {topic:t} ```

I'm injecting a numberic payload of 22 (record count)
Eventually, I'll add a slide node to make my select statement dynamic.
Thank you "dynamicdave" for you great post.
Also, in general, this site is my favorite help site for everything "node-red" and R-Pi.

1 Like