in reply to DBI and primary keys

At least in MySQL, to retrieve the ID of the most recently inserted row, use LAST_INSERT_ID() ala: 
select LAST_INSERT_ID();

[download]

Replies are listed 'Best First'.
Re: Re: DBI and primary keys
by tadman (Prior) on Mar 28, 2001 at 00:17 UTC
    Or, futhermore:     my ($last_id) = $db->selectrow_array("SELECT LAST_INSERT_ID()");
[reply]
[d/l]
      But what if this is multiprocess where there might be two or three inserts that go in at (or close to) the same time? I might end up with the same "last row" for each of them?

      $ perldoc perldoc
[reply]
        It's not efficient, and it depends on your dataset, but what's to prevent you from doing a select for the primary key of the row that matches your data exactly?

        If there's duplication of your values, you might have to order by the key, descending, and limit the results to one row... but you already have the exact values you've just inserted.

[reply]
        I can only tell for Sybase ASE and mssql, select @@identity will give the identity-value for the last insert of your current session.
[reply]
[d/l]
        the @@identity variable is per user session. To be frank, I've never done anything DBI work in the environment you're describing. That being said, you can check @@spid for each of your processes before you embark on this dangerous path. Unique spids should indicate @@identity is really unique for each process. Duplicate spids, should they appear, would indicate that your concern is correct.
        And in any case, whatever you do with the advice is your own gig :) feel free to ignore.
[reply]
Re: Re: DBI and primary keys
by sutch (Curate) on Mar 28, 2001 at 00:18 UTC
    Knowing which database you are using would help. Also available, at least with mySQL:

    $dbh->{'mysql_insertid'};

    [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom