[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Dan Kennedy]

On 03/18/2015 02:39 AM, Bart Smissaert wrote:
> OK, let me try to explain:
>
> Simple table with primary integer key, call the field ID
>
> strSQL = "Insert into table1 values(?)"
>
> lReturnValue = sqlite3_prepare16_v2(lDBHandle,
>StrPtr(strSQL),
>Len(strSQL) * 2,
>lReturnStatementHandle,
>0)
>
> strSQL = "BEGIN TRANSACTION"
>
> lReturnValue = sqlite3_prepare16_v2(lDBHandle,
>StrPtr(strSQL),
>Len(strSQL) * 2,
>lReturnStatementHandle,
>0)
> lReturnValue = sqlite3_step(lStatementHandle)
> lReturnValue = sqlite3_finalize(lStatementHandle)
>
> In a loop, looping through rows of a VBA variant array:
>
> sqlite3_bind_int lStatementHandle, 1, vArray(r)
> sqlite3_step lStatementHandle
> sqlite3_reset lStatementHandle
>
> After this loop:
>
> lReturnValue = sqlite3_finalize(lStatementHandle)   < unexpected
> lReturnValue here
>
> strSQL = "COMMIT TRANSACTION"
>
> lReturnValue = sqlite3_prepare16_v2(lDBHandle,
>StrPtr(strSQL),
>Len(strSQL) * 2,
>lReturnStatementHandle,
>0)
> lReturnValue = sqlite3_step(lStatementHandle)
> lReturnValue = sqlite3_finalize(lStatementHandle)
>
>
>
> That is it.
> The result is fine and no duplicates are inserted, only thing wrong is the
> result the one from last sqlite3_finalize.
> Note that I don't check the return values in the loop, maybe I should, but
> it saves some time.

The last or second last sqlite3_finalize()?

If an error occurs in sqlite3_step(), then the following sqlite3_reset() 
or sqlite3_finalize() also returns an error. So you can write code like 
this:

   while( SQLITE_ROW==sqlite3_step(pStmt) ){
 // process row
   }
   rc = sqlite3_reset(pStmt);

and you are sure to catch any error. Replacing the sqlite3_reset() with 
finalize() would also catch any error code.

However, it is only the first sqlite3_reset() or sqlite3_finalize() 
following the error that also returns an error code. After that call has 
returned, the statement is back in "initial" state, so reset() or 
finalize() returns SQLITE_OK. So, if I do this:

   sqlite3_step(pStmt);
   rc = sqlite3_reset(pStmt);
   rc2 = sqlite3_finalize(pStmt);

and an error occurs in sqlite3_step(), then "rc" will be set to an error 
code, but "rc2" to SQLITE_OK. As by the time sqlite3_finalize() is 
called the statement is already back in "initial - no error has 
occurred" state.

Does that explain what you're seeing?

Dan.





>
>
> RBS
>
>
> On Tue, Mar 17, 2015 at 6:33 PM, Richard Hipp  wrote:
>
>> On 3/17/15, Bart Smissaert  wrote:
>>> Have a simple table with a primary integer key.
>>> Doing an insert (with no insert or ignore or insert or replace) with
>>> duplicate values
>>> for this primary integer key field produces zero on sqlite3_finalize, but
>>> 19 from sqlite3_errorcode.
>> I wrote a test program for this and I get 19 in both cases.  Maybe you
>> could share your test case with us?
>>
>>> I thought that the result value of sqlite3_finalize also should produce a
>>> non-zero value.
>>> This is with the latest version.
>>> Anything I am overlooking here or misunderstanding?
>>>
>>> RBS
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>> --
>> D. Richard Hipp
>> drh at sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Simon Slavin]

On 17 Mar 2015, at 8:12pm, Bart Smissaert  wrote:

> Will do some timings, but as you guess the slowdown may be negligible.

You know, on rereading the documentation for _reset() and _finalize() I can see 
why you thought you'd be able to check only the results for those calls.  
Unfortunately I don't think that's possible, and I have never seen code from an 
experienced SQLite user which depended on those calls echoing the results from 
_step().  So I thin you had better avoid it for now.

Simon.

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Bart Smissaert]

OK, thanks, I see now.
Will do some timings, but as you guess the slowdown may be negligible.

RBS

On Tue, Mar 17, 2015 at 8:08 PM, Simon Slavin  wrote:

>
> On 17 Mar 2015, at 8:00pm, Bart Smissaert 
> wrote:
>
> > OK, but I can find out from sqlite3_errcode after the loop if there was
> an
> > error, saving all the checks inside the loop.
> > Would there be any harm from that?
>
> From the documentation:
>
> 
>
> "If a prior API call failed but the most recent API call succeeded, the
> return value from sqlite3_errcode() is undefined."
>
> In other words, it's possible for one call within the loop to generate an
> error, then the next one to make it impossible to figure that out.
>
> I know that right now it looks like having an error accumulator function
> might be useful, but calls to sqlite3_errcode() execute extremely quickly
> because they just pick up a value which was previously set.  They won't
> slow your program down.  It takes longer to call sqlite3_errmsg() but you
> only need to do that if there's an error you hadn't predicted.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Simon Slavin]

On 17 Mar 2015, at 8:00pm, Bart Smissaert  wrote:

> OK, but I can find out from sqlite3_errcode after the loop if there was an
> error, saving all the checks inside the loop.
> Would there be any harm from that?

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Bart Smissaert]

OK, but I can find out from sqlite3_errcode after the loop if there was an
error, saving all the checks inside the loop.
Would there be any harm from that?

RBS

On Tue, Mar 17, 2015 at 7:52 PM, Simon Slavin  wrote:

>
> On 17 Mar 2015, at 7:39pm, Bart Smissaert 
> wrote:
>
> > The result is fine and no duplicates are inserted, only thing wrong is
> the
> > result the one from last sqlite3_finalize.
> > Note that I don't check the return values in the loop, maybe I should,
> but
> > it saves some time.
>
> The result you get from _finalize() is only guaranteed to tell you about
> problems with _finalize().  To find out if _step() worked you have to check
> the result from each call to _step().
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Simon Slavin]

On 17 Mar 2015, at 7:39pm, Bart Smissaert  wrote:

> The result is fine and no duplicates are inserted, only thing wrong is the
> result the one from last sqlite3_finalize.
> Note that I don't check the return values in the loop, maybe I should, but
> it saves some time.

The result you get from _finalize() is only guaranteed to tell you about 
problems with _finalize().  To find out if _step() worked you have to check the 
result from each call to _step().

Simon.

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Bart Smissaert]

OK, let me try to explain:

Simple table with primary integer key, call the field ID

strSQL = "Insert into table1 values(?)"

lReturnValue = sqlite3_prepare16_v2(lDBHandle,
  StrPtr(strSQL),
  Len(strSQL) * 2,
  lReturnStatementHandle,
  0)

strSQL = "BEGIN TRANSACTION"

lReturnValue = sqlite3_prepare16_v2(lDBHandle,
  StrPtr(strSQL),
  Len(strSQL) * 2,
  lReturnStatementHandle,
  0)
lReturnValue = sqlite3_step(lStatementHandle)
lReturnValue = sqlite3_finalize(lStatementHandle)

In a loop, looping through rows of a VBA variant array:

sqlite3_bind_int lStatementHandle, 1, vArray(r)
sqlite3_step lStatementHandle
sqlite3_reset lStatementHandle

After this loop:

lReturnValue = sqlite3_finalize(lStatementHandle)   < unexpected
lReturnValue here

strSQL = "COMMIT TRANSACTION"

lReturnValue = sqlite3_prepare16_v2(lDBHandle,
  StrPtr(strSQL),
  Len(strSQL) * 2,
  lReturnStatementHandle,
  0)
lReturnValue = sqlite3_step(lStatementHandle)
lReturnValue = sqlite3_finalize(lStatementHandle)



That is it.
The result is fine and no duplicates are inserted, only thing wrong is the
result the one from last sqlite3_finalize.
Note that I don't check the return values in the loop, maybe I should, but
it saves some time.


RBS


On Tue, Mar 17, 2015 at 6:33 PM, Richard Hipp  wrote:

> On 3/17/15, Bart Smissaert  wrote:
> > Have a simple table with a primary integer key.
> > Doing an insert (with no insert or ignore or insert or replace) with
> > duplicate values
> > for this primary integer key field produces zero on sqlite3_finalize, but
> > 19 from sqlite3_errorcode.
>
> I wrote a test program for this and I get 19 in both cases.  Maybe you
> could share your test case with us?
>
> > I thought that the result value of sqlite3_finalize also should produce a
> > non-zero value.
> > This is with the latest version.
> > Anything I am overlooking here or misunderstanding?
> >
> > RBS
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Hick Gunter]

Your calling sequence should be

- sqlite3_prepare() or sqlite3_prepare_v2()
- sqlite3_bind_xxx() if you have any parameters
- sqlite3_step()
- sqlite3_reset() or sqlite3_finalize() depending on if you want to run the 
statement again or not

You need to *check* the return status of *every* call.

Probably you are ignoring SQLITE_ERROR from your sqlite3_step() call.

-Urspr?ngliche Nachricht-
Von: Bart Smissaert [mailto:bart.smissaert at gmail.com]
Gesendet: Dienstag, 17. M?rz 2015 19:19
An: General Discussion of SQLite Database
Betreff: [sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces 
(rightly) SQLITE_CONSTRAINT

Have a simple table with a primary integer key.
Doing an insert (with no insert or ignore or insert or replace) with duplicate 
values for this primary integer key field produces zero on sqlite3_finalize, but
19 from sqlite3_errorcode.
I thought that the result value of sqlite3_finalize also should produce a 
non-zero value.
This is with the latest version.
Anything I am overlooking here or misunderstanding?

RBS
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Bart Smissaert]

Have a simple table with a primary integer key.
Doing an insert (with no insert or ignore or insert or replace) with
duplicate values
for this primary integer key field produces zero on sqlite3_finalize, but
19 from sqlite3_errorcode.
I thought that the result value of sqlite3_finalize also should produce a
non-zero value.
This is with the latest version.
Anything I am overlooking here or misunderstanding?

RBS

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Richard Hipp]

On 3/17/15, Bart Smissaert  wrote:
> Have a simple table with a primary integer key.
> Doing an insert (with no insert or ignore or insert or replace) with
> duplicate values
> for this primary integer key field produces zero on sqlite3_finalize, but
> 19 from sqlite3_errorcode.

I wrote a test program for this and I get 19 in both cases.  Maybe you
could share your test case with us?

> I thought that the result value of sqlite3_finalize also should produce a
> non-zero value.
> This is with the latest version.
> Anything I am overlooking here or misunderstanding?
>
> RBS
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org