SQlite SELECT variable substitution

General
1

Hi all,
On a Raspberry Pi I am attempting to use a FUNCTION NODE to pass a msg.topic containing an SQlite SELECT query.

msg.topic = 'SELECT * FROM dhtreadings WHERE TankID = "BlackLaundry" AND TankTime > ${BlackLaundryLevel}';

I receive the error message
Error: SQLITE_ERROR: unrecognized token: "$"

With no formatting on the variable BlackLaundryLevel

msg.topic = 'SELECT * FROM dhtreadings WHERE TankID = "BlackLaundry" AND TankTime > BlackLaundryLevel';
I get the error:
Error: SQLITE_ERROR: no such column: BlackLaundryLevel

Can anyone please inform me of the correct syntax to pass a variable to a SELECT statement in a message topic?

Blockquote

2

Any reason you're using topic and not payload to store your query?

personally, I like to use the template node, this makes it extremely easy to construct a query.

  1. Bring in a function node and set a property on the payload object.
msg.payload.BlackLaundryLevel = 100
return msg
  1. bring in a template node and set it's contents as follows (connect the function node to it)
SELECT * FROM dhtreadings WHERE TankID = "BlackLaundry" AND TankTime > {{payload.BlackLaundryLevel}}
  1. connect the template node to your sql client node.
3

Does node-red know the value of BlackLaundryLevel?

msg.topic = "SELECT * FROM dhtreadings WHERE TankID = 'BlackLaundry' AND TankTime > " + msg.payload.BlackLaundryLevel + ":"

4

the ' should be `. That is backticks rather than single quotes


msg.topic = `SELECT * FROM dhtreadings WHERE TankID = 'BlackLaundry' AND TankTime > ${BlackLaundryLevel}`;
2 Likes
5

Thanks, this is the answer I am looking for. That and new glasses

6

Yes, the variable was assigned earlier in the node.
Thanks for your reply. E1cid has answered the question

7

Marcus,
I copied an example where they used the topic.
Thanks for your reply. E1cid has answered the question

8

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.