Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

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

by blue_cowdawg (Monsignor)
on Sep 21, 2012 at 13:49 UTC ( #994909=note: print w/replies, xml ) Need Help??

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

      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:
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

Replies are listed 'Best First'.
Re^2: $sth = $dbh->prepare($sql) or what?
by Steve_BZ (Chaplain) on Sep 27, 2012 at 11:25 UTC

    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().''; 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.



Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://994909]
[1nickt]: yes.
[1nickt]: one of the largest distros
[Corion]: I wait for the day when DateTime uses Moose ;)
[TCLion]: finaly finished the install wow that was over 10 min
[1nickt]: Corion using Moose will hardly reduce the number of dependencies or time to install DT !

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (8)
As of 2017-03-23 15:27 GMT
Find Nodes?
    Voting Booth?
    Should Pluto Get Its Planethood Back?

    Results (289 votes). Check out past polls.