On 12.11.2007 23:31 CE(S)T, Perrin Harkins wrote:
> On Nov 12, 2007 5:24 PM, Yves Goergen <nospam.list@stripped> wrote:
>> Damn, I found out that I need table locking *and* transactions.
> What makes you say that?
SELECT MAX(id) FROM table
INSERT INTO table (id) VALUES (?)
INSERT INTO othertable (id) VALUES (?)
First I find a new id value, then I do several INSERTs that need to be
atomic, and especially roll back completely if a later one fails.
> That Perl module uses the exact technique I described to you with
> updates and LAST_INSERT_ID().
AUTO_INCREMENT isn't portable. Now I only "support" MySQL and SQLite.
But I also did PostgreSQL (until it failed one of the more complex
queries, maybe it comes back one day) and maybe Oracle or whatever will
be compatible, too, so that I then stand there with my AUTO_INCREMENT
and can't use it.
> Frankly, doing the insert and checking for an error seems like a
> pretty reasonable solution to me, since you only have two databases to
> care about at this point.
I wonder if I can safely use an error code to determine this error
condition and then just retry. Here's an interesting page:
> Error: 1022 SQLSTATE: 23000 (ER_DUP_KEY)
> Message: Can't write; duplicate key in table '%s'
No documentation for SQLite.
PostgreSQL uses several SQLSTATE codes for this situation.
Something must be wrong with SQL-92 because the two reference tables
have no common SQLSTATE values for related error conditions. But
generally I think that an SQLSTATE beginning with "23" is close enough
for a match.
Yves Goergen "LonelyPixel" <nospam.list@stripped>
Visit my web laboratory at http://beta.unclassified.de