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 <drh at sqlite.org> wrote:
>
>> On 3/17/15, Bart Smissaert <bart.smissaert at gmail.com> 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
