in reply to Re: SQL Troubles
in thread SQL Troubles

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*

Replies are listed 'Best First'.
Re: Re: Re: SQL Troubles
by George_Sherston (Vicar) on Dec 01, 2001 at 17:27 UTC
    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]
        I agree it's pretty important! The sledgehammer / nut solution I used (first part of the post you reference) *does* solve the problem. I didn't try out crazyinsomniac's more elegant method, so I don't * know* that it works, but given the author I wd have high confidence in it. But I'd try it out before using it!

        § George Sherston
[reply]

In Section Seekers of Perl Wisdom