On 12.11.2007 23:31 CE(S)T, Perrin Harkins wrote: > On Nov 12, 2007 5:24 PM, Yves Goergen <[EMAIL PROTECTED]> wrote: >> Damn, I found out that I need table locking *and* transactions. > > What makes you say that?
BEGIN TRANSACTION SELECT MAX(id) FROM table INSERT INTO table (id) VALUES (?) INSERT INTO othertable (id) VALUES (?) COMMIT 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: http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html > 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. (http://www.postgresql.org/docs/8.2/interactive/errcodes-appendix.html) 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" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
