I would like to have a more structured way of reporting database errors

Me too! :-)

A recent consulting assignment I had had me refactoring legacy Perl code that was very heavily database access. Since the user interface to the application was a browser and PHBs were the target audience it was very undesirable to have raw error messages to be seen by the end user.

Further the requirement was to alert support staff depending on the severity of the error either by SMS or email. In any event all errors and warnings were to be logged with log rotation and retention.

So what's a Perl Monk to do?

There is nothing truly special about die that it can't be replaced by a home grown sub. Here is an example:

sub spit_milk_out_my_nose{
       (my $severity,$msg,$exit,$exit_code)=@_;

       # Insert logic here...
       if ( $severity == 1 ) {
            send_page($msg);
       else {
             send_email($severity,$msg);
       } 
       write2log($severity,$msg);
       
       if ( $exit ) {
          if ( $exit_code ) { 
             display_error_page_exit($exit_code);
          } else { 
             display_error_page_exit(0);
          }
       }
       
}
[download]

That's an oversimplified version of my error processing routine, but it will do for the purposes of this explanation. Each of the referenced subs:

Sub	Function
send_page	Sends an SMS message or series of messages to a predefined group of destinatons. $msg is broken into 140 character pieces and each becomes an SMS
send_email	Sends an email message to the support team along with the severity
display_error_page_exit	Sends the HTML error page to the browser and halts further execution
write2log	Checks the size of the log file and rotates it out if it exceeds a limit prior to logging the message. If needed creates a new log file. Finally writes the message to the log.

Now to show execution examples:

   | hand waving here 
   my $dbh = DBI->connect($connect_string,$user,$password,$options)
                or spit_milk_out_my_nose(SEV1,"Connection error: " . $
+DBI::errstr,1,-1);
[download]

The constant SEV1 was defined by a use constant invocation along with other severity levels. Since we are experiencing some form of connection issue to the database we want to halt further execution of the code. A severity 1 issue in my client's application was cause to not only alert the developers of an issue, but the DBAs and SysAdmins. (That's the logic I did not show in the sub above)

Another example:

my $sth=$dbh->prepare($sql) or 
       spit_milk_out_my_nose(SEV2,
              "Error Preparing:\n" . $sql . "\nDBI returned: \n", $dbh
+->errstr
        );
[download]

In this case we are going to send email to the developers letting them know a query failed. Since the end user has many ways to influence the query it might be a PEBKAC or it could be a real coding issue. Either way the developers need to know about it.

Stuff I didn't show (besides the priority logic in full) is the fact that I prepend all messages with  __PACKAGE__ so the developers know where the prepare failed since there were about 50 home grown packages in play. I also when writing to the logs prepend the long version of the date and time of when the error was reported.

Hope this gives you some ideas on how to proceed.