On Mon, 2006-01-23 at 09:48 -0500, Paul Tomblin wrote: > Quoting Dan Kennedy ([EMAIL PROTECTED]): > > > - The second problem appears to be a problem with self-locking. I'm > > > inserting a "mapping" into a table. I have a query active to find ids > > > that require mapping, and then I try to find the current lowest > > > unused "mapping" value, and insert it. > > > > Right. You can't modify a table that is currently being scanned by > > a SELECT query. One way around this is to make a copy of the table > > in a TEMP table for the duration of the operation. > > But actually I'm not currently selecting from it. I prepare two > statements, one for query and one for insert. The query should only > return 0 or 1 rows. I do the query, then if it returns 0 rows, I do the > insert. I repeat this multiple times, using the same prepared statements > each time. Like I said, this never caused a problem when I did it in > MySQL or PostgreSQL. > > I realize that SQLite has to be simplied compared to full features RDBMS, > but it appears that either the SQLite library, or possibly the perl > wrapper, is somehow mistaking a prepared statement which has finished > executing the current query for one whose query is still open. > > I suppose the only way I'm going to know for sure is to try writing the > same sequence of events in C, and it's been so long I'm not sure my C > skills are up to it. >
I ran into this just yesterday. I have a SELECT query that uses a sub-query to find a value in another table in order to find out if a particular row has been INSERTED yet. I suspect what's happening is that SQLite thinks that the SELECT query may fetch more than one row and so is not considered finished. Interestingly, the column against which I'm performing the sub-SELECT equality query is defined with UNIQUE, so I assumed SQLite would DWIM. The only solution I found was to use LIMIT 1 in both the inner and outer SELECTs, or to invoke finish() against the statement handles. Looking at http://www.sqlite.org/optoverview.html I see that LIMIT is preventing the optimizer flattening the subquery, so finish() is probably a better solution. -- David P.C. Wollmann AIM & Yahoo!: converter42 | MSN Messenger: [EMAIL PROTECTED] PGP Fingerprint: 53C8 BF29 9AF0 EEE8 85DB 8D1C 14B1 023E 9079 CAD8 Get free PKCS client and server certificates at http://www.cacert.org/
