[rt-devel] getting insertion ids in various databases

[Jesse]

Ivan suggested a DBIx:: module a while ago just to handle DBD specific
insert and return the auto-incremented primary key.  Here's some content
I found about how to get that key out for various dbs..

http://151.201.22.73/programming/yapc_dbi

Auto-increment fields

  Auto-increment fields are present in many databases. Such a field ensures that each row gets a unique number by keeping an internal counter and incrementing it with every new row inserted. Other databases (such as Oracle and MiniSQL) accomplish this sort of thing through a sequence object. 

  This is very useful to keep your rows unique and saves some administrative headache, but once the value is inserted, you frequently need to know the value that was just used. For instance, when doing a multi-table insert joined by a common value. 

  The DBI does not currently define an interface for retrieving the value of the
auto-increment field, so what follows is database-dependent. 

  MySQL uses the 'AUTO_INCREMENT' tag to identify a field in a table that will be incremented with each INSERT. (There can be only one per table.) The DBD::mysql module makes retrieving the value for the auto-increment field very simple:
 
  Example 6: Retrieving auto-increment value from MySQL 

     1: my $sql = qq/
   2:   INSERT INTO my_widgets
   3:   ( widget_ref, widget_name, widget_factory )
   4:   VALUES ( ?, ?, ? )
   5:   /;
   6: my ( $sth ); 
   7: eval {
   8:   $sth = $dbh->prepare( $sql );
   9:   $sth->execute( '568172-XB', 'Specialized hammer', '912P918-G1' );
  10: };
  11: die "Cannot prepare/execute!\nSQL: $sql\nError: $@" if ( $@ );
  12: my $inserted_id = $sth->{insertid}; 

  Sybase and Microsoft SQL Server both use Transact-SQL as their flavor of SQL. Defining a field as auto-incrementing is typically done like this: 

    CREATE TABLE my_widgets (
    my_id numeric(14,0) identity not null,
  ...

  You can set seed and step values as desired; see your favorite Transact-SQL book for more. Retrieving the value for a Transact-SQL identity column is done like this: 

  Example 7: Retrieving auto-increment value from Sybase/MSSQL 

    1 - 11: same as Example 6
  12: $sth = $dbh->prepare( 'SELECT @@IDENTITY' );
  13: $sth->execute;
  14: my $row = $sth->fetchrow_arrayref;
  15: my $inserted_id = $row->[0];

  (We'll get to how the SELECT statement and the statement handle work shortly.) 

  When writing an application, it's typically a good idea to write a generic fetch_insert_id() method that you can use. Then that method can be aliased to a database-specific method for actually retrieving the value as needed. 

  As for the sequence object, DBI and DBD::Oracle author Tim Bunce recently posted the following on the dbi-users mailing list. It's presented here without modification or testing and for your information only. YMMV. 

  Example 8: Retrieving sequence value from Oracle 

   1: $sth = $dbh->prepare(q{
   2:           INSERT INTO foo (Id) VALUES (seqn.NEXTVAL) RETURNING Id INTO :PId;
   3: });
   4: $sth->bind_param_inout(":PId", \$PId, 100);
   5: $sth->execute;





-- 
jesse reed vincent --- root at eruditorum.org --- jesse at fsck.com 
pgp keyprint: 50 41 9C 03 D0 BC BC C8 2C B9 77 26 6F E1 EB 91
-------------------------------------------------------------
There are no supercomputer applications that are solvable that cannot be solved
in finite time using a fucking TRS-80 with approprite disk/tape drives.  Zero.
									-Tanj