Hi Rolf,
You could do a check on the $dbi:err return code which depend on the used db.
DB2 returns -803 when you try to insert a duplicate record but you have
to check which one your db returns.
I would use something like this:
connect to db with RaiseError => 0 and PrintError => 1
# assume usg_update and usg_insert are prepared already
$sth = $usg_insert->execute(...);
my $err = $dbh->err();
if ( $err eq "-803" ) { # If duplicate.
$sth = $usg_update->execute(...);
if (my $str = $dbh->errstr()) {
print "MSG = $str";
}
}
else {
$str = $dbh->errstr();
print "MSG = $str";
}
Paul.
"Kamp, Rolf F, ALCNS" wrote:
> Hi:
> I have a question dealing with the detection of an error condition. I am
>updating and
> inserting many rows into the database. Most of the time the record exists, so I just
>want to
> do an update. If the update fails, I assume the record does not exist and I must do
>an insert.
> What is the most positive way to discern an update failure due to a
> record not being there from another type of error (such as bad SQL syntax, listener
>gone, or
> some other database error that I cannot recover from). Note that RaiseError is ON.
>
> $dbh = DBI->connect(undef, 'i', 'i3', {AutoCommit => 0, RaiseError => 1,})
> or die ("Cannot connect to the database " . $DBI::errstr);
> # assume usg_update and usg_insert are prepared already
> eval {
> # must this line be in an eval block?
> $uval = $usg_update->execute(...);
> };
> if($@ || $uval == 0) {
> # might I get here for some other reason?
> # update failed...do an insert
> $ival = $usg_insert->execute(...);
> if($ival <= 0) {
> print "Error: insert usage failed\n";
> print "MSG = $@, DBI::err = $DBI::err\n";
> return -1;
> }
> }
