Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

$sth = $dbh->prepare($sql) or what?

by Steve_BZ (Hermit)
on Sep 21, 2012 at 12:49 UTC ( #994893=perlquestion: print w/ replies, xml ) Need Help??
Steve_BZ has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

I would like to have a more structured way of reporting database errors than just the ubiquitous: $sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr();

Ideally, I'd have a standard piece of code that checks the error code eg:

if ($dbh->err() == 1){ do something; # Duplicate key return 0; } if ($dbh->err() == 2){ do something else; #Missing configuration value; return 0; } die;

So I could log errors to a file or email them to a standard address or just display a dialogue box with an error message chosen by me rather than the system error message.

What are my options?

Regards,

Steve

Comment on $sth = $dbh->prepare($sql) or what?
Select or Download Code
Re: $sth = $dbh->prepare($sql) or what?
by sundialsvc4 (Abbot) on Sep 21, 2012 at 13:14 UTC

    Couple of thoughts.   First, in most of my programs lately you’ll find this.   (Have a look at these Perl modules ...):

    use Try::Tiny; use Exception::Class; use Exception::Caught; use except::classes; // the app-specific taxonomy of classes

    In my code, there is a class which descends from a statement-handle and which incorporates this kind of specific error-checking into it, throwing an appropriate structured error object which might contain properties of its own.   The checking of DBI return-codes and so-forth is used but it only occurs in one place.   You don’t have to check if the statement succeeded because an exception (object...) will have been thrown if it didn’t.

    In fact, the entire app is built around the principle of, “if you screw up, you won’t make it out of here alive.”   Even user-errors cause exceptions to be thrown, to be caught if necessary at the highest levels of the program.   Some logic catches one error, then re-throws another, in exception handling blocks that are built like the layers of an onion.   The handling of exception cases, if you will, is now no longer “exceptional.”   It has a very well defined path that is consistently followed.   The nested class-hierarchy of error objects naturally provided by Exception::Class is used to good effect.

    The outer-level logic contains a try block (which is syntactic sugar provided by Try::Tiny), which looks for these exception classes.   If the logic succeeded, it makes it all the way through; if it doesn’t, it winds up in the catch-block, and in its catcher’s mitt comes an object of a particular recognizable class with particular and predictable properties.   (If “a string” is caught instead, it’s known to be a bad-bug of some sort.)

Re: $sth = $dbh->prepare($sql) or what?
by greengaroo (Hermit) on Sep 21, 2012 at 13:15 UTC

    I would suggest you throw an exception when an error occurs. You don't need if blocks if you do it with objects. Then you wrap this in a try-catch block, see module TryCatch

    Basically you "try" your code, you "throw" an exception object when you have an error and you "catch" the error for displaying it elegantly. You catch block should be able to process any kind of exception. You code would be more generic like that.

    You can build your own exception classes but I suggest you take a look on CPAN, there is plenty of exception modules.

    There are no stupid questions, but there are a lot of inquisitive idiots.
Re: $sth = $dbh->prepare($sql) or what?
by blue_cowdawg (Monsignor) on Sep 21, 2012 at 13:49 UTC
        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); } } }

    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:
    SubFunction
    send_pageSends 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_emailSends an email message to the support team along with the severity
    display_error_page_exitSends the HTML error page to the browser and halts further execution
    write2logChecks 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);

    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 );

    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.


    Peter L. Berghold -- Unix Professional
    Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg

      Hi blue_cowdawg,

      Well, thanks for your suggestions. I don't have SMS available yet, so I use Mail::Sender like this:

      Calling code:

      $loc_sth=$gl_dbh9->prepare($loc_sql_string) or report_die_db( +"Can't prepare sql statement" . DBI->errstr.":\n SQL is: $loc_sql_str +ing", (caller(0))[0]. ":".(caller(0))[1]. ":".(caller(0))[2]);

      Reporting code:

      sub report_die_db{ # # Send emails. # my $message=shift; my $line_number=shift; #my $loc_location_txt = "Package: ".@{$line_number}[0].", file: ". +@{$line_number}[1].", line: ".@{$line_number}[2]; print STDOUT $line_number, "\n", $message; my $from = hostname().'@x---------x.com'; my $msg = "There has been an error at line $line_number. \nThe err +or message is '$message'. "; my $sender = send_email("DB Error at line $line_number", $msg, $fr +om); my $result = $sender->MailMsg({ msg => $sender->{msg}, # file => $sender->{file}, }) or die "$Mail::Sender::Error\n"; die "DB Error" . DBI->errstr; }

      Finally dieing anyway, as I don't know if it's safe to continue.

      Regards

      Steve

Re: $sth = $dbh->prepare($sql) or what?
by mje (Curate) on Sep 21, 2012 at 14:24 UTC

    In addition to what others had said you might find HandlError useful. If you want to see all the things you can find out about an error look at DBIx::Log4perl module's error handler (look for _error_handler sub in DBIx::Log4perl::db.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://994893]
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (5)
As of 2014-11-25 23:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (160 votes), past polls