Generating IDs can be broken down into two cases:

• perform an action before the insert (call a sequence, generator or pull the next value from a table)
• perform an action after the insert (generally by asking the driver for a property -- 'mysql_insertid' -- or calling another statement to fetch a value -- 'SELECT @@IDENTITY')

So the easiest way I've found to do this is create pre/post actions for the insert and register certain generative capabilities with certain drivers. (This is how we do it in SPOPS.) If you're using PostgreSQL/Oracle/FirebirdSQL you need to fetch the next value from a sequence/generator. And if you're using MySQL/Sybase you need to create an INSERT using all fields except the ID field, then get the ID afterwards.

IME trying to be portable with something like this tends to get you in trouble, since most people already have some scheme for doing this that works quickly, atomically and reliably.

Best of luck,

Chris
M-x auto-bs-mode

The databases I am familiar with that use sequences support getting the previous value. With PostgreSQL it is:

SELECT currval('table_key_seq')
[download]

With Oracle it is:

SELECT table_key_seq.currval FROM dual
[download]

Both of them can use sequences for the default value on the primary key. This makes the usage model the same as MySQL or SQL Server of insert the row without the key, and fetch the just-inserted value of the key.

If you've got transactions, why not create a table of primary keys? Seems the most straightforward mechanism, and can be written in standard SQL so its portable across databases.

If you've not got transactions I've found DBIx::Sequence useful.

You might also find this discussion of interest.

That is a good thought. Something like this would be fine if the database supported trasactions (which MySQL does not). You would then create the table with or without AUTO_INCREMENT. For MySQL we return the required NULL value for id otherwise we generate one in an atomic select/update/commit/rollback.

use DBI;

my $DB_TYPE = 'mysql';
my $AUTO_COMMIT = 1;
my $DB = 'test';
my $DB_USERNAME = 'user';
my $DB_PASSWORD = 'pwd;

my $dbh = DBI->connect( "dbi:$DB_TYPE:$DB", $DB_USERNAME, $DB_PASSWORD
+, {AutoCommit => $AUTO_COMMIT} ) or die_nice( DBI->errstr() );

END { $dbh->disconnect }

# do_sql('DROP TABLE unique_id'); # for debugging

my $sql =<<SQL;
CREATE TABLE unique_id (
    id                  INT UNSIGNED NOT NULL,
    address_book        INT UNSIGNED NOT NULL,
    task_list           INT UNSIGNED NOT NULL,
    PRIMARY KEY (id) )
SQL

# create unique id table;
do_sql($sql);

# initialize table;
$sql = 'INSERT INTO unique_id VALUES (?,?,?)';
do_sql($sql, 1, 0, 0);

print get_unique_id('address_book'), "\n" for 1..10;

sub get_unique_id {
    my ( $table_name ) = @_;
  return 'NULL' if $DB_TYPE = 'mysql';
    my ($select_sth, $update_sth);    
    my $select_sth = get_sth("SELECT $table_name from unique_id where 
+id = 1" ); 
    my $update_sth = get_sth("UPDATE unique_id SET $table_name = ? whe
+re id = 1"); 
    my $success = 1;
    $select_sth->execute() or $success = 0;
    my ($id) = $select_sth->fetchrow_array  or $success = 0;
    $id++;
    $update_sth->execute($id) or $success = 0;    
    $success = ($success ? $dbh->commit : $dbh->rollback) unless $AUTO
+_COMMIT;
    die_nice( "Couldn't finish get_unique_id transaction: " . $dbh->er
+rstr )  unless $success;
    $select_sth->finish;
    $update_sth->finish;
  return $success ? $id : 0;
}

sub do_sql {
    my $sql = shift;
    my $sth = $dbh->prepare($sql) or 
        die_nice( "Could not prepare SQL statement\n\n$sql\n" . $dbh->
+errstr() );
    $sth->execute(@_) or 
        die_nice( "Could not execute SQL statement\n\n$sql\n" . $sth->
+errstr() );
    $sth->finish;
    $dbh->commit unless $AUTO_COMMIT;
}

sub get_sth {
    my $sql = shift;
    my $sth = $dbh->prepare_cached($sql) or 
        die_nice( "Could not prepare SQL statement\n\n$sql\n" . $dbh->
+errstr() );
  return $sth;
}

sub die_nice {
    die shift;
}
[download]

cheers

tachyon

s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

Update - MySQL 4.1 and higher support transactions on the InnoDB and BDB table types.

------
We are the carpenters and bricklayers of the Information Age.

Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

I shouldn't have to say this, but any code, unless otherwise stated, is untested

DBIx::MySQLSequence

A safe, transaction safe sequence emulation for MySQL. The docs explain some reasons you might want to allocate the object ID first, rather than afterwards.

Extracted out of a commercial app I wrote once.

I really need to write the equivalent for some other non-sequence-friendly database one of these days.