SELECT from SQL into variable

General
1

Hello.
I need again some help with SQL and NR. I've stored some temp datas into a SQL db. Now I want to "ask" the db for the latest temp.
The db is like this
column "1" = AUTO_INCREMENT
column "temperature_C" = the temp. My function loooks like:

var query;
query ="SELECT Wettersensoren.temperature_C FROM Wettersensoren ORDER BY Wettersensoren.1 DESC LIMIT 1";

flow.set("flowtmp",query);
msg.payload = query;
msg.topic = "Temperatur";
return msg;

The debug output is

"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 'Temperatur' at line 1"

Could anybody give me a hint how to do it correctly?!?

Many thanks and merry christmas :slight_smile:

2

Which sql node are you using (node-red-contrib-something possibly). Some of them expect the query in the topic not the payload.

3

I'm using node-red-node-mysql

my flow looks like this:

inject -> function -> node-red-node-mysql -> debug

I'm pretty sure the SQL works. Because I'm feeding the database with tempreture datas.

My "aim" is to store the latest temp. in the flow.set "FLOWTMP" and send this together with some text by "telegram" to my cell-phone.

4

As Colin mentioned earlier in this thread, you need the query to be in msg.topic.

5

Where does the info tab say the query should be placed and what have you got in there? Feed the output of your function node into a debug node set to Show Complete Message and see whether what you see matches what the node expects.

6 
Temperatur : msg.payload : string[94]

"SELECT Wettersensoren.temperature_C FROM Wettersensoren ORDER BY Wettersensoren.1 DESC LIMIT 1"

this is the debug output from the function

It seems to that the SQL SELECT is not processed.. it just output the text

7

This shows you have putt the query into msg.payload.

As the sidebar help for the node explains, and others have pointed out here, it needs to be msg.topic.