Re: What is the meaning of the return value from a DBI do statement?

On 25.05.2010 14:21, Larry W. Virden wrote:
>     $drc = $handle->prepare ("DELETE FROM MyTable WHERE EMPNO = ?") or
> do {
>         print "DELETE prepare failed - $DBI::errstr\n";
>         next;
>     }
>     $drc = $oraProdDBH->execute ($employee) or do {
>         print "DELETE execute for $employee failed - $DBI::errstr\n";
>         next;
>     }
>     if ($drc != 1) {
>         if ($drc eq '0E0' or $drc == 0) {
>                 print "DELETE from MyTable for EMPNO $employee changed
> 0 rows\n";
>         } else {
>                 print "DELETE from MyTable for EMPNO $employee changed
> $drc rows\n";
>         }
>     }
>
>
>   

I really recommend that you set the RaiseError attribute to 1 when 
calling DBI->connect(), and that you read the fine manual (i.e. the DBI 
documentation).
Both together will make your code shorter and easier to read. Wrap code 
that may fail into an eval { ... }, check $@ afterwards, i.e.

my $dbh=DBI->connect('dbi:Oracle:ORCL','scott','tiger',{ RaiseError => 
1, PrintError => 0, AutoCommit => 1 });
eval {
    my $sth=$dbh->prepare(...);
    $sth->execute(...);
    while (my $row=$sth->fetchrow_hashref('NAME_uc')) {
       ...
    }
    $sth->finish();
};
if ($@) {
    print "Something went wrong: $@\n";
} else {
    print "Oh happyness, everything is well\n";
}

Or for a simple $dbh->do(...):

my $dbh=DBI->connect('dbi:Oracle:ORCL','scott','tiger',{ RaiseError => 
1, PrintError => 0, AutoCommit => 1 });
eval {
    if ($dbh->do('DELETE ...',...)>1) { # $dbh->do() will never return 
undef, due to RaiseError. It will die() when it fails.
       die "Rats, killed too many records"; # escape the eval block
    }
};
if ($@) {
    print "Something went wrong: $@\n";
} else {
    print "Oh happyness, everything is well\n";
}

Even better, with transactions:

my $dbh=DBI->connect('dbi:Oracle:ORCL','scott','tiger',{ RaiseError => 
1, PrintError => 0, AutoCommit => 1 });
eval {
    $dbh->begin_work();
    $dbh->do($this);
    $dbh->do($that);
    if ($dbh->do('DELETE ...',...)>1) { # $dbh->do() will never return 
undef, due to RaiseError. It will die() when it fails.
       die "Rats, would have killed too many records"; # escape the eval 
block, avoid the commit and force a rollback
    }
    $dbh->commit(); # commit may fail, in that case, you also want a 
rollback
};
if ($@) {
    warn "Rolling back because an error occured: $@";
    $dbh->rollback(); # may die if even rollback failed, I consider this 
a good thing.
} else {
    print "Oh happyness, everything is well\n";
}


And by the way: if ($drc eq '0E0' or $drc==0) is redundant, it would be 
sufficient to use $drc==0 ('0E0' is 0, but unlike a "real" 0, it 
evaluates as true in a boolean context).


Alexander


-- 
Alexander Foken
mailto:[email protected]  http://www.foken.de/alexander/