Re: Trapping error for $dbh->do()

Quoting the DBI man page <http://search.cpan.org/~timb/DBI-1.55/DBI.pm#Database_Handle_Methods>:
do
$rows = $dbh->do($statement) or die $dbh->errstr;
$rows = $dbh->do($statement, \%attr) or die $dbh->errstr;
$rows = $dbh->do($statement, \%attr, @bind_values) or die ...

Prepare and execute a *SINGLE* statement.
If your DBD seems to support mutliple statements in a single $dbh->do(), it does that by accident.

If you need "all or nothing", read about transactions: <http://search.cpan.org/~timb/DBI-1.55/DBI.pm#Transactions>

If you just need to process several SQL commands, use a loop.
my @statements=(...);
foreach my $st (@statements) {
$dbh->do($st);
}

With transactions, you would wrap the entire loop and the final commit inside an eval BLOCK, and call rollback if $@ is true after the eval.

NEVER, NEVER, NEVER put values inside the SQL statements, this begs for trouble and usually performs suboptimal.

Hope that helps,
Alexander



Loo, Peter # PHX wrote:
Hi,
I am trying to execute multi SQL statements within the $dbh->do() and it
appears to work fine except it does not give me an error when part of
the SQL fails. For example:
BEGIN WORK;
CREATE TEMP TABLE p_temp AS
SELECT col1
, col2
, col3
>FROM table1
, table2
WHERE blah blah;
INSERT INTO some_destination_table SELECT col1
, col2
, col3
, etc...
>FROM table1
, table2
, table3;
COMMIT;
The part that does the CREATE TEMP TABLE failed because one of the
tables it is referencing does not exist, however, $dbh->do() did not
return any error. I did in fact turned on the RaiseError in the connect
statement.
unless($dbh = DBI->connect("dbi:$dbDriver:$dbName", $dbUser,
$dbPass, { RaiseError => 1 })) {
$MESSAGE = "ERROR: Connection failed to $dbName for user
$dbUser.";
print STDERR "$MESSAGE\n\n";
$STATUS = $FAILURE;
sub_exit();
}
I am also trying to trap $dbh->do() using "eval".
eval {
$dbh->do($sqlString);
};
if ($@) {
$MESSAGE = "ERROR: dbh->do($sqlString) failed. $@";
print STDERR "$MESSAGE\n\n";
$STATUS = $FAILURE;
sub_exit();
}
Hope someone can shed some light for me. The versions I am using are:
This is perl, v5.8.7 built for sun4-solaris
$ perl -M'DBD::ODBC' -le 'print $DBD::ODBC::VERSION'
1.13
Thanks.
Peter


--
Alexander Foken
mailto:alexander@xxxxxxxx http://www.foken.de/alexander/

.

Relevant Pages