CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: DBI:
Stored Proc Error Handling

 



mh53j_fe
New User

Oct 4, 2005, 6:14 AM

Post #1 of 3 (3181 views)
Stored Proc Error Handling Can't Post

I have a web screen that a user may add or update one or more records at one time. Here is my current code:

##the eval block is called inside a loop to go through all records that the end user wants to update or insert.

eval
{
my $sth = $dbh->prepare( "BEGIN Exchange_Rates.ExRates(:1,:2,:3,:4,:5,:6);END;" );
$sth->bind_param(1,$CURRENCY);
$sth->bind_param(2,$COUNTRY);
$sth->bind_param(3,$RATE);
$sth->bind_param(4,$CALENDAR_YEAR);
$sth->bind_param(5,$uid);
$sth->bind_param(6,$p_action);
$sth->execute;
$dbh->commit();
};
if ( $@ ) ##any error
{
eval {$dbh->rollback()};
$errorCnt++;
##output error message to screen, loop stops here! I want to continue!
&outputDbErrors;
}
elsif ( !$@ ) #success
#procs ran successfully - no errors.
{
#return to the cgi form with updated values, visually display success.
} #end

Here is what I would like to do.

(1) If eval traps an error, I want to capture the record's values and continue to the next record. After the loop is through all records, I want to display a message that says which records were successful and which records failed.

Thanks to all for your help.

Regards,

Dave


davorg
Thaumaturge / Moderator

Oct 5, 2005, 2:30 AM

Post #2 of 3 (3179 views)
Re: [mh53j_fe] Stored Proc Error Handling [In reply to] Can't Post


In Reply To
If eval traps an error, I want to capture the record's values and continue to the next record. After the loop is through all records, I want to display a message that says which records were successful and which records failed.



Code
# declare this array outside of your loop 
my @errors;

eval {
my $sth = $dbh->prepare( "BEGIN Exchange_Rates.ExRates(:1,:2,:3,:4,:5,:6);END;" );
$sth->bind_param(1,$CURRENCY);
$sth->bind_param(2,$COUNTRY);
$sth->bind_param(3,$RATE);
$sth->bind_param(4,$CALENDAR_YEAR);
$sth->bind_param(5,$uid);
$sth->bind_param(6,$p_action);
$sth->execute;
$dbh->commit();
};

if ($@) {
eval {$dbh->rollback()};
$errorCnt++;
##output error message to screen, loop stops here! I want
##to continue!
push @errors {
curr => $CURRENCY,
country => $COUNTRY,
rate => $RATE,
year => $CALENDAR_YEAR,
uid => $uid,
p_action => $p_action,
};
}
else { #success
#procs ran successfully - no errors.
#return to the cgi form with updated values, visually display
# success.
} #end


Then outside of your loop you can look at the contents of @errors to see what problems you had and to display the details.

--
Dave Cross, Perl Hacker, Trainer and Writer
http://www.dave.org.uk/
Get more help at Perl Monks


mh53j_fe
New User

Oct 5, 2005, 7:17 AM

Post #3 of 3 (3177 views)
Re: [mh53j_fe] Stored Proc Error Handling [In reply to] Can't Post

Thanks Dave for your quick reply. Your solution is exactly what I need. Great job! It is simple yet effective!



Regards,

Dave

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives