----- Original Message -----
From: "Paul Tomblin" <[EMAIL PROTECTED]>
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.
All you need to do is make sure you've reset() your row-selecting reader
before you execute the insert and then it'll work.
Robert
