I'm wondering what other people have developed as database
independent way(s) of determining if the insert or update being
done results in a duplicate key situation.
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.
2. allow the database to detect the "duplicate key" error -
when you have RaiseError turned on and you do the insert
or update in an eval, then the "duplicate" error message
is manifested in the $@ variable.
In this case, after the eval you would test the value
of $@ like this:
eval {
$sth = $dbh->prepare($insert_sql);
$rows_affected = $sth->execute($a, $b, $c);
};
if ($@) {
### eval had a problem - how do I(in a database
### independent way) determine if the problem was
### a "duplicate key" situation?
}
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?
TIA.
--
Hardy Merrill
Mission Critical Linux, Inc.
http://www.missioncriticallinux.com
