Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

DBI insert: Need to retrieve an autoincremented column from a table after an insert.

by SkipHuffman (Monk)
on Aug 09, 2008 at 16:01 UTC ( #703273=perlquestion: print w/replies, xml ) Need Help??

SkipHuffman has asked for the wisdom of the Perl Monks concerning the following question:

Basically I need to know the autoincremented "id" value from row that I am inserting into a table. How can I get this?

I have tried the "last_insert_id" function, but I really don't trust it. I could get into a race condition if a second row were inserted before I could retrieve the id from the row that I just inserted.

Here is what I have tried. Perhaps I just don't have last_insert_id working properly. (it is returning null at the moment.)

my $query; $query=qq(insert into MasterEpisode set \ ShowId ="$ShowID",\ title ="$p->{'ep_title'}",\ description ="$p->{'ep_description'}",\ uploaddate =localtime ); print p($query); $dbh->do("$query"); # I see a risk of a race condition here. I should see +if I can get the ID while I insert the line. my $episodeid=$dbh->last_insert_id(undef,undef,"MasterEpisode" +,undef); print p("Episode id is $episodeid.");

Ok, looks like I can make it work in MySQL by doing this

# $episodeid=$dbh->last_insert_id(undef,undef,'MasterEpisode', +undef); $sth = $dbh->prepare(qq(select LAST_INSERT_ID() from MasterEpi +sode)); $sth->execute(); @episodeid=$sth->fetchrow_array(); $episodeid=$episodeid[0]; print p("Episode id is $episodeid.");

But it looks ugly and non portable to me.

Skip

Replies are listed 'Best First'.
Re: DBI insert: Need to retrieve an autoincremented column from a table after an insert.
by Jenda (Abbot) on Aug 09, 2008 at 18:27 UTC

    I would be very surprised if you found a database where the last_insert_id is not session specific. That is you always get YOUR last inserted id. You can test it by a script that'd make two connections, insert one row by each connection and then looked at the last_insert_id() for each connection.

Re: DBI insert: Need to retrieve an autoincremented column from a table after an insert.
by graff (Chancellor) on Aug 10, 2008 at 05:04 UTC
    You said:
    I have tried the "last_insert_id" function, but I really don't trust it. I could get into a race condition if a second row were inserted before I could retrieve the id from the row that I just inserted.

    Based on the description of "last_insert_id" in the DBI man page, it seems clear that this function is no more portable than the notion of an auto-increment field itself. If portability to different DB servers is a serious design goal for you, then adapting to different servers will have to be handled either as an install-time config issue, or as a run-time branching issue for the code you are writing. One way or another, if you have to support DB servers that differ wrt autoincrement/sequence keys, you'll need to write distinct code for each distinct flavor, and there's no way around it.

    Apart from that, as pointed out above by Jenda, when the DB server and associated DBD module properly support last_insert_id, there's no reason to worry about race conditions, and it's easy enough to test for the proper support:

    #!/usr/bin/perl use strict; use DBI; my $db1 = DBI->connect( "DBI:mysql:database=test;host=localhost", "", +"" ); $db1->do( "DROP TABLE IF EXISTS testautoinc" ); $db1->do( "CREATE TABLE testautoinc (tkey int not null auto_increment +key,". "tval varchar(100))" ); my $db2 = DBI->connect( "DBI:mysql:database=test;host=localhost", "", +"" ); my $sth1 = $db1->prepare( "insert into testautoinc (tval) values (?)" +); my $sth2 = $db2->prepare( "insert into testautoinc (tval) values (?)" +); $sth1->execute( "foo on sth1" ); $sth2->execute( "bar on sth2" ); $sth1->finish; $sth2->finish; my ( $key2 ) = $db2->last_insert_id( undef, undef, undef, undef ); my ( $key1 ) = $db1->last_insert_id( undef, undef, undef, undef ); print "key1 is $key1, key2 is $key2\n"; my $data = $db1->selectall_arrayref( "select * from testautoinc" ); for my $row ( @$data ) { print join( "\t", @$row ), "\n"; }
    (update: inverted the order of the two "last_insert_id" calls, just to prove that calling order doesn't matter)

    For me (using mysql 5.0), the output of that script shows that last_insert_id does the right thing in terms of keeping the connections separate and returning the correct id value for each one.

    My inclination would be to go ahead and write distinct code for distinct servers (as the need arises), so that I don't slow down operations unnecessarily on the servers that don't pose any problems.

Re: DBI insert: Need to retrieve an autoincremented column from a table after an insert.
by rowdog (Curate) on Aug 09, 2008 at 17:32 UTC

    DBI has last_insert_id but be sure to read all the notes about portability and such. I'm no dba but I believe the dilemma is that there's no real standard for such a query.

    Edit: Oops, you already mentioned that. Sorry, I didn't read your post closely enough. I'm afraid you'll probably need to get into something more driver specific. As Anon mentioned, PostgreSQL has a native way to get an id, and so do some (most?) other databases.

Re: DBI insert: Need to retrieve an autoincremented column from a table after an insert.
by hangon (Deacon) on Aug 09, 2008 at 21:26 UTC

    One way to achieve database portability and avoid race conditions is to assign the id from a common source independent of the database, then insert the id along with your data. The example below (untested) of course is not OS portable and has some warts of its own.

    my $episodeid = nextId(); my $query=qq(INSERT INTO MasterEpisode SET episoideid = "$episodeid", ShowId = "$ShowID", title = "$p->{'ep_title'}", description = "$p->{'ep_description'}", uploaddate = "@{[localtime]}" ); sub nextId { open my $seq, "+<", "sequence.dat"; flock $seq, 2 my $id = <$seq>; $id++; print $seq $id; close $seq; return $id; }
      assign the id from a common source independent of the database

      I would want to keep the sequence ids in the database...one way to do that in a portable way is with DBIx::Sequence.

        I would want to keep the sequence ids in the database

        I agree, its one of the warts from using a separate file. Like the OP, I've never completely trusted last_insert_id either, and getting unique row id's is just not an easy thing to do portably. I wasn't aware of DBIx::Sequence, just tried it out and it seems to work as advertized. Thanks for the pointer.

Re: DBI insert: Need to retrieve an autoincremented column from a table after an insert.
by Anonymous Monk on Aug 09, 2008 at 16:11 UTC
    With postgresql you could do

    INSERT INTO table ... RETURNING id

      Thanks, but I prefer to remain driver independent.

      Skip
Re: DBI insert: Need to retrieve an autoincremented column from a table after an insert.
by injunjoel (Priest) on Aug 10, 2008 at 02:52 UTC
    Greetings,
    One idea, though probably not the most efficient, would be to create two prepared statements, one for insert and another for query.
    use DBI; #connection stuff #prepare your statement handles #assuming the values are unique enough... my $sth_ins = $dbh->prepare("INSERT INTO table_foo(col1,col2,col3) VAL +UES(?,?,?)"); #after the insert run the query on those same values. my $sth_sel = $dbh->prepare("SELECT id FROM table_foo WHERE col1=?, co +l2=?, col3=?"); #...later in the code... my $last_id; for(@some_data_of_yours){ $sth_ins->execute($_->{'col1'},$_->{'col2'},$_->{'col3'}); $sth_sel->execute($_->{'col1'},$_->{'col2'},$_->{'col3'}); $sth_sel->bind_columns(\$last_id); $sth_sel->fetch(); #do some stuff with your $last_id... }
    Seems like overkill but at least you can trust the results. Basically a "Read it back to me" strategy.
    Not sure how much load this will add to things but since the statements are prepared you get all of those benefits. You can also use the select statement as a pre-check to see if the data is already in the DB. Just a thought...

    -InjunJoel
    "I do not feel obliged to believe that the same God who endowed us with sense, reason and intellect has intended us to forego their use." -Galileo

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://703273]
Approved by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (9)
As of 2019-05-21 11:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you enjoy 3D movies?



    Results (132 votes). Check out past polls.

    Notices?
    • (Sep 10, 2018 at 22:53 UTC) Welcome new users!