Destruction of the connection handle will disconnect it, so you don't need the explicit disconnect. It might be a good idea to call rollback ( and to call commit before disconnect in the success branch). Your db may not have full transaction support, but the calls will do no harm and ease portability.

Because &bail_out represents a failure, it would be better to exit with a non-zero value.

Thanks, Zaxo!

I'm using bail_out to print a host of other messages, some for a successful executing of a query or process. Should I then have exit(0) or exit(1)?

I think you should refactor so that you handle errors with one functions and success with another. Reserve exit(0) for leaving with your utility having done everything your user should expect. You may want to give exit different values to represent differeent errors. 0+$! is a good value if the reason is an error that sets $!. Or you could call die $!, instead.

After Compline,
Zaxo

First you have to answer the question "what do i want to have happend when an error occurs during X?" Then you can decide where to do what.

Regarding your connection line:

$dbh->connect(assuming_relevant_connection_params_are_here);
[download]

my $dbh = DBI->connect(
   'DBI:driver:database:host', 'user', 'pass',
   {RaiseError => 1, HandleError => \&bail_out},
);
[download]

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

I need a little clarification below:

my $dbh = DBI->connect(
   'DBI:driver:database:host', 'user', 'pass',
   {RaiseError => 1, HandleError => \&bail_out},
);
[download]

With the settings above, does it mean that I don't need a 'or' to catch a failed query. See code below:

my $dbh = DBI->connect(
   'DBI:driver:database:host', 'user', 'pass',
   {RaiseError => 1, HandleError => \&bail_out},
);

my $sql = qq{ SELECT COUNT(*) FROM profiles WHERE username=? };

# Original
#my $result = $sth->execute($username)
#  or bail_out('Error executing SELECT');

# Modified
# Note that 'or' clause is removed
my $result = $sth->execute($username);

$dbh->disconnect;
[download]

Relatedly, do I need to explicitly call disconnect within bail_out with your settings (i.e. RaiseError => 1 and HandleError => \&bail_out)?

That is:

sub bail_out {
   my $dbh = shift;
   $dbh->disconnect
     or bail_out(msg => "Cannot disconnect from the database");
   # prints html message
   exit(0);
}
[download]

Thanks for your great help :)

That's right, bail_out will called for you. As for disconnect ... i don't explicitly call it unless i absolutely have to. As noted other replies to this question, you really don't have to. You definetly do not want to call bail_out itside of itself. As always, refer to the manual when in doubt: (just search for HandleError)

use Carp;

...

sub bail_out {
   my $message = shift;
   confess ($message);
}
[download]

Should do the trick for now ... oh, don't worry about passing the message argument when you call bail_out, because you don't even have to call bail_out (but you still can if you need to). Also ... do you really need handle the errors? Have you tried letting DBI do it for you? Just specify RaiseError without any HandleError subroutine ...

And you are always welcome. :)

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

The database will automatically disconnect when the script ends or when the "dbh"-handle goes out of scope (I notice that you did not do a my $dbh).

But if you are using mod-perl, if the script reaches its end, it does not get destroyed and the database connection remains open as exit() does not work under mod-perl.

And if you are pooling your database connections under mod-perl with Apache::DBI disconnect is a no-op anyway.

As disconnect might do some cleaning up (such as rollback, ...) depending on the type of database you are using, I think it is better to do this clean-up explicitly yourself and not rely on what disconnect might or might not do under the hood itself.

While DBI will both clean up your un-finished statement handle, rollback your uncommited transaction (usually, read your DBD doc to be sure) and disconnect your connection when $sth and $dbh go out of scope, its always a good idea to do this yourself (depending, of course, upon the level of reliabilty and robust-ness your application needs). As I have said in other posts, i tend to program in a paranoid way, so take this with a grain of salt. (but hey, I don't get alot of "3a.m.-the-site-is-down/broke" phone calls either)

Personally I have a wrapper class around my DBI objects, which takes care of all this finishing, rolling-back and disconnection for me in the DESTORY method. But before i wrote that my DBI code used to look like this.

# declare them here so 
# they are always in scope
my ($dbh, $sth);
eval {
    $dbh = DBI->connect(@connection_params, 
        {RaiseError => 1, 
         PrintError => 0,
         AutoCommit => 0}) || die "connection failed";
    # die-ing in the above line might seem 
    # redundant, since we are setting the 
    # RaiseError flag, but you don't know
    # for sure that was set, so best to be 
    # sure, since anything other than a valid
    # connection in $dbh is bad.
    $sth = $dbh->prepare($SQL);
    my $result = $sth->execute(@params);
    # ... do something with $result here ...

    # although it doesn't make sense to 
    # commit for a SELECT statement, if 
    # it wasn't a SELECT, i would commit
    # it here
    $dbh->commit();
};
# now lets check for an error/exception
if ($@) {
    # check for "our" error
    if ($@ =~ /^connection failed$/) {
        print "Database unavailable: " . $DBI::errstr;
    }
    # otherwise something in DBI went wrong
    else {
        print $DBI::errstr;
    }
    # again, transactions are irrelvant 
    # with SELECTs, but if you were to
    # need to rollback, you would do it
    # here.
    $dbh->rollback();
}
# clean everything up now
$sth->finish();
$dbh->disconnect();
[download]

This does not catch any exceptions thrown upon finish and disconnect, but if you want to be that paranoid then just wrap this whole thing inside another eval and you'll be fine.

Of course too, if you don't set AutoCommit to be 0, DBI will handle your transactions for you with an explicit commit after every SQL call. Which alot of times is very desirable, I only use explicit transactions if I really need too. I beleive too that calling commit or rollback when AutoCommit is on (the default) will print a warning too (cant find my Perl & DBI book right now to check).

Hope this helps.