I am storing the sensor data in sqlite databse. In one hour around 7200 rows of data is stored in the database. So after an interval of one hour, I want to fetch 7000 rows and send to AWS using MQTT. This process should run in loop.
Below is the flow.
Use an Inject node configured to inject every hour to do the transfer.
If that isn't the bit of code you are having trouble with then tell us what it is that you don't know how to do.
I am using the inject node to trigger the query which is inside the function node:
let offset = context.get('offset') || 0; // initialize offset from context, defaulting to 0
const limit = 20; // set the limit to 20 rows per query
const query = SELECT geodata, device_id, timestamp, temperature, humidity, windspeed FROM winddata LIMIT ${limit} OFFSET ${offset};
offset += limit; // increment offset for the next query
context.set('offset', offset); // store offset in context for next iteration
msg.topic = query;
return msg;
Now I want to makes changes such that for the first time when the inject node is triggered it should return all the data in the database and after one hour when the inject node is triggered it should returns the newly added rows and not the intial ones which were fetched previously.
you could save in Context lastTimestamp variable which will hold the timestamp from the last data row
(im judging from the names of your db's column names that that's the column that is unique / primary key ?)
and in your sql query append .. WHERE timestamp > ${lastTimestamp}
maybe this way you wont even need an offset
There is one more column "sr_no" which is primary key and it is auto-incremeted for each row of data.Timestamp is not unique because there are 2 sensors which are sending the data and both get a common time stamp.
I also tried this code:
let lastTimestamp = context.get('lastTimestamp') || 0; // initialize lastTimestamp from context, defaulting to 0
const query = SELECT geodata, device_id, timestamp, temperature, humidity, windspeed FROM winddata WHERE timestamp > '${lastTimestamp}';
msg.topic = query;
context.set('lastTimestamp', lastTimestamp);
return msg;
But it did not work, it queries all the data every time instead of querying the newly added data
yes using sr_no seems like a better option
where are you setting the Context with context.set('lastTimestamp', lastTimestamp);
this code should be (based on your last screenshot) in function 12 right after the first request
and it should be something like
let lastSr_no = msg.payload[msg.payload.length - 1].sr_no
flow.set('lastSr_no', lastSr_no);
and the function preparing the sql query
let lastSr_no = flow.get('lastSr_no') || 0;
const query = `SELECT sr_no, geodata, device_id, timestamp, temperature, humidity, windspeed FROM winddata WHERE sr_no > ${lastSr_no} LIMIT 20`;
ps. memory context is reset when you restart node-red so you may need to set it to local file system
ps2. you need to use flow Context when you need to access it from different functions because context.set/get is scoped only to that particular function
No, The above code which I mentioned is in function node" Query for 20rows with dyanmic offset"
funtion 12 is just converting teh fetched data in json structured
This worked!! I did not understand about the local file system
1 Like
if you read the documentation on Context it explains everything in detail.
by default Context is stored in memory .. so if you restart node-red the variable lastSr_no is lost and based on your code it will not be accessible and it will default to 0 .. losing track of what was the last row.
if its important for your project .. you can set in your node-red's settings.js file to save Context variables in a file instead of memory so if you restart NR that var will be available again.
more info in the docs
Oh okay! Understood. Thankyou so much.
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
