in reply to SQL Troubles

Blakem is right (no surprises there) about LAST_INSERT_ID. If I read you right, though, your (reasonable) concern is that between making the new db entry and getting the ID, somebody else makes a new entry and you end up getting the wrong ID. This is something I asked about a while back, and this thread has some interesting discussion. NB that if, as I am told, more recent versions of MYSQL support transactions, then there is a more elegant way to solve the problem than the one I chose, all that does work.

§ George Sherston

Replies are listed 'Best First'.
Re: Re: SQL Troubles
by growlf (Pilgrim) on Dec 01, 2001 at 16:40 UTC
    Also note that (from the mysql pages):

    The most recently generated ID is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0).

    i.e.

    INSERT INTO foo (auto,text)
    VALUES(NULL,'text');              # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
    VALUES(LAST_INSERT_ID(),'text');  # use ID in second table

    [download]


    *G*
[reply]
[d/l]
      That's interesting. If that means what it sounds like it means, I'm at a loss to explain the results of my experiment, where I ran the same script twice, and made the first run hang after it had written the db entry but before it got the id... then it restarts after the other run has written *its* db line, and the first run ends up getting the id created by the second run. It's more an academic point for me now, but I'd still be curious to know what was going on.

      § George Sherston
[reply]
        But did you not find that by using the $dbh, that the connection id was unique in the last post of that thread, and that this does prevent loosing the correct last_insert_id?

        I think this is a very important thread, and I for one hope that I have not been badly mistaken in a few apps that I have written that use this technique. *cringes*

        *G*

[reply]

In Section Seekers of Perl Wisdom