Number of rows in a query result

classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Number of rows in a query result

Alfredo Cole-2
Hi:

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.

Thank you.

--
Alfredo J. Cole
Grupo ACyC
Reply | Threaded
Open this post in threaded view
|

Re: Number of rows in a query result

rlodina
Hi Alfredo,


I supose you don't use
sqlite3_get_table<http://www.sqlite.org/capi3ref.html#sqlite3_get_table>function
right ?


Call another query with:
SELECT COUNT(*) AS NrRecords FROM ( <YOUR_ORIGINAL QUERY_STRING>)


Radu Lodina

On 10/28/05, Alfredo Cole <[hidden email]> wrote:

>
> Hi:
>
> 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.
>
> Thank you.
>
> --
> Alfredo J. Cole
> Grupo ACyC
>
Reply | Threaded
Open this post in threaded view
|

Re: Number of rows in a query result

Igor Tandetnik
In reply to this post by Alfredo Cole-2
Alfredo Cole <[hidden email]> 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.

Igor Tandetnik

Reply | Threaded
Open this post in threaded view
|

Re: Re: Number of rows in a query result

Puneet Kishor
Igor Tandetnik wrote:

> Alfredo Cole <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Number of rows in a query result

Alfredo Cole-2
In reply to this post by rlodina
El Jueves, 27 de Octubre de 2005 15:44, Radu Lodina escribió:
 > Hi Alfredo,
 >
 >
 > I supose you don't use
 > sqlite3_get_table<http://www.sqlite.org/capi3ref.html#sqlite3_get_table>fu
 >nction right ?
 >
 >
 > Call another query with:
 > SELECT COUNT(*) AS NrRecords FROM ( <YOUR_ORIGINAL QUERY_STRING>)
 >
 >
 > Radu Lodina

I read about the sqlite3_get_table function, which the manual refers to as
"really just a wrapper around sqlite3_exec()", which in turn "is just a
wrapper around calls to the prepared statement interface". Since I'm using
prepare, step and finalize as the recommended procedure, I thought there
might be a method to get the number of rows directly. I will try the
get_table funtion.

Thank you.

--
Alfredo J. Cole
Grupo ACyC
Reply | Threaded
Open this post in threaded view
|

Re: Re: Number of rows in a query result

Dennis Cote
In reply to this post by Puneet Kishor
Puneet Kishor wrote:

> Igor Tandetnik wrote:
>
>> Alfredo Cole <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: Number of rows in a query result

Huanghongdong
In reply to this post by Alfredo Cole-2
there is a wrapper named CppSQLite,it has a method to get the rows of query result,maybe you can look into the source and get the answer or you can just use the wrapper,I used the wrapper,it works fine.
 best regards

> Hi:
>
> 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.
>
> Thank you.
>
> --
> Alfredo J. Cole
> Grupo ACyC
>  

Reply | Threaded
Open this post in threaded view
|

Re: Re: Number of rows in a query result

vidushi tandon
In reply to this post by Dennis Cote
hi all

         can anyone tell me how to port sqlite
databases to the handheld devices

rgds
vidushi tandon

--- Dennis Cote <[hidden email]> wrote:

> Puneet Kishor wrote:
>
> > Igor Tandetnik wrote:
> >
> >> Alfredo Cole
> <[hidden email]> 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.
>



       
               
__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
Reply | Threaded
Open this post in threaded view
|

RE: Re: Number of rows in a query result

Eric Pankoke
If by handheld you mean PocketPCs, then:
http://sqlite-wince.sourceforge.net/.  The database file itself stays
the same.  If you want Palm support, you'll be a lot harder pressed to
find anything.

Eric Pankoke
Founder
Point Of Light Software
http://www.polsoftware.com/
 

-----Original Message-----
From: vidushi tandon [mailto:[hidden email]]
Sent: Friday, October 28, 2005 1:09 AM
To: [hidden email]
Subject: Re: [sqlite] Re: Number of rows in a query result

hi all

         can anyone tell me how to port sqlite
databases to the handheld devices

rgds
vidushi tandon

--- Dennis Cote <[hidden email]> wrote:

> Puneet Kishor wrote:
>
> > Igor Tandetnik wrote:
> >
> >> Alfredo Cole
> <[hidden email]> 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.
>



       
               
__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com


Reply | Threaded
Open this post in threaded view
|

Re: Re: Number of rows in a query result

Edward Wilson
In reply to this post by Dennis Cote
Sqlite-Kernel-Hackers:

This is really an important feature to have, I am surprised it's not already there.  I too have
had the same question but have just never asked.

btw, is this by any chance on the feature road-map?

The idea of issuing two selects is, well, a hack, and knowing how many records one has in a
result-set is a powerful feature.

-
ed



               
__________________________________
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
Reply | Threaded
Open this post in threaded view
|

Re: Re: Number of rows in a query result

Igor Tandetnik
Edward Wilson <web2ed-/[hidden email]> wrote:
> This is really an important feature to have, I am surprised it's not
> already there.  I too have had the same question but have just never
> asked.

Well, if the golden standard is mysql_num_rows, consider the following
bit in the documentation:

<quote>
The use of mysql_num_rows() depends on whether you use
mysql_store_result() or mysql_use_result() to return the result set. If
you use mysql_store_result(), mysql_num_rows() may be called
immediately. If you use mysql_use_result(), mysql_num_rows() does not
return the correct value until all the rows in the result set have been
retrieved.
</quote>

mysql_store_result is essentially equivalent to sqlite3_get_table - it
does not return until all rows are retrieved and stored in memory. Note
that sqlite3_get_table does return the number of rows. Of course it is
pretty useless for the purpose of providing progress indication.

mysql_use_result is equivalent to calling sqlite3_step in a loop - and
just as in SQLite, MySQL cannot return the row count until all rows are
fetched. Well, SQLite does not provide the row count in this case
either, but you can easily maintain your own counter. Again, this does
not help much with progress indicator.

> The idea of issuing two selects is, well, a hack, and knowing how
> many records one has in a result-set is a powerful feature.

The fundamental problem is that, for many queries, counting all rows is
actually as complex a task as retrieving them in the first place. This
is a property of SQL language and relational data model in general, it's
not specific to SQLite or any other engine. There is no way around it.

Igor Tandetnik

Reply | Threaded
Open this post in threaded view
|

Re: Re: Re: Number of rows in a query result

John Stanton-3
Igor puts it very clearly.  No DBMS knows how many rows are in a
selection until the selection has been performed.  Therefore you cannot
do a progress bar since the selection is complete before you have the
necessary information to build the bar.  The answer is to use some other
form of progress indicator, such as a running count, a running man or
even a running Duke etc.  Performing two SELECTs is rather pointless
unless the progress bar is meant to present the progress of post-SELECT
processing which is very much slower than the actual SELECT.

One of the nice feature of Sqlite is it's callback and step functions so
that it is not necessary to store any results, permitting the building
of robust programs which cannot hit local storage limits and fail.
JS

Igor Tandetnik wrote:

> Edward Wilson <web2ed-/[hidden email]> wrote:
>
>> This is really an important feature to have, I am surprised it's not
>> already there.  I too have had the same question but have just never
>> asked.
>
>
> Well, if the golden standard is mysql_num_rows, consider the following
> bit in the documentation:
>
> <quote>
> The use of mysql_num_rows() depends on whether you use
> mysql_store_result() or mysql_use_result() to return the result set. If
> you use mysql_store_result(), mysql_num_rows() may be called
> immediately. If you use mysql_use_result(), mysql_num_rows() does not
> return the correct value until all the rows in the result set have been
> retrieved.
> </quote>
>
> mysql_store_result is essentially equivalent to sqlite3_get_table - it
> does not return until all rows are retrieved and stored in memory. Note
> that sqlite3_get_table does return the number of rows. Of course it is
> pretty useless for the purpose of providing progress indication.
>
> mysql_use_result is equivalent to calling sqlite3_step in a loop - and
> just as in SQLite, MySQL cannot return the row count until all rows are
> fetched. Well, SQLite does not provide the row count in this case
> either, but you can easily maintain your own counter. Again, this does
> not help much with progress indicator.
>
>> The idea of issuing two selects is, well, a hack, and knowing how
>> many records one has in a result-set is a powerful feature.
>
>
> The fundamental problem is that, for many queries, counting all rows is
> actually as complex a task as retrieving them in the first place. This
> is a property of SQL language and relational data model in general, it's
> not specific to SQLite or any other engine. There is no way around it.
>
> Igor Tandetnik

Reply | Threaded
Open this post in threaded view
|

Re: Re: Number of rows in a query result

srs-5
In reply to this post by Edward Wilson
Edward Wilson wrote:

>The idea of issuing two selects is, well, a hack, and knowing how many records one has in a
>result-set is a powerful feature
>  
>

Are you needing a progress bar for the search (ie the query?) Or some
action based on the result set?  If the later, get the result set as
your favorite container.. ask the container the size.  If its the first
then a "feature" won't help.  It still has to 'run' the query in order
to get the count.  It would be like me asking you to tell me how many
red Skittles are in a package before you open it.
As for being a 'hack' .. all your 'feature' would be is a pretty
programming interface around that hack.  As I said before, how can the
database know the number of items that will be returned without first
searching for them.
Reply | Threaded
Open this post in threaded view
|

Re: Re: Number of rows in a query result

Puneet Kishor

On Oct 28, 2005, at 7:20 PM, SRS wrote:

> Edward Wilson wrote:
>
>> The idea of issuing two selects is, well, a hack, and knowing how
>> many records one has in a
>> result-set is a powerful feature
>>
>
> Are you needing a progress bar for the search (ie the query?) Or some
> action based on the result set?  If the later, get the result set as
> your favorite container.. ask the container the size.  If its the
> first then a "feature" won't help.  It still has to 'run' the query in
> order to get the count.  It would be like me asking you to tell me how
> many red Skittles are in a package before you open it. As for being a
> 'hack' .. all your 'feature' would be is a pretty programming
> interface around that hack.  As I said before, how can the database
> know the number of items that will be returned without first searching
> for them.
>

I think the problem is not so much (at least IMHO) that two queries
have to be performed (that itself is a reasonable expectation), but
that the COUNT(*) query is likely to be slow because of the full table
scan. One option is to use an aftermarket solution... for example, in
my Perl applications once I have queried the db for the columns based
on my criteria, I simply count the number of elements in my record set
thereby avoiding a double query to the database. Although, in reality,
I personally don't mind the COUNT(*) option... none of my databases are
that large to merit worrying about this.

Reply | Threaded
Open this post in threaded view
|

Re: Re: Number of rows in a query result

Jay Sprenkle
I had this same problem some time ago. We were trying to make a virtual list
box that could handle ANY number of items. The scroll bar is the wrinkle. You
can't set it or size it without knowing the size of the list.

My conclusion was this:

A list of more than 100 or so items on screen is really useless for humans.
It's so long nobody will read the entire thing. It's good enough to present what
you can load into memory (and you can count this result set easily).

We eliminated the need for this because in reality it wasn't very useful.

"When in doubt, chop it out."
Reply | Threaded
Open this post in threaded view
|

Re: Re: Number of rows in a query result

Edward Wilson
In reply to this post by Puneet Kishor
>I simply count the number of elements in my record set
>thereby avoiding a double query to the database.

Yes, exactly, I take for granted that the resultset is accumulated at the database level and not
at the application level.


-
ed

--- Puneet Kishor <[hidden email]> wrote:

>
> On Oct 28, 2005, at 7:20 PM, SRS wrote:
>
> > Edward Wilson wrote:
> >
> >> The idea of issuing two selects is, well, a hack, and knowing how
> >> many records one has in a
> >> result-set is a powerful feature
> >>
> >
> > Are you needing a progress bar for the search (ie the query?) Or some
> > action based on the result set?  If the later, get the result set as
> > your favorite container.. ask the container the size.  If its the
> > first then a "feature" won't help.  It still has to 'run' the query in
> > order to get the count.  It would be like me asking you to tell me how
> > many red Skittles are in a package before you open it. As for being a
> > 'hack' .. all your 'feature' would be is a pretty programming
> > interface around that hack.  As I said before, how can the database
> > know the number of items that will be returned without first searching
> > for them.
> >
>
> I think the problem is not so much (at least IMHO) that two queries
> have to be performed (that itself is a reasonable expectation), but
> that the COUNT(*) query is likely to be slow because of the full table
> scan. One option is to use an aftermarket solution... for example, in
> my Perl applications once I have queried the db for the columns based
> on my criteria, I simply count the number of elements in my record set
> thereby avoiding a double query to the database. Although, in reality,
> I personally don't mind the COUNT(*) option... none of my databases are
> that large to merit worrying about this.
>
>



               
__________________________________
Start your day with Yahoo! - Make it your home page!
http://www.yahoo.com/r/hs
Reply | Threaded
Open this post in threaded view
|

Re: Re: Number of rows in a query result

Puneet Kishor

On Oct 31, 2005, at 7:54 PM, Edward Wilson wrote:

>> I simply count the number of elements in my record set
>> thereby avoiding a double query to the database.
>
> Yes, exactly, I take for granted that the resultset is accumulated at
> the database level and not
> at the application level.

sorry, I don't quite understand what you imply by the above. Obviously
this discussion stems from the fact that you can't take that for
granted, at least not without paying some cost for it. Because I don't
want to tie up the db doing double queries, I just do it in the
application.



>
>
> -
> ed
>
> --- Puneet Kishor <[hidden email]> wrote:
>
>>
>> On Oct 28, 2005, at 7:20 PM, SRS wrote:
>>
>>> Edward Wilson wrote:
>>>
>>>> The idea of issuing two selects is, well, a hack, and knowing how
>>>> many records one has in a
>>>> result-set is a powerful feature
>>>>
>>>
>>> Are you needing a progress bar for the search (ie the query?) Or some
>>> action based on the result set?  If the later, get the result set as
>>> your favorite container.. ask the container the size.  If its the
>>> first then a "feature" won't help.  It still has to 'run' the query
>>> in
>>> order to get the count.  It would be like me asking you to tell me
>>> how
>>> many red Skittles are in a package before you open it. As for being a
>>> 'hack' .. all your 'feature' would be is a pretty programming
>>> interface around that hack.  As I said before, how can the database
>>> know the number of items that will be returned without first
>>> searching
>>> for them.
>>>
>>
>> I think the problem is not so much (at least IMHO) that two queries
>> have to be performed (that itself is a reasonable expectation), but
>> that the COUNT(*) query is likely to be slow because of the full table
>> scan. One option is to use an aftermarket solution... for example, in
>> my Perl applications once I have queried the db for the columns based
>> on my criteria, I simply count the number of elements in my record set
>> thereby avoiding a double query to the database. Although, in reality,
>> I personally don't mind the COUNT(*) option... none of my databases
>> are
>> that large to merit worrying about this.
>>
>>
>
>
>
>
> __________________________________
> Start your day with Yahoo! - Make it your home page!
> http://www.yahoo.com/r/hs

Reply | Threaded
Open this post in threaded view
|

Re: Re: Number of rows in a query result

Edward Wilson
What I was trying to say was: with other db products the drivers (or something somewhere)
calculated the number of rows returned in a query automagicly.  I have never had to do anything
'extra' to get the number of rows in a query other than resultset-object.rowcout - 'rowcout' being
whatever the syntax was for that particular environment.  So what I meant was, I have always taken
for granted that the rowcount was 'apart of' the query returned from the database and not
something that I had to do 'extra' in addition to fetching the data to begin with.  I hope this
was clear.

-
ed

--- Puneet Kishor <[hidden email]> wrote:

>
> On Oct 31, 2005, at 7:54 PM, Edward Wilson wrote:
>
> >> I simply count the number of elements in my record set
> >> thereby avoiding a double query to the database.
> >
> > Yes, exactly, I take for granted that the resultset is accumulated at
> > the database level and not
> > at the application level.
>
> sorry, I don't quite understand what you imply by the above. Obviously
> this discussion stems from the fact that you can't take that for
> granted, at least not without paying some cost for it. Because I don't
> want to tie up the db doing double queries, I just do it in the
> application.
>
>
>
> >
> >
> > -
> > ed
> >
> > --- Puneet Kishor <[hidden email]> wrote:
> >
> >>
> >> On Oct 28, 2005, at 7:20 PM, SRS wrote:
> >>
> >>> Edward Wilson wrote:
> >>>
> >>>> The idea of issuing two selects is, well, a hack, and knowing how
> >>>> many records one has in a
> >>>> result-set is a powerful feature
> >>>>
> >>>
> >>> Are you needing a progress bar for the search (ie the query?) Or some
> >>> action based on the result set?  If the later, get the result set as
> >>> your favorite container.. ask the container the size.  If its the
> >>> first then a "feature" won't help.  It still has to 'run' the query
> >>> in
> >>> order to get the count.  It would be like me asking you to tell me
> >>> how
> >>> many red Skittles are in a package before you open it. As for being a
> >>> 'hack' .. all your 'feature' would be is a pretty programming
> >>> interface around that hack.  As I said before, how can the database
> >>> know the number of items that will be returned without first
> >>> searching
> >>> for them.
> >>>
> >>
> >> I think the problem is not so much (at least IMHO) that two queries
> >> have to be performed (that itself is a reasonable expectation), but
> >> that the COUNT(*) query is likely to be slow because of the full table
> >> scan. One option is to use an aftermarket solution... for example, in
> >> my Perl applications once I have queried the db for the columns based
> >> on my criteria, I simply count the number of elements in my record set
> >> thereby avoiding a double query to the database. Although, in reality,
> >> I personally don't mind the COUNT(*) option... none of my databases
> >> are
> >> that large to merit worrying about this.
> >>
> >>
> >
> >
> >
> >
> > __________________________________
> > Start your day with Yahoo! - Make it your home page!
> > http://www.yahoo.com/r/hs
>
>



               
__________________________________
Start your day with Yahoo! - Make it your home page!
http://www.yahoo.com/r/hs
Reply | Threaded
Open this post in threaded view
|

Re: Re: Number of rows in a query result

Eric Bohlman
Edward Wilson wrote:
> What I was trying to say was: with other db products the drivers (or something somewhere)
> calculated the number of rows returned in a query automagicly.  I have never had to do anything
> 'extra' to get the number of rows in a query other than resultset-object.rowcout - 'rowcout' being
> whatever the syntax was for that particular environment.  So what I meant was, I have always taken
> for granted that the rowcount was 'apart of' the query returned from the database and not
> something that I had to do 'extra' in addition to fetching the data to begin with.  I hope this
> was clear.

I think you'll find that any interface involving a "resultset object" is
really a wrapper that talks to the database at a fairly low level and
actually retrieves all the selected rows into its internal memory, later
parcelling them out in response to method calls.  Of course it can count
the rows as it retrieves them and make the count available through a
method or variable.  SQLite's native API actually corresponds to the
low-level communication between the wrapper and database; the row
counting would be done by the code that calls the API.  I believe that a
while back drh strongly implied that most substantial code should be
accessing SQLite via a wrapper rather than the "raw" API.
Reply | Threaded
Open this post in threaded view
|

Re: Re: Number of rows in a query result

Paolo Vernazza
In reply to this post by Edward Wilson
Edward Wilson wrote:

>What I was trying to say was: with other db products the drivers (or something somewhere)
>calculated the number of rows returned in a query automagicly.  I have never had to do anything
>'extra' to get the number of rows in a query other than resultset-object.rowcout - 'rowcout' being
>whatever the syntax was for that particular environment.  So what I meant was, I have always taken
>for granted that the rowcount was 'apart of' the query returned from the database and not
>something that I had to do 'extra' in addition to fetching the data to begin with.  I hope this
>was clear.
>
The other DB, usually, perform the query, retrive all the rows and
stores them somewere. When you get the number of rows in your query, the
query had been completly executed.

SQLite does the same. Your resulset is called "table" and you can get it
using sqlite3_get_table("SELECT bla bla bla bla bla").

But Sqlite permits *also* to retrive the data as soon as they are
calculated. This permits to save memory (no one has to store somewhere
the resultset) and to process the results while the query is executed.

Paolo
12