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:[EMAIL PROTECTED] http://www.foken.de/alexander/
