pStmt = sqlite3_prepare_v2("SELECT * FROM xyz");
while( sqlite3_step(pStmt) ){
/* Do something with the current row */
}
sqlite3_finalize(pStmt);
(Aside: The foregoing is pseudocode because the calling conventions for sqlite3_prepare_v2() are not right. The function is simplified for clarity of presentation. Do not get hung up by this.)
The sqlite3_step() function above seems to advance a cursor forwards through the result set. It is natural to then ask why there is not a corresponding sqlite3_step_backwards() function to move backwards through the result set. It seems like sqlite3_step_backwards() should be easy to implement, after all. Just step backwards through the result set instead of forwards...
But it is not easy at all. In fact, the designers of SQLite have been unable to think of an algorithm that will do the job in the general case. Stepping backwards through the result set is easy for some special cases, such as the simple example query above. But things get more complicated, for example, if the query were really a 4-way LEFT OUTER JOIN with subqueries both in the result columns and in the WHERE clause.
The problem is that the sqlite3_step() function does not step through a precomputed result set at all. A better and more realistic way to think about matters is to suppose that each prepared statement is really a computer program. You are running this program in a debugger and there is a breakpoint set on a single statement somewhere deep down inside the computation. Calling the sqlite3_step() function is like pressing the "Run" button in your debugger and thus asking the debugger to run the program until it either exits or hits the breakpoint. Sqlite3_step() returns SQLITE_ROW if it hits the breakpoint and SQLITE_DONE if it finishes. If you hit the breakpoint, you can then look at local variable in order to find the values of a "row". Then you can press the "Run" button (call sqlite3_step()) again to continue execution until the next breakpoint or until the program exits.
From this point of view (which is much closer to how SQLite works on the inside) asking for an sqlite3_step_backward() button is really like expecting your symbolic debugger to be able to run backwards or to "undo" its execution back to the previous breakpoint. Nobody reasonably expects debuggers to be able to do this, so you shouldn't expect SQLite to be able to sqlite3_step_backward() either.
Work Arounds
The usual reason for wanting to move backwards through a query is that the programmer is working on an MP3 player of some kind. They want to query the database for all tracks that meet some criteria and then display the results in a scrolling window. Often this happens on a cellphone or some other display-limited device which can only show 5 lines of the 300 line result set. So the programmer calls sqlite3_step() 5 times, writes the results on the screen, then waits for the user to press the "Down" button to call sqlite3_step() 5 more times to display the next five lines, and so forth.
There a couple of problems with this approach. First off, since neither sqlite3_reset() nor sqlite3_finalize() are called on the statement, the database is locked and other processes are unable to update it. But perhaps more seriously is that there is not a good way to respond when the user presses the "Up" button to scroll back up. There is no sqlite3_step_backwards() function in SQLite. It is normally at this point in the reasoning process that the programmer gets on the mailing list asking for how to "scroll backwards".
Rule Number 1: Do not leave queries open waiting for the user input. Run a query to fill up the screen with as much information as it will hold then reset() or finalize() the query statment. Get in, grab your data, then get out. Later on, when the user decides to scroll up or down (which will usually be eons of time later from the point of view of your CPU), run another query to refresh the screen with new data.
Usually the list being displayed will be sorted.. Suppose you are wanting to display the "title" column of the "tracks" table. The secret is to remember the title of first and last entry current on display. Initialize the display using something like this:
SELECT title FROM tracks
WHERE singer='Madonna'
ORDER BY title
LIMIT 5;
(Disclaimer: The preceding is just an example. The author is only vaguely aware that Madonna is a singer, owns no Madonna tracks, knows the names of no songs sung by Madonna, and would be hard pressed to pick out Madonna from a photo line-up. This is just an example. In a real application you will want to substitute a different search criteria in the WHERE clause.)
The LIMIT 5 clause is optional. You can omit the LIMIT 5 then just call sqlite3_finalize() on the statement after you get the first 5 rows out. It is not required to run sqlite3_step() until it returns SQLITE_DONE. You can cancel a query by calling sqlite3_finalize() or sqlite3_reset() at any time. The LIMIT 5 clause is just for clarity.
As the display is initialized, you must remember the first and last title being displayed in separate variables outside of SQLite. To scroll down, run this query:
SELECT title FROM tracks
WHERE singer='Madonna'
AND title>:lasttitle
ORDER BY title
LIMIT 5;
To scroll up, run this query:
SELECT title FROM tracks
WHERE singer='Madonna'
AND title<:firsttitle
ORDER BY title DESC
LIMIT 5;
For the scrolldown query, bind the name of the last title current on display to the :lasttitle parameter. For the scrollup query, bind the title of the first line currently being displayed to :firsttitle. On the scrollup query, the titles come out in reverse order. (Notice the DESC modifier on the ORDER BY clause!) As the new query runs and the display is refilled, be sure to remember the new first and last entry in case the user presses the Up or Down buttons again.
Assuming you have an index like this:
CREATE INDEX example1 ON tracks(singer, title);
then the scrollup and scrolldown queries above will be very efficient. Even if there are 300 million matching tracks, SQLite will jump right to the first one that you need (using a binary search), read off 5 more in quick succession, and be done. The queries will be very fast even on slow flash memory cards. The queries will continue to be fast even if they involve complex outer joins and subqueries (within reason). Fast, that is, assuming you have the indices set up right. (Setting up the indices right is a whole other story, though, which we will not go into here.)
What Not To Do
Another error that crops up frequently is programmers trying to implement a scrolling window using LIMIT and OFFSET. The idea here is that you first just remember the index of the top entry in the display and run a query like this:
SELECT title FROM tracks
WHERE singer='Madonna'
ORDER BY title
LIMIT 5 OFFSET :index
The index is initialized to 0. To scroll down just increment the index by 5 and rerun the query. To scroll up, decrement the index by 5 and rerun.
The above will work actually. The problem is that it gets slow when the index gets large. The way OFFSET works in SQLite is that it causes the sqlite3_step() function to ignore the first :index breakpoints that it sees. So, for example, if :index is 1000, you are really reading in 1005 entries and ignoring all but the last 5. The net effect is that scrolling starts to become sluggish as you get lower and lower in the list.
Actually, depending on how big your list is and how fast your machine runs, you might easily get away with using OFFSET this way. OFFSET will often work OK on a workstation. But on battery powered devices with slow CPUs and slower mass storage, using the OFFSET approach usually breaks down when the list becomes large.
Rule Number 2: Do not try to implement a scrolling window using LIMIT and OFFSET. Doing so will become sluggish as the user scrolls down toward the bottom of the list.