Kiat,

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.

-stvn