On 2001-09-28, Hardy Merrill <[EMAIL PROTECTED]> wrote:
> I can think of 2 methods to handle this:
> 1. before doing the insert or update, for *EACH AND EVERY* unique
> key defined for a table, do a select with the where clause
> looking for equality on all the fields in the unique key - if any
> of those selects find rows, then your insert or update would
> cause a duplicate.
This is not a good solution -- in addition to being a performance drain,
there's a race condition if two or more processes try this at the same
time.
> 2. allow the database to detect the "duplicate key" error [...]
>
> I prefer number 2, but it has a big problem - what do you do about the
> fact that different databases return different error numbers and
> different error messages for the "duplicate" key situation? Do you
> just build a regular expression that attempts to catch all the
> different duplicate key error messages that could come back from
> different db's?
Better yet, use DBIx::AnyDBD to create a DBI wrapper class, in which you
define your new insert_and_catch_duplicates() method; then create
subclasses for each supported database that provide the server-specific
information.
My recollection of the syntax isn't perfect, but you'd end up with
something like this:
package My::DBI::Default;
sub insert_and_catch_duplicates {
my $dbh = shift;
eval {
$sth = $dbh->prepare( shift );
$rows_affected = $sth->execute( @_ );
};
if ($@) {
if ( $dbh->_error_is_dup_key( $@ ) {
# Your duplicate key handling code goes here
} else {
# Other error-handling, or just re-throw the error
die( $@ );
}
}
}
package My::DBI::MySQL;
sub _error_is_dup_key {
my $dbh = shift;
local $_ = shift;
/duplicate key/;
}
package My::DBI::Oracle;
sub _error_is_dup_key {
my $dbh = shift;
local $_ = shift;
/key violation/;
}
package main;
use DBIx::AnyDBD;
my $dbh = DBIx::AnyDBD->connect("dbi:whatever", "user", "pass", {},
"My::DBI");
$dbh->insert_and_catch_duplicates('insert into whatever whatever');
If you've connected to a MySQL server, your $dbh will automatically be
reblessed into that package, and likewise for other server types.
It takes a little getting used to, but I'd heartily recommend
DBIx::AnyDBD for almost any case in which you're trying to extend DBI
with "high-level" functionality in a way that needs to work with multiple
drivers and server types.
-Simon
