Disconnection

Explicit disconnection from the database is not strictly necessary if you are exiting from your program after you have performed all the work, but it is a good idea. We strongly recommend that you get into the habit of disconnecting explicitly.

DBI provides a method through which programmers may disconnect a given database handle from its database. This is good practice, especially in programs in which you have performed multiple connections or will be carrying out multiple sequential connections.

The method for performing disconnections is:

$rc = $dbh->disconnect();

According to this definition, disconnect() is invoked against a specific database handle. This preserves the notion that database handles are completely discrete. With multiple database handles active at any given time, each one must explictly be disconnected.

An example of using disconnect() might look like:

#!/usr/bin/perl -w
#
# ch04/disconnect/ex1: Connects to an Oracle database
#                      with auto-error-reporting disabled
#                      then performs an explicit disconnection.

use DBI;            # Load the DBI module

### Perform the connection using the Oracle driver
my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" , {
            PrintError => 0
        } )
    or die "Can't connect to Oracle database: $DBI::errstr\n";

### Now, disconnect from the database
$dbh->disconnect
    or warn "Disconnection failed: $DBI::errstr\n";

exit;

Upon successful disconnection, the return value will be true. Otherwise, it will be false. In practice, failure to disconnect usually means that the connection has already been lost for some reason. After disconnecting the database handle can’t be used for anything worthwhile.

What happens if you don’t explicitly disconnect? Since DBI handles are references to Perl objects, Perl’s own garbage collector will move in and sweep up any object trash you leave lying around. It does that by calling the object’s DESTROY method when there are no longer any references to the object held by your script, or when Perl is exiting.

The DESTROY method for a database handle will call disconnect() for you, if you’ve left the handle connected, in order to disconnect cleanly from the database. But it will complain about having to do so by issuing a warning:

Database handle destroyed without explicit disconnect.

A major caveat with the disconnect() method regards its behavior towards automatically committing transactions at disconnection. For example, if a program has updated data but has not called commit() or rollback() before calling disconnect(), the action taken by different database systems varies. Oracle will automatically commit the modifications, whereas Informix may not. To deal with this, the DESTROY method has to call rollback() before disconnect() if AutoCommit is not enabled. In Chapter 6, we’ll discuss the effect of disconnect() and DESTROY on transactions in more detail.