How to seek stmt to row number in SQLite3?

Question

I don't find the way to seek in a sqlite3_stmt.

I have an input view to let user input and execute SQL, but maybe the result has a huge amounts of records. I just want to display a piece of the result.

E.g.: I have 20000 rows in stmt. But I want to display the first 2000 rows (0-2000). When the user clicks the next button, I want to display the second 2000 rows (2000-4000). When the user click the previous button, I want the display to rewind. So I want seek in the stmt. But I cannot find some SQLite3 function to seek the stmt.

I use the C/C++ interface.

http://stackoverflow.com/questions/2497677/using-the-limit-statement-in-a-sqlite-query — tidwall, Apr 28 '13 at 06:00

score 2 · Accepted Answer · answered Apr 28 '13 at 05:59

SQLite computes the returned values of a statement on the fly. The only way to move to another row is to call sqlite3_step; it is not possible to move backwards.

You could read all rows and save them somewhere, but this becomes inpractical when there are too many of them.

You could execute separate queries with the LIMIT/OFFSET clause to restrict the result to the current window. However, this becomes inefficient when the offset value becomes bigger because SQLite still has to compute all those records.

To implement a scrolling cursor, you should save the last(/first) record of the page, and ask the database to return the next(/previous) 2000 records after(/before) that record. This is explained in detail in the SQLite wiki.

How to seek stmt to row number in SQLite3?

1 Answers1