# I want to fetch data from sqlite in batches after certain interval of time

**URL:** <https://discourse.nodered.org/t/i-want-to-fetch-data-from-sqlite-in-batches-after-certain-interval-of-time/76135>\
**Category:** General\
**Tags:** database, function-node, mqtt\
**Created:** [5 March 2023 13:29 UTC](https://discourse.nodered.org/t/i-want-to-fetch-data-from-sqlite-in-batches-after-certain-interval-of-time/76135 "2023-03-05T13:29:16Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![YashPisat](https://avatars.discourse-cdn.com/v4/letter/y/e9a140/32.png) [@YashPisat](https://discourse.nodered.org/u/YashPisat)\
**Post date:** [5 March 2023 13:29 UTC](https://discourse.nodered.org/t/i-want-to-fetch-data-from-sqlite-in-batches-after-certain-interval-of-time/76135/1 "2023-03-05T13:29:16Z")

</div>

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.

 ![flow](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/5/2565f21f5cdb3a8ad73285f88f7134d70e3b2617.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [5 March 2023 13:42 UTC](https://discourse.nodered.org/t/i-want-to-fetch-data-from-sqlite-in-batches-after-certain-interval-of-time/76135/2 "2023-03-05T13:42:04Z")

</div>

Use an Inject node configured to inject every hour to do the transfer.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [5 March 2023 14:45 UTC](https://discourse.nodered.org/t/i-want-to-fetch-data-from-sqlite-in-batches-after-certain-interval-of-time/76135/3 "2023-03-05T14:45:24Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![YashPisat](https://avatars.discourse-cdn.com/v4/letter/y/e9a140/32.png) [@YashPisat](https://discourse.nodered.org/u/YashPisat)\
**Post date:** [6 March 2023 14:28 UTC](https://discourse.nodered.org/t/i-want-to-fetch-data-from-sqlite-in-batches-after-certain-interval-of-time/76135/4 "2023-03-06T14:28:35Z")

</div>

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.

 ![2023-03-06_15h26_41](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/d/2de1658e62b5b06766df5d2b32749ae91cecf02e.png)

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [6 March 2023 14:46 UTC](https://discourse.nodered.org/t/i-want-to-fetch-data-from-sqlite-in-batches-after-certain-interval-of-time/76135/5 "2023-03-06T14:46:03Z")

</div>

> [@YashPisat](#):
>
> 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`

---

<div class="post-metadata">

**Author:** ![YashPisat](https://avatars.discourse-cdn.com/v4/letter/y/e9a140/32.png) [@YashPisat](https://discourse.nodered.org/u/YashPisat)\
**Post date:** [6 March 2023 14:57 UTC](https://discourse.nodered.org/t/i-want-to-fetch-data-from-sqlite-in-batches-after-certain-interval-of-time/76135/6 "2023-03-06T14:57:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [6 March 2023 15:22 UTC](https://discourse.nodered.org/t/i-want-to-fetch-data-from-sqlite-in-batches-after-certain-interval-of-time/76135/7 "2023-03-06T15:22:05Z")

</div>

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

```java
let lastSr_no = msg.payload[msg.payload.length - 1].sr_no
flow.set('lastSr_no', lastSr_no);

```

and the function preparing the sql query

```java
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](https://nodered.org/docs/user-guide/context#saving-context-data-to-the-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

---

<div class="post-metadata">

**Author:** ![YashPisat](https://avatars.discourse-cdn.com/v4/letter/y/e9a140/32.png) [@YashPisat](https://discourse.nodered.org/u/YashPisat)\
**Post date:** [6 March 2023 15:25 UTC](https://discourse.nodered.org/t/i-want-to-fetch-data-from-sqlite-in-batches-after-certain-interval-of-time/76135/8 "2023-03-06T15:25:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![YashPisat](https://avatars.discourse-cdn.com/v4/letter/y/e9a140/32.png) [@YashPisat](https://discourse.nodered.org/u/YashPisat)\
**Post date:** [6 March 2023 16:47 UTC](https://discourse.nodered.org/t/i-want-to-fetch-data-from-sqlite-in-batches-after-certain-interval-of-time/76135/9 "2023-03-06T16:47:44Z")

</div>

This worked!! I did not understand about the local file system

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [6 March 2023 17:05 UTC](https://discourse.nodered.org/t/i-want-to-fetch-data-from-sqlite-in-batches-after-certain-interval-of-time/76135/10 "2023-03-06T17:05:55Z")

</div>

> [@YashPisat](#):
>
> about the local file system

if you read the [documentation on Context](https://nodered.org/docs/user-guide/context#what-is-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

---

<div class="post-metadata">

**Author:** ![YashPisat](https://avatars.discourse-cdn.com/v4/letter/y/e9a140/32.png) [@YashPisat](https://discourse.nodered.org/u/YashPisat)\
**Post date:** [6 March 2023 17:07 UTC](https://discourse.nodered.org/t/i-want-to-fetch-data-from-sqlite-in-batches-after-certain-interval-of-time/76135/11 "2023-03-06T17:07:55Z")

</div>

Oh okay! Understood. Thankyou so much.

---

<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:** [20 March 2023 17:07 UTC](https://discourse.nodered.org/t/i-want-to-fetch-data-from-sqlite-in-batches-after-certain-interval-of-time/76135/12 "2023-03-20T17:07:57Z")

</div>

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