# Getting Flow variables into SQLite INSERT
**URL:** https://discourse.nodered.org/t/getting-flow-variables-into-sqlite-insert/88330
**Category:** General
**Tags:** sqlite
**Created:** [28 May 2024 20:17 UTC](https://discourse.nodered.org/t/getting-flow-variables-into-sqlite-insert/88330 "2024-05-28T20:17:54Z")
**Posts on this page:** 15
**Page:** 1
### Author:  [@jblack](https://discourse.nodered.org/u/jblack)
#### Post date: [28 May 2024 20:17 UTC](https://discourse.nodered.org/t/getting-flow-variables-into-sqlite-insert/88330/1 "2024-05-28T20:17:54Z")
I could use a bit of assistance...
I am attempting to get a flow variable into a SQLite database. Here is my current INSERT command:
INSERT INTO ACC1 (Data, Date, Time) VALUES ("123", strftime('%m-%d-%Y', 'now', 'localtime'), strftime('%H:%M:%S', 'now', 'localtime'));
I would like to replace the "123" data with a Flow variable. I have tried numerous formats but haven't succeeded as of yet.
The INSERT command works as it is and the variable which is Flow.StoredTemp can be read as expected from a debug node.
Any help would be greatly appreciated.
Cheers!
---
### Author:  [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)
#### Post date: [28 May 2024 20:22 UTC](https://discourse.nodered.org/t/getting-flow-variables-into-sqlite-insert/88330/2 "2024-05-28T20:22:52Z")
Welcome to the forums @jblack
Without knowing the Node you are using...
I would suggest using parameters
```auto
msg.topic = `INSERT INTO ACC1 (Data, Date, Time) VALUES (?, strftime('%m-%d-%Y', 'now', 'localtime'), strftime('%H:%M:%S', 'now', 'localtime'))`
msg.payload = [flow.get('StoredTemp')]
return msg;
```
The array collection will be used to replace the '?' instances
You can get a flow variable with `flow.get('StoredTemp')`
> **[node-red-node-sqlite](https://flows.nodered.org/node/node-red-node-sqlite)**
>
> A sqlite node for Node-RED
---
### Author:  [@jblack](https://discourse.nodered.org/u/jblack)
#### Post date: [28 May 2024 20:38 UTC](https://discourse.nodered.org/t/getting-flow-variables-into-sqlite-insert/88330/3 "2024-05-28T20:38:07Z")
The current node that Iām using is an Inject node that triggers at an interval.
Thank you!!
---
### Author:  [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)
#### Post date: [28 May 2024 20:47 UTC](https://discourse.nodered.org/t/getting-flow-variables-into-sqlite-insert/88330/4 "2024-05-28T20:47:56Z")
If you wanted to do this from an `inject` node.
something like below should work.
```auto
[{"id":"cffb4f27ba66a419","type":"inject","z":"2d7bf6e3.84c97a","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"INSERT INTO ACC1 (Data, Date, Time) VALUES (?, strftime('%m-%d-%Y', 'now', 'localtime'), strftime('%H:%M:%S', 'now', 'localtime'))","payload":"[$flowContext('StoredTemp')]","payloadType":"jsonata","x":570,"y":640,"wires":[[]]}]
```
---
### Author:  [@RedTom](https://discourse.nodered.org/u/RedTom)
#### Post date: [28 May 2024 20:53 UTC](https://discourse.nodered.org/t/getting-flow-variables-into-sqlite-insert/88330/5 "2024-05-28T20:53:18Z")
template literals
In JavaScript, backticks (`) are used to create template literals.
e.g.
```auto
var device=msg.payload
var newMsg={}
newMsg.query=`SELECT * FROM test WHERE device='${device}' ORDER BY DESC LIMIT 1`
return newMsg;
```
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [28 May 2024 21:14 UTC](https://discourse.nodered.org/t/getting-flow-variables-into-sqlite-insert/88330/6 "2024-05-28T21:14:02Z")
You can use the prepared statement feature of the sqlite node:
It will expect to find the value of $data in msg.params.
I use a function node to populate msg.params, like this
```auto
msg.params = {
'$data': flow.get('StoredTemp') || 'no data'
}
return msg
```
If you need other fields just add them to the named parameters object:
```auto
msg.params = {
'$data': flow.get('StoredTemp') || 'no data',
'$location': 'Manhattan'
}
return msg
```
---
### Author:  [@jblack](https://discourse.nodered.org/u/jblack)
#### Post date: [28 May 2024 21:34 UTC](https://discourse.nodered.org/t/getting-flow-variables-into-sqlite-insert/88330/7 "2024-05-28T21:34:06Z")
Thanks Marcus! The inject node works without error, but when I retrieve the data from the database the "data" field "Null". The Date and Time fields do work as anticipated.
---
### Author:  [@jblack](https://discourse.nodered.org/u/jblack)
#### Post date: [28 May 2024 21:56 UTC](https://discourse.nodered.org/t/getting-flow-variables-into-sqlite-insert/88330/8 "2024-05-28T21:56:06Z")
JBudd the prepared statement method worked just fine. Thanks!
---
### Author:  [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)
#### Post date: [29 May 2024 06:58 UTC](https://discourse.nodered.org/t/getting-flow-variables-into-sqlite-insert/88330/9 "2024-05-29T06:58:53Z")
Ah!
Sorry **?** was incorrect, you just needed to use the name instead **$data**
But the solution by @jbudd allows for easier maintenance and probably cleaner.
---
### Author:  [@elbertelena](https://discourse.nodered.org/u/elbertelena)
#### Post date: [29 May 2024 11:00 UTC](https://discourse.nodered.org/t/getting-flow-variables-into-sqlite-insert/88330/10 "2024-05-29T11:00:45Z")
Hey @marcus-j-davies
Your post is really helpful.
---
### Author:  [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)
#### Post date: [29 May 2024 11:04 UTC](https://discourse.nodered.org/t/getting-flow-variables-into-sqlite-insert/88330/11 "2024-05-29T11:04:00Z")
Helpful, but has a slight typo š
don't use ? just name the params
```auto
msg.topic = `INSERT INTO ACC1 (Data, Date, Time) VALUES ($Data, strftime('%m-%d-%Y', 'now', 'localtime'), strftime('%H:%M:%S', 'now', 'localtime'))`
msg.payload = [flow.get('StoredTemp')]
return msg;
```
---
### Author:  [@elbertelena](https://discourse.nodered.org/u/elbertelena)
#### Post date: [29 May 2024 11:09 UTC](https://discourse.nodered.org/t/getting-flow-variables-into-sqlite-insert/88330/12 "2024-05-29T11:09:43Z")
Okay! Thanks for the suggestion š„°
---
### Author:  [@jblack](https://discourse.nodered.org/u/jblack)
#### Post date: [29 May 2024 12:35 UTC](https://discourse.nodered.org/t/getting-flow-variables-into-sqlite-insert/88330/13 "2024-05-29T12:35:00Z")
Thanks Marcus!!!
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [29 May 2024 13:10 UTC](https://discourse.nodered.org/t/getting-flow-variables-into-sqlite-insert/88330/14 "2024-05-29T13:10:13Z")
@marcus-j-davies' function node does work.
It inserts a record with "data" set to the context variable (if it exists)
But there is no connection between $Data in the INSERT statement and the value in msg.payload other than the position of $data in the field list and the position of the value in the payload array.
Consider if the record is enlarged:
```auto
msg.topic = `INSERT INTO ACC1 (Location, Data, Date, Time) VALUES ($LOCATION, $Data, strftime('%m-%d-%Y', 'now', 'localtime'), strftime('%H:%M:%S', 'now', 'localtime'))`
msg.payload = [flow.get('StoredTemp'), 'Manhattan']
return msg;
```
I think it is better to use a method which gives a name in common between the two msg properties.
And I believe it's best practice security wise to always use a prepared statement, regardless of the SQL database
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [12 June 2024 13:10 UTC](https://discourse.nodered.org/t/getting-flow-variables-into-sqlite-insert/88330/15 "2024-06-12T13:10:42Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.