How to properly fetch the next rows in a database

Question

I have tried using RecsSkip as found here however when I set RecsSkip it still grabs the first set of rows. I have tried using FetchNext which works for a little while. When I grab the second set it works then will not grab the third set.

First grab:

dmMain.Query.SQL.Text := 'SELECT * FROM cards;';
dmMain.Query.Open();

Sequential grabs:

dmMain.Query.Disconnect();
dmMain.Query.FetchOptions.RecsSkip := skip * dmMain.Query.FetchOptions.RowsetSize;
dmMain.Query.Open();

and I have tried

//  dmMain.Query.FetchNext();

Not sure I understand clearly: You want to fetch rows from a SQLite database but skip the first rows and limit the number of returned rows? If this is what you are looking for, use LIMIT and OFFSET in your SELECT statement. See http://www.sqlite.org/draft/lang_select.html — fpiette, Feb 12 '21 at 16:51
want to grab the first set of rows, process data. when ready, grab the next set and process. repeat until done. — krg, Feb 12 '21 at 17:12

score 1 · Answer 1 · answered Feb 12 '21 at 18:30

I transform my comment to an answer:

To get a block of rows from SQLite, use the SELECT statement LIMIT clause with the OFFSET.

The LIMIT clause is used to place an upper bound on the number of rows returned by the entire SELECT statement.

Optional OFFSET clause that may follow a LIMIT clause. The first M rows are omitted from the result set returned by the SELECT statement and the next N rows are returned, where M and N are the values that the OFFSET and LIMIT clauses evaluate to, respectively.

How to properly fetch the next rows in a database

1 Answers1