On Fri, 28 Sep 2001 17:06:17 -0400, Hardy Merrill wrote:
>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:
> 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?
That's not the only problem. At least on some databases (it does so in
Access), if your database contains auto-incremented fields, the value
for such a field for a new record is incremented at every attempt of
making a new record, even if that attempt fails because of duplicate
primary keys. You'll get holes in the resulting values.
Perhaps I shouldn't mind. I don't think it looks tidy. And, since these
fields are longs (32 bits) in Access, if you get many failures for each
successful insert, you'll soon run out of unique values. And then you're
in big trouble.
--
Bart.
