The query that is failing is fairly simple: sprintf( strStmt, "select count(*) from sqlite_master where type = 'table' and tbl_name = '%s'", tableName);
I have checked and there are no "loose" queries hanging around and furthermore this is the first query that is executed by the program; To explain in a nutshell what is happening: 1. Two processes gets started at the same time 2. Both try to initialize the database, creating tables filling them with data etc. 3. Only one succeeds into getting into the init code path while the other waits for it to complete. Both have opened connections to the database at this point. 4. Once the program doing the init is finished the second process's very first query fails with SQLITE_SCHEMA That very first query checks if the control table exists so that it can either go query it to see what has been initialized already or create it himself and proceed with initialization. The processes communicate through it. So it seems that both processes creates a database handle and then one proceeds to fill the database with tables while the other waits and then once the first initialization is complete the second process gets these SQLITE_SCHEMA errors. I hope that answeres your questions. Dan Kennedy-4 wrote: > > On Thu, 2007-04-05 at 05:37 -0700, pompomJuice wrote: >> Yes this is with the 3.3.14 code. I initially got the problem with the >> 3.3.12 >> code so I just upgraded to the 3.3.14 code but it behaves exactly the >> same. >> >> Just to recap, my proposed fix was >> //--- >> while( rc == SQLITE_SCHEMA ) >> rc = prepare_v2; >> // Some generic rc error checking here for prepare_v2 >> rc = step; >> if( rc == SQLITE_SCHEMA ) >> finalize; >> continue; >> end; >> // Some generic error checking for the step!=SQLITE_SCHEMA >> >> You proposed: >> >> //--- >> rc = prepare_v2; >> // Some generic rc error checking here for prepare_v2 >> while( rc == SQLITE_SCHEMA ) >> rc = step; >> if( rc == SQLITE_SCHEMA ) >> reset; >> continue; >> end; >> // Some generic error checking for the step!=SQLITE_SCHEMA >> >> The proposed method works and I will change mine to work like that. Still >> is >> this the way it is done or am I missing something. I thought step is >> supposed to handle SQLITE_SCHEMA errors automatically. > > Right, with prepare_v2(), the step() *should* have automatically > re-prepared the statement and continued. And this is what happened > after the reset() - the next time step() was called the statement > was re-prepared() under the hood. > > You should not have to do the reset() - this seems like a bug. I > only meant to suggest it for testing. > > What is the SQL statement that the step() is failing for? And are > there any other SQL statements from the same handle active at the > time? Definition of active: have called step() but not finalize() > or reset(). > > Thanks, > > Dan. > > >> Regards, >> Werner >> >> >> >> >> Dan Kennedy-4 wrote: >> > >> > On Thu, 2007-04-05 at 04:04 -0700, pompomJuice wrote: >> >> Ok. >> >> >> >> I went and re-prepared the statement anyway even though the >> documentation >> >> says it won't work. This trick only works if you finalize the failed >> >> statement after the step command. Otherwhise you keep on getting >> >> SQLITE_SCHEMA errors which might cause and endless loop if so >> >> implemented. >> >> >> >> Interesting. >> > >> > Is this with 3.3.14 code? Also, do you know if there are any other >> > statements created with the same db-handle that have been >> > sqlite3_step()'d but not sqlite3_finalized()'d or sqlite3_reset()'d? >> > >> > Finally, instead of re-prepairing the busted statement, what happens >> > if you sqlite3_reset() it and then try to run it again? >> > >> > Dan. >> > >> > >> >> >> >> pompomJuice wrote: >> >> > >> >> > Hello. >> >> > >> >> > I recently rewrote most of my SQLite wrappers to now ignore SCHEMA >> >> errors, >> >> > as these are now automagically handled by the new sqlite3_prepare_v2 >> >> API. >> >> > The logic changes were simple so I did not bother to test it and >> >> continued >> >> > with development. Now that development is complete and testing >> started >> >> > again I am getting the same old schema errors I use to get with the >> >> > deprecated prepare API. Only now I can’t get rid of them as >> >> re-prepairing >> >> > with v2 won't make the problem go away. >> >> > >> >> > I am therefore inclined to think that I am trying to use SQLite in a >> >> way >> >> > that is not permitted. Basically what I have is one .db file that is >> >> being >> >> > accessed by multiple binaries. If one binary drops or adds >> >> tables/indexes >> >> > the other binary fails with the schema error. There is no way that I >> >> can >> >> > rather use one binary with multiple threads. >> >> > >> >> > So my question is. Can SQLite recover from schema changes caused by >> >> other >> >> > processes or do I need to revert to the deprecated prepare API? >> >> > >> >> > Many thanks for any help in this regards. >> >> > >> >> > Werner. >> >> > >> >> > >> >> > >> >> > >> >> >> > >> > >> > >> ----------------------------------------------------------------------------- >> > To unsubscribe, send email to [EMAIL PROTECTED] >> > >> ----------------------------------------------------------------------------- >> > >> > >> > >> > > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > > -- View this message in context: http://www.nabble.com/sqlite3_prepare_v2-schema-error-Fatal%2C-need-help-please.-tf3530348.html#a9857158 Sent from the SQLite mailing list archive at Nabble.com. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
