To use sqlite for populate UITableViews, I copy all the data to NSArrays/Dicts so I can travel back/forward on the list. However, this is wastefull and requiere to have the data twice.
I wonder if is possible to have [FMResultSet previous] and/or [FMResultSet goRecord:RECNO] so I can get rid of the copy and use a FMResultSet directly.
So the sqlite API provide this possibility?
There is a nice article that Richard Hipp, the create of SQLite, has written about why SQLite does not have the functionality of stepping backwards, and why this is a very hard problem in-general. The article also offers some workarounds that you might find interesting.
Quoting from that article,
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.