Re: [sqlite] sqlite3_reset() bug?
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 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 wrote:
>
>> It doesn't look like my attachment worked, so here is the code:
>>
>>
>>
>> #include
>> #include
>>
>> int
>> main (void)
>> {
>> inti;
>> 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
>> > sqlite-users@mailinglists.sqlite.org
>> >
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_reset() bug?
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 wrote:
> It doesn't look like my attachment worked, so here is the code:
>
>
>
> #include
> #include
>
> int
> main (void)
> {
> inti;
> 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
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_reset() bug?
It doesn't look like my attachment worked, so here is the code:
#include
#include
int
main (void)
{
inti;
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_reset() bug?
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_reset and sqlite3_clear_bindings
> I can’t think of any cases where these would happen if everything was working as planned. I can't think of anything either and this is all fully tested, so I think I can leave these checks out. > From what I can see, you’re executing a long series of queries, each of which you expect to return zero or one row. No, this is moving data from a 2D variant array to a SQLite table, so these are inserts with parameters. RBS On Sun, May 28, 2017 at 4:37 PM, Simon Slavin wrote: > > On 28 May 2017, at 12:14pm, Gwendal Roué wrote: > > > I personnally call sqlite3_reset before sqlite3_clear_bingings with > great success, but I don't know if the order is relevant or not. > > It makes more sense to move sqlite3_clear_bindings() to before you set > parameters individually, including before the first time you call > sqlite3_step(). There’s no need to call it just before > sqlite3_finalize(). In fact, if you’re sure your code sets all the > parameters in the statement there’s no need to call it at all. > > On 28 May 2017, at 12:24pm, Bart Smissaert > wrote: > > > If there was a successful sqlite3_step just preceding it could a > > sqlite3_reset possibly be unsuccessful? > > If there was a successful sqlite3_reset just preceding it could a > > sqlite3_clear_bindings possibly be unsuccessful? > > I can’t think of any cases where these would happen if everything was > working as planned. They might happen if something was wrong with your > setup. For instance, some buggy part of your program or OS might write > over SQLite’s statement record. > > But if you have code which has been thoroughly tested, and if timing or > program space is so critical to you that checking an int to see if it’s > SQLITE_OK takes too long, then yes, you might leave the check out. But a > single check for an integer’s exact value doesn’t take long. > > From what I can see, you’re executing a long series of queries, each of > which you expect to return zero or one row. After each result you don’t > need any other values which might result, so you execute sqlite3_reset(). > There’s no problem with this, and it’s a standard way of operating. > > sqlite3_clear_bindings() is just a quick way of making sure all parameters > have legal (if not useful) values. If you’re immediately going to set all > the parameters yourself, then there’s no need to do it. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_reset and sqlite3_clear_bindings
On 28 May 2017, at 12:14pm, Gwendal Roué wrote: > I personnally call sqlite3_reset before sqlite3_clear_bingings with great > success, but I don't know if the order is relevant or not. It makes more sense to move sqlite3_clear_bindings() to before you set parameters individually, including before the first time you call sqlite3_step(). There’s no need to call it just before sqlite3_finalize(). In fact, if you’re sure your code sets all the parameters in the statement there’s no need to call it at all. On 28 May 2017, at 12:24pm, Bart Smissaert wrote: > If there was a successful sqlite3_step just preceding it could a > sqlite3_reset possibly be unsuccessful? > If there was a successful sqlite3_reset just preceding it could a > sqlite3_clear_bindings possibly be unsuccessful? I can’t think of any cases where these would happen if everything was working as planned. They might happen if something was wrong with your setup. For instance, some buggy part of your program or OS might write over SQLite’s statement record. But if you have code which has been thoroughly tested, and if timing or program space is so critical to you that checking an int to see if it’s SQLITE_OK takes too long, then yes, you might leave the check out. But a single check for an integer’s exact value doesn’t take long. From what I can see, you’re executing a long series of queries, each of which you expect to return zero or one row. After each result you don’t need any other values which might result, so you execute sqlite3_reset(). There’s no problem with this, and it’s a standard way of operating. sqlite3_clear_bindings() is just a quick way of making sure all parameters have legal (if not useful) values. If you’re immediately going to set all the parameters yourself, then there’s no need to do it. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_reset and sqlite3_clear_bindings
The documentation (https://www.sqlite.org/c3ref/reset.html) seems to suggest that sqlite3_reset can only be unsuccessful if sqlite3_step returned an error. Not sure about sqlite3_clear_bindings. I will do some testing and see if there is any performance gain in leaving these checks out. Very likely there is no relevant gain. RBS On Sun, May 28, 2017 at 12:28 PM, Gwendal Roué wrote: > > > Le 28 mai 2017 à 13:24, Bart Smissaert a > écrit : > > > >> Calling sqlite3_clear_bindings does the same thing as calling > > sqlite3_bind_null for all arguments. > > > > Yes, I understand that, just thinking about efficiency. > > Then I don't know. Your experience will tell. > > >> I personnally call sqlite3_reset before sqlite3_clear_bingings with > great > > success > > > > I am doing the same now. Probably no difference there > > I suppose so. > > >> is there any point *not* checking a result code whenever you are given > > the opportunity to? > > > > Yes, there is if there is no possible way in that particular situation > that > > the result could be other than success. > > If there was a successful sqlite3_step just preceding it could a > > sqlite3_reset possibly be unsuccessful? > > If there was a successful sqlite3_reset just preceding it could a > > sqlite3_clear_bindings possibly be unsuccessful? > > The documentation is your reference. If the documentation does not answer > your questions, then you shouldn't assume anything, and take the only > reasonable decision: check for errors whenever you can. > > Gwendal > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_reset and sqlite3_clear_bindings
> Le 28 mai 2017 à 13:24, Bart Smissaert a écrit : > >> Calling sqlite3_clear_bindings does the same thing as calling > sqlite3_bind_null for all arguments. > > Yes, I understand that, just thinking about efficiency. Then I don't know. Your experience will tell. >> I personnally call sqlite3_reset before sqlite3_clear_bingings with great > success > > I am doing the same now. Probably no difference there I suppose so. >> is there any point *not* checking a result code whenever you are given > the opportunity to? > > Yes, there is if there is no possible way in that particular situation that > the result could be other than success. > If there was a successful sqlite3_step just preceding it could a > sqlite3_reset possibly be unsuccessful? > If there was a successful sqlite3_reset just preceding it could a > sqlite3_clear_bindings possibly be unsuccessful? The documentation is your reference. If the documentation does not answer your questions, then you shouldn't assume anything, and take the only reasonable decision: check for errors whenever you can. Gwendal ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_reset and sqlite3_clear_bindings
> Calling sqlite3_clear_bindings does the same thing as calling sqlite3_bind_null for all arguments. Yes, I understand that, just thinking about efficiency. > I personnally call sqlite3_reset before sqlite3_clear_bingings with great success I am doing the same now. Probably no difference there > is there any point *not* checking a result code whenever you are given the opportunity to? Yes, there is if there is no possible way in that particular situation that the result could be other than success. If there was a successful sqlite3_step just preceding it could a sqlite3_reset possibly be unsuccessful? If there was a successful sqlite3_reset just preceding it could a sqlite3_clear_bindings possibly be unsuccessful? RBS On Sun, May 28, 2017 at 12:14 PM, Gwendal Roué wrote: > Hello Bart, > > > Le 28 mai 2017 à 13:03, Bart Smissaert a > écrit : > > > > Using SQLite3 3.19.0 on a Windows machine. > > I have some general questions about sqlite3_reset and > > sqlite3_clear_bindings: > > I am processing data from a 2D variant array (this is VB6). > > > > 1. I understand that after processing a row (binding all the values in a > > row of that variant array) > > I need to do either sqlite3_clear_bindings or make sure the next row has > > all the values bound > > either with a value (eg with sqlite3_bind_int) or with sqlite3_bind_null. > > Now if I am sure that there are always values to be bound (so I will > never > > need sqlite3_bind_null) then I don't need sqlite3_clear_bindings. If > there > > are empty array elements however then I could either do sqlite3_bind_null > > or always do sqlite3_clear_bindings after processing a row. > > In general what would be the most efficient approach? I suppose the only > > way to find out is testing, but maybe somebody can shred some light on > this. > > You are responsible for binding correct values before executing any > statement. Calling sqlite3_clear_bindings does the same thing as calling > sqlite3_bind_null for all arguments. > > > 2. Is there any difference in the order of doing sqlite3_reset and > > sqlite3_clear_bindings? > > I personnally call sqlite3_reset before sqlite3_clear_bingings with great > success, but I don't know if the order is relevant or not. > > > 3. Is there any point in checking the return value of > > sqlite3_clear_bindings, especially if it was > > already preceded by a successful sqlite3_reset? > > > > 4. Is there any point in checking the return value of sqlite3_reset if > > there was a successful > > sqlite3_bind_XXX preceding it? > > 3, 4: is there any point *not* checking a result code whenever you are > given the opportunity to? > > Of course you have to check it. The two functions perform a different job, > and may fail for different reasons. For example, sqlite3_reset() will > return an error if a previous execution of the statement has returned an > error. I'm almost sure sqlite3_clear_bindings does not. > > Happy SQLite :-) > Gwendal > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_reset and sqlite3_clear_bindings
Hello Bart, > Le 28 mai 2017 à 13:03, Bart Smissaert a écrit : > > Using SQLite3 3.19.0 on a Windows machine. > I have some general questions about sqlite3_reset and > sqlite3_clear_bindings: > I am processing data from a 2D variant array (this is VB6). > > 1. I understand that after processing a row (binding all the values in a > row of that variant array) > I need to do either sqlite3_clear_bindings or make sure the next row has > all the values bound > either with a value (eg with sqlite3_bind_int) or with sqlite3_bind_null. > Now if I am sure that there are always values to be bound (so I will never > need sqlite3_bind_null) then I don't need sqlite3_clear_bindings. If there > are empty array elements however then I could either do sqlite3_bind_null > or always do sqlite3_clear_bindings after processing a row. > In general what would be the most efficient approach? I suppose the only > way to find out is testing, but maybe somebody can shred some light on this. You are responsible for binding correct values before executing any statement. Calling sqlite3_clear_bindings does the same thing as calling sqlite3_bind_null for all arguments. > 2. Is there any difference in the order of doing sqlite3_reset and > sqlite3_clear_bindings? I personnally call sqlite3_reset before sqlite3_clear_bingings with great success, but I don't know if the order is relevant or not. > 3. Is there any point in checking the return value of > sqlite3_clear_bindings, especially if it was > already preceded by a successful sqlite3_reset? > > 4. Is there any point in checking the return value of sqlite3_reset if > there was a successful > sqlite3_bind_XXX preceding it? 3, 4: is there any point *not* checking a result code whenever you are given the opportunity to? Of course you have to check it. The two functions perform a different job, and may fail for different reasons. For example, sqlite3_reset() will return an error if a previous execution of the statement has returned an error. I'm almost sure sqlite3_clear_bindings does not. Happy SQLite :-) Gwendal ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_reset and sqlite3_clear_bindings
Using SQLite3 3.19.0 on a Windows machine. I have some general questions about sqlite3_reset and sqlite3_clear_bindings: I am processing data from a 2D variant array (this is VB6). 1. I understand that after processing a row (binding all the values in a row of that variant array) I need to do either sqlite3_clear_bindings or make sure the next row has all the values bound either with a value (eg with sqlite3_bind_int) or with sqlite3_bind_null. Now if I am sure that there are always values to be bound (so I will never need sqlite3_bind_null) then I don't need sqlite3_clear_bindings. If there are empty array elements however then I could either do sqlite3_bind_null or always do sqlite3_clear_bindings after processing a row. In general what would be the most efficient approach? I suppose the only way to find out is testing, but maybe somebody can shred some light on this. 2. Is there any difference in the order of doing sqlite3_reset and sqlite3_clear_bindings? 3. Is there any point in checking the return value of sqlite3_clear_bindings, especially if it was already preceded by a successful sqlite3_reset? 4. Is there any point in checking the return value of sqlite3_reset if there was a successful sqlite3_bind_XXX preceding it? Thanks for any advice. RBS ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_reset nor sqlite3_clear_bindings are clearing the auxdata when using sqlite3_set_auxdata
Philip Van Hoof wrote: > When we use our custom SQLite function function_sparql_regex (lower in > this E-mail) together with bound values for the argvs of the function, > then sqlite3_reset nor sqlite3_clear_bindings are clearning the > auxdata. > > This makes it impossible to pass the regex as a sqlite3_bind_text (the > same regex would be used even if you'd pass a new regex string). You are supposed to handle cache invalidation yourself in the function. Store the original regex string together with its compiled form, compare the incoming regex with the cached one to see whether you can reuse the cached value or need to recompile. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_reset nor sqlite3_clear_bindings are clearing the auxdata when using sqlite3_set_auxdata
Hi there,
When we use our custom SQLite function function_sparql_regex (lower in
this E-mail) together with bound values for the argvs of the function,
then sqlite3_reset nor sqlite3_clear_bindings are clearning the auxdata.
This makes it impossible to pass the regex as a sqlite3_bind_text (the
same regex would be used even if you'd pass a new regex string).
The sqlite3_set_auxdata's destroy function ptr is called when you'd
sqlite3_finalize the stmt each time, of course. But then it's not
possible to cache our statements as efficiently as we are doing right
now.
Here's an example that reproduces it:
static sqlite *db = NULL;
static const *filename = "test.db";
static sqlite3_stmt *stmt = NULL;
static void
open_db (void)
{
sqlite3_open (filename, &db) != SQLITE_OK);
sqlite3_create_function (db, "SparqlRegex", 3, SQLITE_ANY,
priv, &function_sparql_regex,
NULL, NULL);
}
static void
exec_regex (char *regex, char *mod)
{
if (!stmt) {
sqlite3_prepare_v2 (db, "SELECT field FROM Table"
"WHERE ... AND ... "
"SparqlRegex (field, ?, ?)",
-1, &stmt, NULL);
} else {
sqlite3_reset (stmt);
sqlite3_clear_bindings (stmt);
}
sqlite3_bind_text (stmt, 0, regex, -1, SQLITE_TRANSIENT);
sqlite3_bind_text (stmt, 1, mod, -1, SQLITE_TRANSIENT);
...
sqlite3_step () ...
...
}
static void
app (void)
{
open_db ();
exec_regex (".*", "i");
exec_regex ("foo", "i");
}
-
static void
function_sparql_regex (sqlite3_context *context,
int argc,
sqlite3_value *argv[])
{
gboolean ret;
const gchar *text, *pattern, *flags;
GRegexCompileFlags regex_flags;
GRegex *regex;
if (argc != 3) {
sqlite3_result_error (context, “Invalid argument count”, -1);
return;
}
regex = sqlite3_get_auxdata (context, 1);
text = sqlite3_value_text (argv[0]);
flags = sqlite3_value_text (argv[2]);
if (regex == NULL) {
gchar *err_str;
GError *error = NULL;
pattern = sqlite3_value_text (argv[1]);
regex_flags = 0;
while (*flags) {
switch (*flags) {
case ’s’: regex_flags |= G_REGEX_DOTALL; break;
case ‘m’: regex_flags |= G_REGEX_MULTILINE; break;
case ‘i’: regex_flags |= G_REGEX_CASELESS; break;
case ‘x’: regex_flags |= G_REGEX_EXTENDED; break;
default:
err_str = g_strdup_printf (”Invalid SPARQL regex flag ‘%c’”, *flags);
sqlite3_result_error (context, err_str, -1);
g_free (err_str);
return;
}
flags++;
}
regex = g_regex_new (pattern, regex_flags, 0, &error);
if (error) {
sqlite3_result_error (context, error->message, error->code);
g_clear_error (&error);
return;
}
sqlite3_set_auxdata (context, 1, regex, (void (*) (void*)) g_regex_unref);
}
ret = g_regex_match (regex, text, 0, NULL);
sqlite3_result_int (context, ret);
return;
}
--
Philip Van Hoof
freelance software developer
Codeminded BVBA - http://codeminded.be
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_reset (or sqlite3_finalize) and error reporting?
I have some questions on the usage of sqlite3_reset (or
sqlite3_finalize) after sqlite3_step.
In the legacy interface I use sqlite3_reset after sqlite3_step to obtain
a more specific error code for SQLITE_ERROR (to be able to detect schema
errors and automatically reprepare the statement, like the v2 interface
does):
int myprepare(sqlite3* db, const char* sql, int nbytes, sqlite3_stmt**
stmt, const char** tail)
{
#ifdef USE_LEGACY
int rc = sqlite3_prepare (db, sql, nbytes, stmt, tail);
#else
int rc = sqlite3_prepare_v2 (db, sql, nbytes, stmt, tail);
#endif
if (rc != SQLITE_OK && *stmt != 0) {
sqlite3_finalize (*stmt);
*stmt = 0;
}
}
int mystep(sqlite3_stmt* stmt)
{
int rc = sqlite3_step (stmt);
#ifdef USE_LEGACY
if (rc == SQLITE_ERROR)
rc = sqlite3_reset (stmt);
#endif
return rc;
}
This works well, but now I also want to report an appropriate error
message to the user (by throwing an exception). But I'm having some
problems with that. In some cases, the correct errcode and errmsg (from
the sqlite3_errcode and sqlite3_errmsg functions) can be obtained
directly after calling sqlite3_step, but sometimes sqlite3_reset is
required because sqlite3_step only returns a generic error. My idea was
now to always use sqlite3_reset (see example results below):
int mystep(sqlite3_stmt* stmt)
{
int rc = sqlite3_step (stmt);
if (rc != SQLITE_DONE && rc != SQLITE_ROW)
rc = sqlite3_reset (stmt);
return rc;
}
This also makes my code behave the same for both the legacy and the v2
interface, since I have to use sqlite3_reset anyway in the legacy
interface (except for the few return codes that are reported directly).
This works well in most cases, but as you can see from the results
below, I can't get a correct error message for SQLITE_MISUSE. Now my
questions are:
1. How do I retrieve the errmsg for SQLITE_MISUSE? Are there any other
codes with this problem? Maybe it's worth adding a function to obtain
the errmsg from an error code (e.g. not only the most recent one).
2. Is it normal that sometimes the rc value is different from the
errcode (and its associated errmsg)? The documentation for
sqlite3_errcode seems to suggest that this should not happen.
Sample output (in the format "function: rc, errcode, errmsg") for a few
errors:
SQLITE_CONSTRAINT (legacy)
sqlite3_step: 1, 1, SQL logic error or missing database
sqlite3_reset: 19, 19, column is not unique
SQLITE_CONSTRAINT (v2)
sqlite3_step: 19, 1, SQL logic error or missing database
sqlite3_reset: 19, 19, column is not unique
SQLITE_BUSY (legacy and v2)
sqlite3_step: 5, 5, database is locked
sqlite3_reset: 5, 5, database is locked
SQLITE_MISUSE (legacy and v2)
sqlite3_step: 21, 0, not an error
sqlite3_reset: 0, 0, not an error
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Sqlite3_reset needed?
"CARTER-HITCHIN, David, FM" <[EMAIL PROTECTED]> wrote: > Hi Richard, > > > You should be in the habit of calling sqlite3_reset() on each query > > as soon as that query finishes. Otherwise the query *might* leave a > > read-lock on the database file and thus prevent subsequent write > > operations for working correctly. > > What about if one just uses sqlite3_exec ? The manual says that > sqlite3_reset > is only needed for SQL that was prepared. > sqlite3_exec calls sqlite3_reset and sqlite3_finalize automatically. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Sqlite3_reset needed?
Hi Richard, > You should be in the habit of calling sqlite3_reset() on each query > as soon as that query finishes. Otherwise the query *might* leave a > read-lock on the database file and thus prevent subsequent write > operations for working correctly. What about if one just uses sqlite3_exec ? The manual says that sqlite3_reset is only needed for SQL that was prepared. Thanks, David. -- --LongSig *** The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. Authorized and regulated by the Financial Services Authority This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent. Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by The Royal Bank of Scotland plc in this regard and the recipient should carry out such virus and other checks as it considers appropriate. Visit our websites at: http://www.rbos.com http://www.rbsmarkets.com
Re: [sqlite] Sqlite3_reset needed?
"Merijn Vandenabeele" <[EMAIL PROTECTED]> wrote: > Hi, > > I prepare a statement in my constructor's class. Later, when I need data > from that statement, I bind all variables and step trough the results. Is it > necessary to reset the statement when I need other data from that statement > or is this done automatically when I bind new variables? > Yes. sqlite3_reset() is always required to restart a query. You should be in the habit of calling sqlite3_reset() on each query as soon as that query finishes. Otherwise the query *might* leave a read-lock on the database file and thus prevent subsequent write operations for working correctly. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Sqlite3_reset needed?
Correct. Thanks for your help! Merijn -Oorspronkelijk bericht- Van: Martin Engelschalk [mailto:[EMAIL PROTECTED] Verzonden: donderdag 23 maart 2006 12:13 Aan: sqlite-users@sqlite.org Onderwerp: Re: [sqlite] Sqlite3_reset needed? Hi, if i understand you right, the bound variables occur in your where class. If you want to start stepping through your result set with newly bound variables, you have to reset the statement first. You can also reset the statement, put new values into the already bound variables and start stepping again. This will be faster. Hope this helps, Martin Merijn Vandenabeele schrieb: >Hi, > >I prepare a statement in my constructor's class. Later, when I need >data from that statement, I bind all variables and step trough the >results. Is it necessary to reset the statement when I need other data >from that statement or is this done automatically when I bind new variables? > >Best regards, >Merijn Vandenabeele > > >
Re: [sqlite] Sqlite3_reset needed?
Hi, if i understand you right, the bound variables occur in your where class. If you want to start stepping through your result set with newly bound variables, you have to reset the statement first. You can also reset the statement, put new values into the already bound variables and start stepping again. This will be faster. Hope this helps, Martin Merijn Vandenabeele schrieb: Hi, I prepare a statement in my constructor's class. Later, when I need data from that statement, I bind all variables and step trough the results. Is it necessary to reset the statement when I need other data from that statement or is this done automatically when I bind new variables? Best regards, Merijn Vandenabeele
[sqlite] Sqlite3_reset needed?
Hi, I prepare a statement in my constructor's class. Later, when I need data from that statement, I bind all variables and step trough the results. Is it necessary to reset the statement when I need other data from that statement or is this done automatically when I bind new variables? Best regards, Merijn Vandenabeele
[sqlite] sqlite3_reset
Hi! I have a couple of questions regarding the sqlite3_reset function. Does sqlite3_reset free all the locks on the database? Do I actually need to call sqlite3_reset directly after sqlite3_step is done to free database locks? Or does sqlite3_step free the locks automatically? Thanks in advance! Jan-Eric Duden
