No I would not wrap the querries in a transaction. I think the problem you are encountering is due to thread interaction upon the sqlite structures. Since it was compiled with THREADsafety disabled.
If you have two threads that share the same connection. You need to compile with THREADSAFE turned on. Or provide your own mutexing such that neither thread interacts with the sqlite connection concurrently. Steps to take from here: 1. re compile with thread saftey turned on. 2. Each thread should create its own connection independently of the other. 3. Perform test for sqlite busy when starting transactions and the prepare and first calls to step (ie for selects). Hope that helps --- On Mon, 11/3/08, dbikash <[EMAIL PROTECTED]> wrote: > From: dbikash <[EMAIL PROTECTED]> > Subject: Re: [sqlite] "SQL logic error or missing database" with > multithreaded program > To: sqlite-users@sqlite.org > Date: Monday, November 3, 2008, 7:39 AM > Many Thanks Ken for your guidelines. > > Following your suggestions I found that indeed there was a > silly mistake in > the querying thread (I was using a 2-D array of queries, > and I missed a > comma :-( ). > > After correcting this mistake, the two threads ran fine > when run one at a > time. When run together, I did not get any error message in > the query > thread. However, I did receive the "SQL logic error or > missing database" > message (error code 1) during an insertion after a random > number of > successful insertions and queries. This was accompanied by > a seg fault. > > After many futile tries, I replaced the call to > sqlite_prepare by > sqlite_prepare_v2. And it seems to work! The program is > running for 10 > minutes now. > > What could have gone wrong with prepare? > > Another question, should I wrap each individual query (not > inserts) within a > transaction too? > > Thanks again for all the help. > dbikash > > > ken-33 wrote: > > > > I think you should consider running the program with > one thread at a time. > > > > This will help you isolate if this is a problem within > a thread or is an > > interaction among the threads. > > > > I'll assume its both. After you fix all of the > localized issues. > > > > I'd suggest recompiling sqlite with thread safe > turned on. > > > > Allow each thread to create its own connection. > > > > Then Wrap each transaction with a begin immediate. > That way you can > > acquire a lock when needed. Use a loop around the > begin immediate testing > > for success. If you fail with sqlite busy. Simply > sleep a short while > > (hint usleep) loop and try again. > > > > HTH > > Ken > > > > > > > > --- On Fri, 10/31/08, dbikash > <[EMAIL PROTECTED]> wrote: > > > >> From: dbikash <[EMAIL PROTECTED]> > >> Subject: Re: [sqlite] "SQL logic error or > missing database" with > >> multithreaded program > >> To: sqlite-users@sqlite.org > >> Date: Friday, October 31, 2008, 10:24 AM > >> I use both int sqlite3_errcode() and > sqlite3_errmsg(). Here > >> is what they > >> return (I get 3 different types of errors in > different > >> runs): > >> 1, SQL logic error or missing database. > >> 1, unrecognized token "" (there's a > non > >> printable character like a rectangle > >> within the quotes). > >> 1, SQL logic error or missing database, > sqlite3_get_table() > >> called with two > >> or more incompatible queries (this last print is > obtained > >> from zErrMsg > >> returned when the return value from > sqlite3_get_table() is > >> not SQLITE_OK) > >> > >> Also, the program is written to print an error and > then > >> continue in the > >> infinite loop. I can find a few errors in query > execution, > >> but it is after > >> an error in insertion (sqlite3_step() != > SQLITE_DONE) that > >> the program quits > >> by itself. > >> > >> > >> D. Richard Hipp wrote: > >> > > >> > > >> > On Oct 31, 2008, at 9:46 AM, dbikash wrote: > >> >> > >> >> I get a SQLite error 1 (SQL logic error > >> >> or missing database). What could be the > problem? > >> > > >> > Is that the error message text that SQLite > returns: > >> "SQL logic error > >> > or missing database"? Or is that just > the > >> meaning of SQLITE_ERROR > >> > that you looked up? What error message does > >> sqlite3_errmsg(db) return? > >> > > >> > D. Richard Hipp > >> > [EMAIL PROTECTED] > >> > > >> > > >> > > >> > > _______________________________________________ > >> > sqlite-users mailing list > >> > sqlite-users@sqlite.org > >> > > >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > >> > > >> > >> -- > >> View this message in context: > >> > http://www.nabble.com/%22SQL-logic-error-or-missing-database%22-with-multithreaded-program-tp20266281p20268213.html > >> Sent from the SQLite mailing list archive at > Nabble.com. > >> > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > View this message in context: > http://www.nabble.com/%22SQL-logic-error-or-missing-database%22-with-multithreaded-program-tp20266281p20302902.html > Sent from the SQLite mailing list archive at Nabble.com. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users