On 2016-03-03 11:27 AM, James K. Lowden wrote: > On Thu, 3 Mar 2016 10:43:26 +0800 (CST) > ?? <qiulang2000 at 126.com> wrote: > >>> Can anyone describe a situation for which this style of LIMIT & >>> OFFSET is advisable from the application's point of view? (The >>> DBMS costs are obvious enough.) >> >> For me this is the easiest way to implement a scrolling cursor. >> Otherwise I will have to record for each table the condition I use >> to scroll it. So if there is an easier way to implement the >> scrolling cursor please let me know. > > You say, "record ... the condition ... to scroll [the table]". I'm > sure I don't know what "condition" you mean. > > You appear to be doing something like: > > offset = 0 > do > sqlte3_prepare (limit, offset) > do > sqlite3_step > until SQLITE_DONE > offset += limit > ... application stuff ... > until last row processed > > The obvious, better alternative is > > sqlte3_prepare > do > do > sqlite3_step > until LIMIT > ... application stuff ... > until last row processed > > because in the second example you're using SQLite's cursor to "record > the condition of the table". There's nothing for you to keep between > "pages"; you just start from where you stopped. > > How exactly is the first way "easiest"? > > --jkl
If these are pages displayed to the user, they may want to scroll backwards at some point; I don't expect that sqlite3_step is bidirectional, but if it is that's a pleasant surprise; the approaches using SQL can go backwards as easily as forwards, but really it depends on the use case. -- Darren Duncan
