# SQlite SELECT variable substitution

**URL:** <https://discourse.nodered.org/t/sqlite-select-variable-substitution/57954>\
**Category:** General\
**Tags:** database\
**Created:** [7 February 2022 20:56 UTC](https://discourse.nodered.org/t/sqlite-select-variable-substitution/57954 "2022-02-07T20:56:46Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Slowdown](https://avatars.discourse-cdn.com/v4/letter/s/dfb087/32.png) [@Slowdown](https://discourse.nodered.org/u/Slowdown)\
**Post date:** [7 February 2022 20:56 UTC](https://discourse.nodered.org/t/sqlite-select-variable-substitution/57954/1 "2022-02-07T20:56:46Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [7 February 2022 21:04 UTC](https://discourse.nodered.org/t/sqlite-select-variable-substitution/57954/2 "2022-02-07T21:04:00Z")

</div>

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.

```javascript
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)

```sql
SELECT * FROM dhtreadings WHERE TankID = "BlackLaundry" AND TankTime > {{payload.BlackLaundryLevel}}

```

1. connect the template node to your sql client node.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [7 February 2022 21:05 UTC](https://discourse.nodered.org/t/sqlite-select-variable-substitution/57954/3 "2022-02-07T21:05:24Z")

</div>

Does node-red know the value of BlackLaundryLevel?

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

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [7 February 2022 21:07 UTC](https://discourse.nodered.org/t/sqlite-select-variable-substitution/57954/4 "2022-02-07T21:07:16Z")

</div>

> [@Slowdown](#):
>
> msg.topic = 'SELECT \* FROM dhtreadings WHERE TankID = "BlackLaundry" AND TankTime \> ${BlackLaundryLevel}';

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

```auto

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

```

---

<div class="post-metadata">

**Author:** ![Slowdown](https://avatars.discourse-cdn.com/v4/letter/s/dfb087/32.png) [@Slowdown](https://discourse.nodered.org/u/Slowdown)\
**Post date:** [7 February 2022 21:59 UTC](https://discourse.nodered.org/t/sqlite-select-variable-substitution/57954/5 "2022-02-07T21:59:47Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Slowdown](https://avatars.discourse-cdn.com/v4/letter/s/dfb087/32.png) [@Slowdown](https://discourse.nodered.org/u/Slowdown)\
**Post date:** [7 February 2022 22:11 UTC](https://discourse.nodered.org/t/sqlite-select-variable-substitution/57954/6 "2022-02-07T22:11:40Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Slowdown](https://avatars.discourse-cdn.com/v4/letter/s/dfb087/32.png) [@Slowdown](https://discourse.nodered.org/u/Slowdown)\
**Post date:** [7 February 2022 22:12 UTC](https://discourse.nodered.org/t/sqlite-select-variable-substitution/57954/7 "2022-02-07T22:12:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [21 February 2022 22:12 UTC](https://discourse.nodered.org/t/sqlite-select-variable-substitution/57954/8 "2022-02-21T22:12:24Z")

</div>

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