Last record

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

Last record

Philippe RIO
A short question : how could I know if I am reading the last record with
sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a
function for that case which returns SQLITE_DONE? A function which is one
record in advance from sqlite3_step.

Thank every one



-----
--------
Kenavo

https://md5finder.blogspot.com/
(Never be pleased, always improve)
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
-------- Kenavo https://md5finder.blogspot.com/ (Never be pleased, always improve)
Reply | Threaded
Open this post in threaded view
|

Re: Last record

Simon Slavin-3
On 15 Oct 2019, at 4:34pm, Philippe RIO <[hidden email]> wrote:

> how could I know if I am reading the last record with
> sqlite  (sqlite3_step)?

Sorry, there's no way to do that for some arbitrary SELECT.  Because SQLite itself may not know.

SQLite does not always process your query and store all the results in memory.  If there's an ideal index for your query, each call to _step() just one more row.  SQLite itself doesn't know it has reached the end until it gets an error because it runs off the end of the index.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Last record

Philippe RIO
The only way is to make a query for getting the number of records and in the
second query I have to count the number of records retrieved to know if it
is the last one.

Thank you for the quick answer.



-----
--------
Kenavo

https://md5finder.blogspot.com/
(Never be pleased, always improve)
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
-------- Kenavo https://md5finder.blogspot.com/ (Never be pleased, always improve)
Reply | Threaded
Open this post in threaded view
|

Re: Last record

Simon Slavin-3
On 15 Oct 2019, at 5:38pm, Philippe RIO <[hidden email]> wrote:

> The only way is to make a query for getting the number of records and in the second query I have to count the number of records retrieved to know if it is the last one.

Assume your intended query specifies an order which has unique keys (in other words, you know exactly what order rows will be answered in.  Suppose it is

    SELECT ... ORDER BY a, b DESC, c

You can find the last row which will be returned by reversing the order and adding LIMIT 1

    SELECT ... ORDER BY a DESC, b ASC, c DESC LIMIT 1

You can then remember the value(s) of some column(s) of the row returned, and watch for the same one(s) when you do your desired SELECT.

Depending on the number of rows in your result set, this may or may not be faster than counting the number of rows.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Last record

Chris Green
In reply to this post by Philippe RIO
Philippe RIO <[hidden email]> wrote:
> A short question : how could I know if I am reading the last record with
> sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a
> function for that case which returns SQLITE_DONE? A function which is one
> record in advance from sqlite3_step.
>
What do you mean by "last record"?

--
Chris Green
·

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Last record

Jim Morris
In reply to this post by Simon Slavin-3
In your application you can create a wrapping iterator that pre-reads
the next value instead or directly accessing the low level step
function.  Then you can ask if it is the last.

On 10/15/2019 9:44 AM, Simon Slavin wrote:

> On 15 Oct 2019, at 5:38pm, Philippe RIO <[hidden email]> wrote:
>
>> The only way is to make a query for getting the number of records and in the second query I have to count the number of records retrieved to know if it is the last one.
> Assume your intended query specifies an order which has unique keys (in other words, you know exactly what order rows will be answered in.  Suppose it is
>
>     SELECT ... ORDER BY a, b DESC, c
>
> You can find the last row which will be returned by reversing the order and adding LIMIT 1
>
>     SELECT ... ORDER BY a DESC, b ASC, c DESC LIMIT 1
>
> You can then remember the value(s) of some column(s) of the row returned, and watch for the same one(s) when you do your desired SELECT.
>
> Depending on the number of rows in your result set, this may or may not be faster than counting the number of rows.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Last record

Jens Alfke-2
In reply to this post by Simon Slavin-3


> On Oct 15, 2019, at 9:44 AM, Simon Slavin <[hidden email]> wrote:
>
> You can then remember the value(s) of some column(s) of the row returned, and watch for the same one(s) when you do your desired SELECT.

As long as all the result rows are unique…

All of these workaround seem more expensive/complex than just adapting your code so it doesn't have to know the last row in advance.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Last record

Don V Nielsen
Pardon me for being thick. But the end of what? The end of the sqlite file?
The end of a table? The end of a select?

I always thought there was no such thing as "a start or an end" as the
database is basically air until you request something from it. Even when
you have something, it could change in the very next exec of the query. So
what is the "start" and what is the "end"?

Looking at a sqlite file as a whole, is it the row at the very end of the
file or the last row inserted, which could be located in some page that is
not even the end of the file.

I don't think I understand too well.

On Tue, Oct 15, 2019 at 11:53 AM Jens Alfke <[hidden email]> wrote:

>
>
> > On Oct 15, 2019, at 9:44 AM, Simon Slavin <[hidden email]> wrote:
> >
> > You can then remember the value(s) of some column(s) of the row
> returned, and watch for the same one(s) when you do your desired SELECT.
>
> As long as all the result rows are unique…
>
> All of these workaround seem more expensive/complex than just adapting
> your code so it doesn't have to know the last row in advance.
>
> —Jens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Last record

Keith Medcalf
In reply to this post by Philippe RIO

On Tuesday, 15 October, 2019 09:35, Philippe RIO <[hidden email]> wrote:

>A short question : how could I know if I am reading the last record with
>sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a
>function for that case which returns SQLITE_DONE? A function which is one
>record in advance from sqlite3_step.

When sqlite3_step returns SQLITE_DONE there are no more rows.

A prepared statement is a row generator.  Each time you ask it to produce a row (sqlite3_step) it does whatever needs to be done to get you the next row and gives it to you (SQLITE_ROW).  Eventually it cannot generate another row and you get the message that the generator is empty (SQLITE_DONE).

Think of sqlite3 as a maid.  You place your order for peanuts with the maid (sqlite3_prepare).  The maid then tells you whether the order was accepted or not (SQLITE_OK or some other return code).  Every time you want a peanut you tell the maid to give you a peanut (sqlite3_exec).  If this is the first time that you have asked for a peanut, then the maid will have to run to the store and buy a tin of peanuts, open the tin, and give you a peanut (SQLITE_ROW) -- though it might be discovered that the tin is empty to begin with, in which case the maid will tell you so when you first ask for a peanut (SQLITE_DONE).  Each time subsequently that you ask for a peanut the maid will simply give you another peanut from the tin.  Eventually, the tin will be empty and the maid will toss the empty tin in the rubbish and tell you that the tin is empty (SQLITE_DONE).  You may ask for another peanut in which case the maid will start all over again and go to the store for another tin of peanuts.  Lather, rinse, repeat.  Sometimes the store may be out of tins of peanuts, or the maid may get hit by a bus and killed on the way to and from the store, or may die of a heart attack in between you requesting peanuts.  In this case the butler will give you an error indication (SQLITE_ERROR) telling you what went awry.

Your only way of getting peanuts is to ask the maid for one, you cannot see the tin nor can you look inside it.  The only way that you have to know that the tin is empty is that when you ask for a peanut the maid replies "Sorry luv, but the tin is empty", rather than giving you a peanut.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Last record

Jose Isaias Cabrera-4


Keith Medcalf, on Tuesday, October 15, 2019 02:26 PM, wrote...

>
>
> On Tuesday, 15 October, 2019 09:35, Philippe RIO, on
>
> >A short question : how could I know if I am reading the last record with
> >sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a
> >function for that case which returns SQLITE_DONE? A function which is one
> >record in advance from sqlite3_step.
>
> When sqlite3_step returns SQLITE_DONE there are no more rows.
>
> A prepared statement is a row generator.  Each time you ask it to produce a
> row (sqlite3_step) it does whatever needs to be done to get you the next row
> and gives it to you (SQLITE_ROW).  Eventually it cannot generate another row
> and you get the message that the generator is empty (SQLITE_DONE).

[]clip]

> The only way that you have to know that the tin is empty is that when you ask
> for a peanut the maid replies "Sorry luv, but the tin is empty", rather than
> giving you a peanut.

Keith, may I say thank you.  Your parables are so teach-friendly, and more than that, funny.  I appreciate them. Thanks.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Last record

Don V Nielsen
Keith, what if one has a peanut allergy?

On Tue, Oct 15, 2019 at 1:33 PM Jose Isaias Cabrera <[hidden email]>
wrote:

>
>
> Keith Medcalf, on Tuesday, October 15, 2019 02:26 PM, wrote...
> >
> >
> > On Tuesday, 15 October, 2019 09:35, Philippe RIO, on
> >
> > >A short question : how could I know if I am reading the last record with
> > >sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a
> > >function for that case which returns SQLITE_DONE? A function which is
> one
> > >record in advance from sqlite3_step.
> >
> > When sqlite3_step returns SQLITE_DONE there are no more rows.
> >
> > A prepared statement is a row generator.  Each time you ask it to
> produce a
> > row (sqlite3_step) it does whatever needs to be done to get you the next
> row
> > and gives it to you (SQLITE_ROW).  Eventually it cannot generate another
> row
> > and you get the message that the generator is empty (SQLITE_DONE).
>
> []clip]
>
> > The only way that you have to know that the tin is empty is that when
> you ask
> > for a peanut the maid replies "Sorry luv, but the tin is empty", rather
> than
> > giving you a peanut.
>
> Keith, may I say thank you.  Your parables are so teach-friendly, and more
> than that, funny.  I appreciate them. Thanks.
>
> josé
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Last record

Keith Medcalf

On Tuesday, 15 October, 2019 13:52, Don V Nielsen <[hidden email]> wrote:

>Keith, what if one has a peanut allergy?

Well, if one were allergic to beans (colloquially called peanuts for some reason, even though they are not nuts) then I suppose real nuts would do.

I have a big tin of mixed Honey Roasted Nuts (Cashews, Almonds and Pistachios, so says the label) right beside me.  Though it does say that besides those and other nuts it might also contain beans (peanuts) and bean (peanut) oil ... so obviously YMMV :)

Interestingly I also have nearby an empty generic (Presidents Choice) plastic bottle of Honey Roasted Peanuts (the maid is not so efficient here at throwing the empties in the refuse).  The label, interestingly enough, warns that it might also contain actual nuts.

Perhaps I should have used Gummy Bears.  Are people allergic to those?  Do they even come in a tin (so you cannot see what is inside)?  
Or perhaps Sardines.  For sure those come in tins and do not contain beans (peanuts) or even real nuts ... or at least the Brunswick ones don't.

:)

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Last record

Doug
In reply to this post by Simon Slavin-3
How about something like this that costs more to run:

Given a table T with columns A, B, C,..
BEGIN TRANSACTION
SELECT Count(*) AS Count [filter spec];
SELECT A,B,C,... [filter spec];
ROLLBACK or COMMIT

Doug

> -----Original Message-----
> From: sqlite-users <[hidden email]>
> On Behalf Of Simon Slavin
> Sent: Tuesday, October 15, 2019 8:35 AM
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] Last record
>
> On 15 Oct 2019, at 4:34pm, Philippe RIO <[hidden email]>
> wrote:
>
> > how could I know if I am reading the last record with
> > sqlite  (sqlite3_step)?
>
> Sorry, there's no way to do that for some arbitrary SELECT.
> Because SQLite itself may not know.
>
> SQLite does not always process your query and store all the
> results in memory.  If there's an ideal index for your query, each
> call to _step() just one more row.  SQLite itself doesn't know it
> has reached the end until it gets an error because it runs off the
> end of the index.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Last record

Adam Levy
Why can't the knowledge of the "last row" be obtained and used by the
application after _step returns SQLITE_DONE?

Although what Doug suggested could work, it doesn't make sense to me to add
an extra count or max query just to know how many results will be returned
when those results will be queried anyway.


On Tue, Oct 15, 2019, 1:16 PM Doug <[hidden email]> wrote:

> How about something like this that costs more to run:
>
> Given a table T with columns A, B, C,..
> BEGIN TRANSACTION
> SELECT Count(*) AS Count [filter spec];
> SELECT A,B,C,... [filter spec];
> ROLLBACK or COMMIT
>
> Doug
>
> > -----Original Message-----
> > From: sqlite-users <[hidden email]>
> > On Behalf Of Simon Slavin
> > Sent: Tuesday, October 15, 2019 8:35 AM
> > To: SQLite mailing list <[hidden email]>
> > Subject: Re: [sqlite] Last record
> >
> > On 15 Oct 2019, at 4:34pm, Philippe RIO <[hidden email]>
> > wrote:
> >
> > > how could I know if I am reading the last record with
> > > sqlite  (sqlite3_step)?
> >
> > Sorry, there's no way to do that for some arbitrary SELECT.
> > Because SQLite itself may not know.
> >
> > SQLite does not always process your query and store all the
> > results in memory.  If there's an ideal index for your query, each
> > call to _step() just one more row.  SQLite itself doesn't know it
> > has reached the end until it gets an error because it runs off the
> > end of the index.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> > users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Last record

Warren Young
In reply to this post by Don V Nielsen
On Oct 15, 2019, at 1:52 PM, Don V Nielsen <[hidden email]> wrote:
>
> what if one has a peanut allergy?

You’re joking, but it gives us cause to extend the fable profitably: use the proper WHERE clause.

    SELECT * FROM food WHERE type != 'peanuts'

The maid delivers whatever you ask for, within the limits specified within the fable.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Last record

Wolfgang Enzinger
In reply to this post by Adam Levy
Am Tue, 15 Oct 2019 13:36:37 -0800 schrieb Adam Levy:

> Although what Doug suggested could work, it doesn't make sense to me to add
> an extra count or max query just to know how many results will be returned
> when those results will be queried anyway.

One typical use case I can think of is that one wants to display a progress
bar during population of a list control with a query result. Impossible as
long as you don't know beforehand what 100% is by means of an absolute
number.

Maybe that's the reason why those percentage based progress bars are more
and more replaced by animation controls nowadays that just indicate that
something is still going on, without any kind of prediction how much more
time it will take. ;-)

Wolfgang

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Last record

Hick Gunter
In reply to this post by Philippe RIO
The order of rows returned by a query is undefined - i.e. from the point of view of the application, a random member of the result set will be returned last - unless you include an ORDER BY clause that uniquely defines the order of the records to be returned. Given the latter, it is easy to define an exactly opposite ORDER BY clause and retrieve only the first record via LIMIT 1.

Why do you think you need to know if/how many records remain?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Philippe RIO
Gesendet: Dienstag, 15. Oktober 2019 17:35
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] Last record

A short question : how could I know if I am reading the last record with sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a function for that case which returns SQLITE_DONE? A function which is one record in advance from sqlite3_step.

Thank every one



-----
--------
Kenavo

https://md5finder.blogspot.com/
(Never be pleased, always improve)
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Last record

Hick Gunter
In reply to this post by Don V Nielsen
Then the first peanut may well be the last one, irrespective of the cardinality of the tin.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Don V Nielsen
Gesendet: Dienstag, 15. Oktober 2019 21:52
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Last record

Keith, what if one has a peanut allergy?

On Tue, Oct 15, 2019 at 1:33 PM Jose Isaias Cabrera <[hidden email]>
wrote:

>
>
> Keith Medcalf, on Tuesday, October 15, 2019 02:26 PM, wrote...
> >
> >
> > On Tuesday, 15 October, 2019 09:35, Philippe RIO, on
> >
> > >A short question : how could I know if I am reading the last record
> > >with sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW.
> > >Is there a function for that case which returns SQLITE_DONE? A
> > >function which is
> one
> > >record in advance from sqlite3_step.
> >
> > When sqlite3_step returns SQLITE_DONE there are no more rows.
> >
> > A prepared statement is a row generator.  Each time you ask it to
> produce a
> > row (sqlite3_step) it does whatever needs to be done to get you the
> > next
> row
> > and gives it to you (SQLITE_ROW).  Eventually it cannot generate
> > another
> row
> > and you get the message that the generator is empty (SQLITE_DONE).
>
> []clip]
>
> > The only way that you have to know that the tin is empty is that
> > when
> you ask
> > for a peanut the maid replies "Sorry luv, but the tin is empty",
> > rather
> than
> > giving you a peanut.
>
> Keith, may I say thank you.  Your parables are so teach-friendly, and
> more than that, funny.  I appreciate them. Thanks.
>
> josé
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Last record

David Raymond
In reply to this post by Don V Nielsen
"Keith, what if one has a peanut allergy?"

Well, the maid dutifully logs the changes she makes to the tin, so that in the event of an anaphylactic crash the tin can be returned to its original state. This helps ensure we have ACID peanuts.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users