Perl Guru Forums: Perl Programming Help: DBI: Stored Proc Error Handling

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	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 (2159 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 (2157 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 (2155 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)

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