Scott McDonald wrote:
Jef Driesen wrote:
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
I was getting similar results, for me I noticed this behavior with a
CONSTRAINT failure was getting a 19 return code but in my logging for this I
use the error code and error message API and was getting 1 and "SQL logic or
missing database" or something like that.
On the "sqlite_finalize" I would get a 19 return code and in my logging
would get a 19 error code and error message of "PRIMARY KEY must be unique"
- this is what I would expect after calling "sqlite_step" not after calling
"sqlite_finalize" - this is like the legacy behavior you mentioned, etc.
In the v2 interface, the return code is always reported immediately,
without the need to call sqlite3_reset or sqlite3_finalize. This is
explained in the documentation.
So far no problem, but I noticed the functions sqlite3_errcode and
sqlite3_errmsg are always behaving as they did with the legacy
interface. The correct error code and error message is only returned
after calling sqlite3_reset or sqlite3_finalize. But this is something
that is *NOT* mentioned in the documentation at all. As it turns out I'm
not the only one who finds this really confusing, so this should really
be added to the documentation.
I noticed you took it a couple of steps further and analyzed other possible
"prepared statement" errors in this area, nice work...
Just wondering if you got any resolution on this as it doesn't feel "clean"
to me - I would like my logging statements to actually provide some useful
information when the error occurs, etc.
You can get the useful error information from sqlite3_step after calling
sqlite3_reset (no matter which interface you used to prepare the statement).
But as I found out, this does not work for SQLITE_MISUSE (and maybe
others?). In this case I never get an appropriate error message (I
always get "not an error") and the error code is even lost after calling
sqlite3_reset! I'm not aware of a solution for this problem.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
