Getting Flow variables into SQLite INSERT

[jblack]

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!

[marcus-j-davies]

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')

node-red-node-sqlite

[jblack]

The current node that I’m using is an Inject node that triggers at an interval.

Thank you!!

[marcus-j-davies]

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":[[]]}]

[RedTom]

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;

[jbudd]

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

[jblack]

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.

[jblack]

JBudd the prepared statement method worked just fine. Thanks!

[marcus-j-davies]

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.

[elbertelena]

Hey @marcus-j-davies
Your post is really helpful.

[marcus-j-davies]

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;

[elbertelena]

Okay! Thanks for the suggestion

[jblack]

Thanks Marcus!!!

[jbudd]

@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