Re: [sqlite] Re: Number of rows in a query result

[Dennis Cote]

Puneet Kishor wrote:

Igor Tandetnik wrote:
Alfredo Cole <[EMAIL PROTECTED]> wrote:

In order to update a progress bar, I need to know the total number of
rows returned by a query, similar to MySQL's mysql_num_rows. Is there
a function like that in the C API? I may have overlooked it, but have
not found it so far.


There is no such function. Most of the time, SQLite does not know how 
many rows there are in the resultset until they are all actually 
retrieved. It just produces rows one by one as it finds them.

The only way I know to achieve what you want is to run the query 
twice, first as "select count(*)" to obtain the count, then again 
with the desired column list. Depending on the query, "select 
count(*)" may take as long as the full query, and in some rare cases 
it may even be slower. Bottom line, it is impractical to try and 
produce an accurate progress indicator for SQLite queries.



one suggestion has been to create another table with a single row 
holding the number of rows in your table of interest. Just query that 
one table for its one value.

Use TRIGGERs  on INSERT, UPDATE, and DELETE to automatically adjust 
the value in the table with the row count.


This only works if your select query is returning all the records in the 
table. If you use where conditions to select a subset of the rows this 
will fail because any query could return a number of rows that won't 
match your carefully maintained count.