Question, Working on an API which returns results like 25 in one call, and i want to fetch all records I want to know how should my approach be, I am planning to store the data returned by the api in database and also load the database in datable or grid, 1. Should i call api and load in grid and save it, 2. when next is clicked in grid, call the api again and fetch the data and display it again and save it but above approach, i am not think is right, because i am also saving it, so i will serve from db instead of api if i am saving but how should i get the data like should i do a schedule task which runs every hour and updates the data in backend until entire is saved in db

Personally, I would get the data from the API, save it to the database, and then use the database to populate the data grid. I would use a scheduled task to iterate over all the data that I need from the api every hour or whatever makes sense.

No matter what, I would not be doing your data calls and saving in the client. I would be doing that from the server. It is so much easier to secure, and guarantee what occurs. How often does the data update? I would then setup a scheduled task to do it on that basis. How much data are you looking at? Is there a problem with having partial data? If that was the case I would load the api data into a temp table and then swap that table with the data table on success of everything. So what ever uses that data always has a full set of data and not a partial load or an errant load.

data gets every hour, so i need to be have updated data so serve it better

So i would have a scheduled task running every hour to get your data in chunks, I would save it to a new table (not the one you reference) after getting all the chunks then I would swap the temp table into the one you reference. Make sure you throw a lock around this process since you don't want it being called more then one time at a time.. MySQL has a rename command:

RENAME TABLE foo TO foo_old, foo_new To foo;

https://dba.stackexchange.com/questions/22108/how-do-i-swap-tables-in-mysql This reference suggests to use a view and then change what the view references which is quite clever. https://stackoverflow.com/questions/605774/swapping-ms-sql-tables

Query the last record ID, and store it someplace, so on the next query, start at previousID+1, grab the next 25 records, and repeat. If your API call supports telling you how many records, could write a loop based on how many times the fetch needs to run.

Thanks all, so here is http response and i am trying this now <cfset getAllCompletionDetails = new cfc.oraConnection.fullDetails({convertQuery = 0,limit=1000,offset="0"})> <cfset lastRecord = getAllCompletionDetails.recordcount + 1> <cfif getAllCompletionDetails.data.hasmore> <cfset getAllCompletionDetails = new cfc.oraConnection.fullDetails({convertQuery = 0,limit=#lastRecord#,offset="1000"})> </cfif> so as you @Evil Ware how can i use the offset tto fetch next batch and until it reaches the total records, it will stop and save the data in table, so in the case of saving, i will save in temp and then after the complete schedule is finihsed i will transfer data from temp to main table, is that how?

Without knowing the API you are using you are going to have to go read the API docs. The big question is does the API support start at a record of your choosing? If it does then the above code would work. If it doesn't then you will have to write a bit more logic depending on how the API works with the retrieval of the records, and sequentially start and keep your "call" alive until complete.