RE: [sqlite] how to iterate on SELECT query results
SQLite use the 'cursor' style like SQL Server / ORACLE in stored procedures: you fetch in a unidirectional (forward only) way. To achieve bi-directional support you must have to store the results in memory as you fetch them (not so difficult to accomplish if you have enough memory to do it, a bit complicated if not). -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: terça-feira, 4 de setembro de 2007 17:17 To: sqlite-users@sqlite.org Subject: Re: [sqlite] how to iterate on SELECT query results Use sqlite3_step to read each row in sequence. Babu, Lokesh wrote: > Dear all, > > After doing some SELECT operation on a TABLE, say we get 100 result > items out of 1000 records, Is there any way where I can iterate > through this result set. i.e., Get N items out of 100, say get > previous 10, get next 10, etc, > > This should be done without creating a temporary table or virtual > tables. As there is overhead of space and time. > > If it is possible to use VIEWs then how can I? As I think VIEWs are > little better than temp table or virtual table. Please correct me if > I'm wrong. > > please reply, thanks in advance. > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] how to iterate on SELECT query results
Use sqlite3_step to read each row in sequence. Babu, Lokesh wrote: Dear all, After doing some SELECT operation on a TABLE, say we get 100 result items out of 1000 records, Is there any way where I can iterate through this result set. i.e., Get N items out of 100, say get previous 10, get next 10, etc, This should be done without creating a temporary table or virtual tables. As there is overhead of space and time. If it is possible to use VIEWs then how can I? As I think VIEWs are little better than temp table or virtual table. Please correct me if I'm wrong. please reply, thanks in advance. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] how to iterate on SELECT query results
On 9/3/07, Babu, Lokesh <[EMAIL PROTECTED]> wrote: > After doing some SELECT operation on a TABLE, say we get 100 result > items out of 1000 records, Is there any way where I can iterate > through this result set. i.e., Get N items out of 100, say get > previous 10, get next 10, etc, > > This should be done without creating a temporary table or virtual > tables. As there is overhead of space and time. There is always the overhead of space or time, as it's impossible to know what the results are unless you actually calculate them. To get row 100, the query must find rows 1-99 first. The LIMIT and OFFSET clauses of the SELECT statement can be used to get parts of a query without storing anything. This is the worst case of time overhead though, since the entire query must be run every time. An approach that works for many people is to store markers for use in relative queries. This requires a column with unique, ordered values, and that you run the query using it as an ORDER BY. An INTEGER PRIMARY KEY AUTOINCREMENT column is useful for this; let's assume you have one named ID. If you retrieve rows 101-110, you can store the IDs for row #101 (let's say this is ID 234), and row #110 (ID 280). When you need to retrieve the previous 10 rows, you get them backwards using a query such as: SELECT * FROM table WHERE id < 234 ORDER BY id DESC LIMIT 10; This avoids having to retrieve rows for the entire result set when changing "pages" sequentially. You can imagine other schemes that involve storing all the IDs for the query result set so that you can jump to them at random, etc. > If it is possible to use VIEWs then how can I? As I think VIEWs are > little better than temp table or virtual table. Please correct me if > I'm wrong. Better for doing what? A view isn't going to help with paging result sets. - To unsubscribe, send email to [EMAIL PROTECTED] -
