Re: [sqlite] [Bug] sqlite3_finalize() *DOES NOT* return most recent evaluation error code

[Dmitry Pashkevich]

Oh, now I see, thanks for the explanation.
By "next version of lsqlite3" do you mean next major version or next patch
release (0.9.2 presumably)? Looking forward to seeing that change.

In the meantime I just stopped relying on stmt:finalize() return code in
Lua and instead always use stmt:step() return code.


On Wed, Aug 21, 2013 at 7:33 PM, Doug Currie  wrote:

> > I'm unable to reproduce the problem using C.  Maybe it is in lsqlite3.
>
> Yes, lsqlite3 still uses the old sqlite3_prepare() API to maintain
> compatibility with some legacy systems. It is long past time that it should
> have changed to use sqlite3_prepare_v2().
>
> Running Richard's example with sqlite3_prepare_v2 changed to
> sqlite3_prepare gives this output:
>
> first step returns 101
> second step returns 1
> error message = SQL logic error or missing database
> finalize returns 19
>
> This doesn't match the output of lsqlite3 because the wrapper tries to be
> helpful, and when the second step fails, it calls sqlite_reset to get the
> error code. The equivalent C code is:
>
>
> #include 
> #include "sqlite3.h"
> int main(int argc, char **argv){
>   sqlite3 *db;
>   sqlite3_stmt *pStmt;
>   int rc;
>   sqlite3_open(":memory:", );
>   sqlite3_exec(db, "create table t(x unique);", 0, 0, 0);
>
>   //sqlite3_prepare_v2(db, "insert into t(x) values(?)", -1, , 0);
>   sqlite3_prepare(db, "insert into t(x) values(?)", -1, , 0);
>
>   sqlite3_bind_int(pStmt, 1, 123);
>   rc = sqlite3_step(pStmt);
>   printf("first step returns %d\n", rc);
>   sqlite3_reset(pStmt);
>   rc = sqlite3_step(pStmt);
>   printf("second step returns %d\n", rc);
>   printf("error message = %s\n", sqlite3_errmsg(db));
>
>
>   if (rc == SQLITE_ERROR)
>   {
> rc = sqlite3_reset(pStmt);
> printf("second step's reset returns %d\n", rc);
> printf("error message = %s\n", sqlite3_errmsg(db));
>   }
>
>   rc = sqlite3_finalize(pStmt);
>   printf("finalize returns %d\n", rc);
>   sqlite3_close(db);
>   return 0;
> }
>
>
>
> That prints
>
> first step returns 101
> second step returns 1
> error message = SQL logic error or missing database
> second step's reset returns 19
> error message = column x is not unique
> finalize returns 0
>
> which matches the output from the Lua script.
>
> The next version of lsqlite3 will use the recommended sqlite3_prepare_v2()
> API.
>
> e
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Dmitry Pashkevich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Bug] sqlite3_finalize() *DOES NOT* return most recent evaluation error code

[Doug Currie]

> I'm unable to reproduce the problem using C.  Maybe it is in lsqlite3.

Yes, lsqlite3 still uses the old sqlite3_prepare() API to maintain 
compatibility with some legacy systems. It is long past time that it should 
have changed to use sqlite3_prepare_v2().

Running Richard's example with sqlite3_prepare_v2 changed to sqlite3_prepare 
gives this output:

first step returns 101
second step returns 1
error message = SQL logic error or missing database
finalize returns 19

This doesn't match the output of lsqlite3 because the wrapper tries to be 
helpful, and when the second step fails, it calls sqlite_reset to get the error 
code. The equivalent C code is:


#include 
#include "sqlite3.h"
int main(int argc, char **argv){
  sqlite3 *db;
  sqlite3_stmt *pStmt;
  int rc;
  sqlite3_open(":memory:", );
  sqlite3_exec(db, "create table t(x unique);", 0, 0, 0);

  //sqlite3_prepare_v2(db, "insert into t(x) values(?)", -1, , 0);
  sqlite3_prepare(db, "insert into t(x) values(?)", -1, , 0);

  sqlite3_bind_int(pStmt, 1, 123);
  rc = sqlite3_step(pStmt);
  printf("first step returns %d\n", rc);
  sqlite3_reset(pStmt);
  rc = sqlite3_step(pStmt);
  printf("second step returns %d\n", rc);
  printf("error message = %s\n", sqlite3_errmsg(db));
  

  if (rc == SQLITE_ERROR)
  {
rc = sqlite3_reset(pStmt);
printf("second step's reset returns %d\n", rc);
printf("error message = %s\n", sqlite3_errmsg(db));
  }

  rc = sqlite3_finalize(pStmt);
  printf("finalize returns %d\n", rc);
  sqlite3_close(db);
  return 0;
}



That prints

first step returns 101
second step returns 1
error message = SQL logic error or missing database
second step's reset returns 19
error message = column x is not unique
finalize returns 0

which matches the output from the Lua script.

The next version of lsqlite3 will use the recommended sqlite3_prepare_v2() API.

e

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Bug] sqlite3_finalize() *DOES NOT* return most recent evaluation error code

[Richard Hipp]

On Wed, Aug 21, 2013 at 8:04 AM, Dmitry Pashkevich  wrote:

> I think I found a bug, tested with SQLite v3.7.15.2 and v3.7.9.
> Looks like sqlite3_finalize() always returns SQLITE_OK, even if most recent
> execution of prepared statement failed, which contradicts the
> documentation
> :
>
> If the most recent evaluation of the statement encountered no errors or if
> > the statement is never been evaluated, then sqlite3_finalize() returns
> > SQLITE_OK. If the most recent evaluation of statement S failed, then
> > sqlite3_finalize(S) returns the appropriate error code<
> http://www.sqlite.org/c3ref/c_abort.html>
> >  or extended error code<
> http://www.sqlite.org/c3ref/c_abort_rollback.html>
> > .
> >
>
> I'm using sqlite3 via lsqlite3  Lua wrapper
> library
> (so maybe the bug is there but I can't confirm).
>

I'm unable to reproduce the problem using C.  Maybe it is in lsqlite3.

My test case:

#include 
#include "sqlite3.h"
int main(int argc, char **argv){
  sqlite3 *db;
  sqlite3_stmt *pStmt;
  int rc;
  sqlite3_open(":memory:", );
  sqlite3_exec(db, "create table t(x unique);", 0, 0, 0);
  sqlite3_prepare_v2(db, "insert into t(x) values(?)", -1, , 0);
  sqlite3_bind_int(pStmt, 1, 123);
  rc = sqlite3_step(pStmt);
  printf("first step returns %d\n", rc);
  sqlite3_reset(pStmt);
  rc = sqlite3_step(pStmt);
  printf("second step returns %d\n", rc);
  printf("error message = %s\n", sqlite3_errmsg(db));
  rc = sqlite3_finalize(pStmt);
  printf("finalize returns %d\n", rc);
  sqlite3_close(db);
  return 0;
}

Output of the test program:

first step returns 101
second step returns 19
error message = column x is not unique
finalize returns 19

And this sort of thing is extensively checked in the SQLite test suites (
http://www.sqlite.org/testing.html), so it is difficult to imagine how
something like this could go unnoticed.

Hence, pending additional contrary evidence, I'm going to assume this is a
problem with the lua bindings.


>
> Here's some sample code:
>
> > require "lsqlite3"
> > db = sqlite3.open_memory()
> > =db
> sqlite database (0x238e858)
> > =db:exec("create table t(x unique)")  *-- create a table with a
> constraint
> *
> 0
> > stmt = db:prepare("insert into t(x) values(?)")
> > =stmt
> sqlite virtual machine (0x23a23e8)
> > =stmt:bind_values(123)
> 0
> > =stmt:step()
> 101
> > =sqlite3.DONE
> 101
> > =stmt:reset()
> 0
> > =stmt:step()  *-- intentionally execute statement with same values to
> violate the constraint*
> 19
> *> =sqlite3.CONSTRAINT  -- step() returns the expected error code*
> *19*
> > =db:errmsg()
> column x is not unique
> *> =stmt:finalize() -- finalize returns OK!*
> *0*
> *> =db:errmsg() -- error message is still persisted, though*
> *column x is not unique*
>
>
> Somebody please confirm this...
>
> --
> Dmitry Pashkevich
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [Bug] sqlite3_finalize() *DOES NOT* return most recent evaluation error code

[Dmitry Pashkevich]

I think I found a bug, tested with SQLite v3.7.15.2 and v3.7.9.
Looks like sqlite3_finalize() always returns SQLITE_OK, even if most recent
execution of prepared statement failed, which contradicts the
documentation
:

If the most recent evaluation of the statement encountered no errors or if
> the statement is never been evaluated, then sqlite3_finalize() returns
> SQLITE_OK. If the most recent evaluation of statement S failed, then
> sqlite3_finalize(S) returns the appropriate error 
> code
>  or extended error code
> .
>

I'm using sqlite3 via lsqlite3  Lua wrapper library
(so maybe the bug is there but I can't confirm).

Here's some sample code:

> require "lsqlite3"
> db = sqlite3.open_memory()
> =db
sqlite database (0x238e858)
> =db:exec("create table t(x unique)")  *-- create a table with a constraint
*
0
> stmt = db:prepare("insert into t(x) values(?)")
> =stmt
sqlite virtual machine (0x23a23e8)
> =stmt:bind_values(123)
0
> =stmt:step()
101
> =sqlite3.DONE
101
> =stmt:reset()
0
> =stmt:step()  *-- intentionally execute statement with same values to
violate the constraint*
19
*> =sqlite3.CONSTRAINT  -- step() returns the expected error code*
*19*
> =db:errmsg()
column x is not unique
*> =stmt:finalize() -- finalize returns OK!*
*0*
*> =db:errmsg() -- error message is still persisted, though*
*column x is not unique*


Somebody please confirm this...

-- 
Dmitry Pashkevich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users