I have a query that should return results and sqlite3_step is, sometimes,
returning SQLITE_DONE instead of SQLITE_ROW.
I have a table, meta_data, with two columns
id INTEGER primary key, Soundid INTEGER
which I know at the time of execution has the rows
ID SOUNDID
38 39
39 40
I can do the query at the console and get the right results.
The code is fairly simple,
FileIsInMeta(int trackIndex)
{
bool debugMe = false;
if(trackIndex == 40 || trackIndex == 39)
{
debugMe = true;
}
int rc;
sqlite3_stmt *pStmt;
const char *pzTail= "";
int alreadyInMeta = 0;
string selectSql = "SELECT id FROM meta_data WHERE soundid =
"+itos(trackIndex)+"";
rc = sqlite3_prepare_v2(db,selectSql.c_str(),-1, ,);
if(rc == SQLITE_OK)
{
while(sqlite3_step(pStmt) == SQLITE_ROW)
{
const char *result = (const char *)sqlite3_column_text(pStmt, 0);
alreadyInMeta = atoi(result);
(debugMe is just somewhere I could put a break point for debugging, itos()
converts an integer to a string)
When I pass in 39 it works correctly and returns 38 for id, but when I pass in
40 sqlite3_step() returns SQLITE_DONE. (The particular numbers aren't
important, I just include the details to show how the same query is being used
both times but I get different results.)
I'm really not sure what I'm doing wrong. I know it would be easier to help me
if I could provide complete code to generate the issue, but it is intermittant
(as above, the same query providing different results) and I'm not sure how to
reproduce it in a briefer way. The above is part of a very large project.
Although I say it's intermittant, it's also predictable.
The application first does an operation that adds 322 records to the table
meta_data, in addition to a table sounds in the same database.
Then in a subsequent operation, it adds several thousand records to meta_data
and sounds. The SQLITE_DONE always happens at the same point, where trackIndex
== 40. If I recreate the database and do the same operations in the same
order, the problem always happens at the same point. (It is not just the one
record where I get the problem though, there are several dozen, this is just
one example).
I know this isn't much to work with, but I am not sure where to look next.
Things I have tried so far
- Originally this occurred during a large transaction BEGIN/COMMIT section,
removed the transaction, no change
- Using a bind statement instead of a literal string for the query
- Calling sqlite3_reset() on statement before calling sqlite3_step()
- Added SQLITE_THREADSAFE=1 (This is running in an MFC worker thread, although
it's designed so that there is no simultaneous access to the database at the
time it's running; only the executing thread)
- Explicitly setting the maximum statement length, instead of using -1
- Checking to see if there was anything returned into pzTail (no)
- There is an index on meta_data.soundid (the index is just soundid), tried
removing that, same results
Most of that was just guesswork based on similar problems I saw searching the
archives for this mailing list.
I tried tracing into the SQLite code, to be honest I didn't know what I was
looking for and got lost. Does anybody know a likely place to look in the
source code for what is going wrong? Is there a way I could dereference a
sqlite3_stmt to see if anything looks wrong with it? Or a way to check that
the statement which it has is still correct or something like that?
Several thousand records are inserted into meta_data before it reaches this
point in the code, which is why I thought it might have something to do with
the transaction or the thread. But all the inserts should definitely be
happening from the same thread. Note though, there is the main application
thread, which creates the db connection, and the worker thread, which does the
heavy work. Neither of them are accessing the db at the same time, but they
are separate threads.
My best guess is it has something or other to do with that, somehow the state
of the table meta_data isn't what it should be at the time I do the query and
it has something to do with having inserted such a large number of records at
once. But, definitely the worker thread which did the inserts is the same one
doing the query that I am having problems with.
Anyway ... I know it's difficult to help me without me being able to provide a
complete reproducible example, but as I say this is part of a large complicated
application, and I'm not sure how I could do it without a huge amount of
dependant code; an XML parser, a routine that scans the user's hard drive for
records to add, etc.
Any ideas anybody could provide as to where to look next, or questions that it
would help if I answered, would be greatly appreciated. Sorry if I'm missing
something obvious.
--
Joe Cosby
http://joecosby.com/
They laughed at Einstein. They laughed at
