Hi Alexander, Thanks for your kind input. Completely understood except the sentence starting with "NEVER, NEVER...". Will you kindly explain?
Thanks again. Peter -----Original Message----- From: Alexander Foken [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 08, 2007 2:11 PM To: Loo, Peter # PHX Cc: dbi-users@perl.org Subject: 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:[EMAIL PROTECTED] http://www.foken.de/alexander/ This E-mail message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply E-mail, and destroy all copies of the original message.
