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, &pStmt,&pzTail); 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 the Wright Brothers. But they also laughed at Bozo the Clown. -- Carl Sagan :: Currently listening to Optimistic, 2000, by Radiohead, from "Kid A" _________________________________________________________________ Climb to the top of the charts! Play the word scramble challenge with star power. http://club.live.com/star_shuffle.aspx?icid=starshuffle_wlmailtextlink_jan ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------