You could loop thru the results of $sth->{TYPE} and look for something with AUTO_UNIQUE_VALUE. (you would get the integer equiv to AUTO_UNIQUE_VALUE from the type_info* methods)

Immediately after the insert, execute a SELECT LAST_INSERT_ID() as if it were a normal select statement in DBI. The value you retreive will be the auto_increment column value of your recent insert.

This is covered in the DuBois book (pp. 93, 170, 547) and in the MySQL online docs under 'Miscellaneous Functions'.

(Note that mysql_insert_id() is part of the C API and is not directly accessable from Perl.)

The function you need is LAST_INSERT_ID().

I think this is particular to MySQL. For Sybase and Oracle, also try


SELECT @@IDENTITY FROM table
[download]

My projects have always had this statement in a transaction, but I'm not sure if it's needed.

artful, Appears you are curious as what the name of the column that contains the incremented value is...not what the value of the last insert was from the previous insert.

Sounds like you are looking for a description of the table itself. Since you mention describe, I am thinking you are talking about Oracle. (although as you can see from the forthcoming example, via oracle you would(should) use the sequenced column in the insert..so maybe I assume wrong) Anyway, here is an oracle way...

a typcial sequence that is tied to a column during an insert:

create sequence seq_name
   start with 1
   increment by 1
   maxvalue 999999
   nocycle
   cache 20;
create public synonym seq_name for owner.seq_name;
grant select on seq_name to joe_user;
[download]

With above created, you now do the insert like so:

insert into table 
    (seq_col, cola, colb, colc)
  values 
    (seq_name.NEXTVAL, 'data','moredata','otherdata')
[download]

Now I guess it's quite possible that the technique currently being used does not list the columns prior to the values...ala

insert into table 
  values 
    (seq_name.NEXTVAL, 'data','moredata','otherdata')
[download]

...therefore you are not seeing the column name...if this is the case, modify the sql to use all the columns (you'll probably need the describe at this point...but just once, since you now have the column name in the sql). Note that doing an insert "by position" (without naming the columns prior to values) can get you into trouble if the table is ever altered without warning (that never happens, eh? :-))

anyway hope I was at least close on this one...

good luck

sub get_auto_increment {
    my ($dbh, $table) = @_;

    my $sth = $dbh->prepare("show columns from $table");
    $sth->execute;

    my $ref;
    while ($ref = $sth->fetchrow_hashref) {
        if ($ref->{'Extra'} eq 'auto_increment') {
            $sth->finish;
            return $ref->{'Field'};
        }
    }

    $sth->finish;
    return undef;
}
[download]

Actually, MySQL only allows one auto_increment column per table. So your method should work fine.