sqlite3_reset() bug?

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

sqlite3_reset() bug?

Josef Barnes
Hi,

We've come across a situation where we think there is a bug in the
sqlite3_reset() function. The documentation states:

"Any SQL statement variables that had values bound to them using the
sqlite3_bind_*() API retain their values"

The behaviour we are seeing appears to contradict this statement. I've
attached a very simple example of searching for a few rows in a
database. To run the example, create a database (test.db) with the
following schema:

CREATE table test ( id INTEGER PRIMARY KEY, value INTEGER NOT NULL );
INSERT INTO test VALUES ( 1, 4 );
INSERT INTO test VALUES ( 2, 5 );
INSERT INTO test VALUES ( 3, 6 );

In the example code, notice that the call to sqlite3_reset() comes after
the call to sqlite3_bind_int64(). When running the example, it will
return the first row all three times. It seems that the call to
sqlite3_reset() actually resets the binded variable to the value it had
at the last call to sqlite3_step().

Is this a bug? Or is it intended behaviour? If it's intended, I
recommend updating the documentation to be clear about this behaviour.

Thanks for any insight anyone can provide.

Joe

_______________________________________________
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: sqlite3_reset() bug?

Josef Barnes
It doesn't look like my attachment worked, so here is the code:

------------------------------------------------------------

#include <stdio.h>
#include <sqlite3.h>

int
main (void)
{
    int            i;
    sqlite3_stmt  *stmt = NULL;
    sqlite3       *db = NULL;

    int vals[] = { 4, 5, 6 };

    sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READONLY, NULL);
    sqlite3_prepare_v2(db, "SELECT id, value FROM test WHERE value = ?", -1, &stmt, NULL);

    for (i = 0; i < 3; i++) {
       sqlite3_bind_int64(stmt, 1, vals[i]);
       printf("BOUND %d\n", vals[i]);
       sqlite3_reset(stmt);
       while (sqlite3_step(stmt) == SQLITE_ROW) {
          printf("FOUND %lld,%lld\n", sqlite3_column_int64(stmt, 0), sqlite3_column_int64(stmt, 1));
       }
    }

    sqlite3_finalize(stmt);
    sqlite3_close_v2(db);

    return 0;
}

------------------------------------------------------------

On 5/6/19 11:04 pm, Josef Barnes wrote:

> Hi,
>
> We've come across a situation where we think there is a bug in the
> sqlite3_reset() function. The documentation states:
>
> "Any SQL statement variables that had values bound to them using the
> sqlite3_bind_*() API retain their values"
>
> The behaviour we are seeing appears to contradict this statement. I've
> attached a very simple example of searching for a few rows in a
> database. To run the example, create a database (test.db) with the
> following schema:
>
> CREATE table test ( id INTEGER PRIMARY KEY, value INTEGER NOT NULL );
> INSERT INTO test VALUES ( 1, 4 );
> INSERT INTO test VALUES ( 2, 5 );
> INSERT INTO test VALUES ( 3, 6 );
>
> In the example code, notice that the call to sqlite3_reset() comes
> after the call to sqlite3_bind_int64(). When running the example, it
> will return the first row all three times. It seems that the call to
> sqlite3_reset() actually resets the binded variable to the value it
> had at the last call to sqlite3_step().
>
> Is this a bug? Or is it intended behaviour? If it's intended, I
> recommend updating the documentation to be clear about this behaviour.
>
> Thanks for any insight anyone can provide.
>
> Joe
>
> _______________________________________________
> 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: sqlite3_reset() bug?

Shawn Wagner
If you tweak that to include some error checking:

    if (sqlite3_bind_int64(stmt, 1, vals[i]) != SQLITE_OK) {
      fprintf(stderr, "BIND ERROR: %s\n", sqlite3_errmsg(db));
    }

You'll see output like:

BOUND 4
FOUND 1,4
BIND ERROR: bad parameter or other API misuse
BOUND 5
FOUND 1,4
BIND ERROR: bad parameter or other API misuse
BOUND 6
FOUND 1,4

From https://www.sqlite.org/c3ref/bind_blob.html:

If any of the sqlite3_bind_*() routines are called with a NULL pointer for
the prepared statement or with a prepared statement for which
sqlite3_step() has been called more recently than sqlite3_reset(), then the
call will return SQLITE_MISUSE.

You're running into the latter situation.


On Wed, Jun 5, 2019 at 6:07 AM Josef Barnes <[hidden email]> wrote:

> It doesn't look like my attachment worked, so here is the code:
>
> ------------------------------------------------------------
>
> #include <stdio.h>
> #include <sqlite3.h>
>
> int
> main (void)
> {
>     int            i;
>     sqlite3_stmt  *stmt = NULL;
>     sqlite3       *db = NULL;
>
>     int vals[] = { 4, 5, 6 };
>
>     sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READONLY, NULL);
>     sqlite3_prepare_v2(db, "SELECT id, value FROM test WHERE value = ?",
> -1, &stmt, NULL);
>
>     for (i = 0; i < 3; i++) {
>        sqlite3_bind_int64(stmt, 1, vals[i]);
>        printf("BOUND %d\n", vals[i]);
>        sqlite3_reset(stmt);
>        while (sqlite3_step(stmt) == SQLITE_ROW) {
>           printf("FOUND %lld,%lld\n", sqlite3_column_int64(stmt, 0),
> sqlite3_column_int64(stmt, 1));
>        }
>     }
>
>     sqlite3_finalize(stmt);
>     sqlite3_close_v2(db);
>
>     return 0;
> }
>
> ------------------------------------------------------------
>
> On 5/6/19 11:04 pm, Josef Barnes wrote:
> > Hi,
> >
> > We've come across a situation where we think there is a bug in the
> > sqlite3_reset() function. The documentation states:
> >
> > "Any SQL statement variables that had values bound to them using the
> > sqlite3_bind_*() API retain their values"
> >
> > The behaviour we are seeing appears to contradict this statement. I've
> > attached a very simple example of searching for a few rows in a
> > database. To run the example, create a database (test.db) with the
> > following schema:
> >
> > CREATE table test ( id INTEGER PRIMARY KEY, value INTEGER NOT NULL );
> > INSERT INTO test VALUES ( 1, 4 );
> > INSERT INTO test VALUES ( 2, 5 );
> > INSERT INTO test VALUES ( 3, 6 );
> >
> > In the example code, notice that the call to sqlite3_reset() comes
> > after the call to sqlite3_bind_int64(). When running the example, it
> > will return the first row all three times. It seems that the call to
> > sqlite3_reset() actually resets the binded variable to the value it
> > had at the last call to sqlite3_step().
> >
> > Is this a bug? Or is it intended behaviour? If it's intended, I
> > recommend updating the documentation to be clear about this behaviour.
> >
> > Thanks for any insight anyone can provide.
> >
> > Joe
> >
> > _______________________________________________
> > 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: sqlite3_reset() bug?

Josef Barnes
Thank you!!
You've saved our sanity for today! Perhaps I should RTFM a bit more thoroughly next time.
We usually include plenty of error checking, but do get lazy with the bind() ones. Won't happen again!

Joe

⁣Sent from BlueMail ​

On 5 Jun. 2019, 23:26, at 23:26, Shawn Wagner <[hidden email]> wrote:

>If you tweak that to include some error checking:
>
>    if (sqlite3_bind_int64(stmt, 1, vals[i]) != SQLITE_OK) {
>      fprintf(stderr, "BIND ERROR: %s\n", sqlite3_errmsg(db));
>    }
>
>You'll see output like:
>
>BOUND 4
>FOUND 1,4
>BIND ERROR: bad parameter or other API misuse
>BOUND 5
>FOUND 1,4
>BIND ERROR: bad parameter or other API misuse
>BOUND 6
>FOUND 1,4
>
>From https://www.sqlite.org/c3ref/bind_blob.html:
>
>If any of the sqlite3_bind_*() routines are called with a NULL pointer
>for
>the prepared statement or with a prepared statement for which
>sqlite3_step() has been called more recently than sqlite3_reset(), then
>the
>call will return SQLITE_MISUSE.
>
>You're running into the latter situation.
>
>
>On Wed, Jun 5, 2019 at 6:07 AM Josef Barnes <[hidden email]> wrote:
>
>> It doesn't look like my attachment worked, so here is the code:
>>
>> ------------------------------------------------------------
>>
>> #include <stdio.h>
>> #include <sqlite3.h>
>>
>> int
>> main (void)
>> {
>>     int            i;
>>     sqlite3_stmt  *stmt = NULL;
>>     sqlite3       *db = NULL;
>>
>>     int vals[] = { 4, 5, 6 };
>>
>>     sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READONLY, NULL);
>>     sqlite3_prepare_v2(db, "SELECT id, value FROM test WHERE value =
>?",
>> -1, &stmt, NULL);
>>
>>     for (i = 0; i < 3; i++) {
>>        sqlite3_bind_int64(stmt, 1, vals[i]);
>>        printf("BOUND %d\n", vals[i]);
>>        sqlite3_reset(stmt);
>>        while (sqlite3_step(stmt) == SQLITE_ROW) {
>>           printf("FOUND %lld,%lld\n", sqlite3_column_int64(stmt, 0),
>> sqlite3_column_int64(stmt, 1));
>>        }
>>     }
>>
>>     sqlite3_finalize(stmt);
>>     sqlite3_close_v2(db);
>>
>>     return 0;
>> }
>>
>> ------------------------------------------------------------
>>
>> On 5/6/19 11:04 pm, Josef Barnes wrote:
>> > Hi,
>> >
>> > We've come across a situation where we think there is a bug in the
>> > sqlite3_reset() function. The documentation states:
>> >
>> > "Any SQL statement variables that had values bound to them using
>the
>> > sqlite3_bind_*() API retain their values"
>> >
>> > The behaviour we are seeing appears to contradict this statement.
>I've
>> > attached a very simple example of searching for a few rows in a
>> > database. To run the example, create a database (test.db) with the
>> > following schema:
>> >
>> > CREATE table test ( id INTEGER PRIMARY KEY, value INTEGER NOT NULL
>);
>> > INSERT INTO test VALUES ( 1, 4 );
>> > INSERT INTO test VALUES ( 2, 5 );
>> > INSERT INTO test VALUES ( 3, 6 );
>> >
>> > In the example code, notice that the call to sqlite3_reset() comes
>> > after the call to sqlite3_bind_int64(). When running the example,
>it
>> > will return the first row all three times. It seems that the call
>to
>> > sqlite3_reset() actually resets the binded variable to the value it
>> > had at the last call to sqlite3_step().
>> >
>> > Is this a bug? Or is it intended behaviour? If it's intended, I
>> > recommend updating the documentation to be clear about this
>behaviour.
>> >
>> > Thanks for any insight anyone can provide.
>> >
>> > Joe
>> >
>> > _______________________________________________
>> > 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
_______________________________________________
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: sqlite3_reset() bug?

Hick Gunter
In reply to this post by Josef Barnes
The sqlite3_reset() call is used to reset the statement AFTER having called sqlite3_step().

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Josef Barnes
Gesendet: Mittwoch, 05. Juni 2019 15:07
An: [hidden email]
Betreff: [EXTERNAL] Re: [sqlite] sqlite3_reset() bug?

It doesn't look like my attachment worked, so here is the code:

------------------------------------------------------------

#include <stdio.h>
#include <sqlite3.h>

int
main (void)
{
    int            i;
    sqlite3_stmt  *stmt = NULL;
    sqlite3       *db = NULL;

    int vals[] = { 4, 5, 6 };

    sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READONLY, NULL);
    sqlite3_prepare_v2(db, "SELECT id, value FROM test WHERE value = ?", -1, &stmt, NULL);

    for (i = 0; i < 3; i++) {
       sqlite3_bind_int64(stmt, 1, vals[i]);
       printf("BOUND %d\n", vals[i]);
       sqlite3_reset(stmt);
       while (sqlite3_step(stmt) == SQLITE_ROW) {
          printf("FOUND %lld,%lld\n", sqlite3_column_int64(stmt, 0), sqlite3_column_int64(stmt, 1));
       }
    }

    sqlite3_finalize(stmt);
    sqlite3_close_v2(db);

    return 0;
}

------------------------------------------------------------

On 5/6/19 11:04 pm, Josef Barnes wrote:

> Hi,
>
> We've come across a situation where we think there is a bug in the
> sqlite3_reset() function. The documentation states:
>
> "Any SQL statement variables that had values bound to them using the
> sqlite3_bind_*() API retain their values"
>
> The behaviour we are seeing appears to contradict this statement. I've
> attached a very simple example of searching for a few rows in a
> database. To run the example, create a database (test.db) with the
> following schema:
>
> CREATE table test ( id INTEGER PRIMARY KEY, value INTEGER NOT NULL );
> INSERT INTO test VALUES ( 1, 4 ); INSERT INTO test VALUES ( 2, 5 );
> INSERT INTO test VALUES ( 3, 6 );
>
> In the example code, notice that the call to sqlite3_reset() comes
> after the call to sqlite3_bind_int64(). When running the example, it
> will return the first row all three times. It seems that the call to
> sqlite3_reset() actually resets the binded variable to the value it
> had at the last call to sqlite3_step().
>
> Is this a bug? Or is it intended behaviour? If it's intended, I
> recommend updating the documentation to be clear about this behaviour.
>
> Thanks for any insight anyone can provide.
>
> Joe
>
> _______________________________________________
> 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