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!
Welcome to the forums @jblack
Without knowing the Node you are using...
I would suggest using parameters
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')
1 Like
The current node that I’m using is an Inject node that triggers at an interval.
Thank you!!
If you wanted to do this from an inject
node.
something like below should work.
[{"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":[[]]}]
template literals
In JavaScript, backticks (`) are used to create template literals.
e.g.
var device=msg.payload
var newMsg={}
newMsg.query=`SELECT * FROM test WHERE device='${device}' ORDER BY DESC LIMIT 1`
return newMsg;
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
msg.params = {
'$data': flow.get('StoredTemp') || 'no data'
}
return msg
If you need other fields just add them to the named parameters object:
msg.params = {
'$data': flow.get('StoredTemp') || 'no data',
'$location': 'Manhattan'
}
return msg
1 Like
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.
1 Like
JBudd the prepared statement method worked just fine. Thanks!
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.
1 Like
Hey @marcus-j-davies
Your post is really helpful.
1 Like
Helpful, but has a slight typo
don't use ? just name the params
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;
1 Like
Okay! Thanks for the suggestion
@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:
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
2 Likes
system
Closed
12 2024 13:10
15
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.