Connection and Disconnection (Programming the Perl DBI)

The main activity in database programming usually involves the execution of SQL statements within a database. However, to accomplish this task, a connection to a database must be established first. Furthermore, after all the work has been done, it is good manners to disconnect from the database to free up both your local machine resources and, more importantly, valuable database resources.

4.4.1. Connection

In the case of simple databases, such as flat-file or Berkeley DB files, ``connecting'' is usually as simple as opening the files for reading or using the tie mechanism. However, in larger database systems, connecting may be considerably more complicated.

A relatively simple RDBMS is mSQL, which has a simple method of connection: to connect, a program connects to a TCP/IP port on the computer running the database. This establishes a live connection within the database. However, more complex systems, such as Oracle, have a lot more internal security and housekeeping work that must be performed at connection time. They also have more data that needs to be specified by the program, such as the username and password that you wish to connect with.

By looking at a broad spectrum of database systems, the information required to connect can be boiled down to:

The data source name, a string containing information specifying the driver to use, what database you wish to connect to, and possibly its whereabouts. This argument takes the format discussed in the previous section and is highly database-specific.

The username that you wish to connect to the database as. To elaborate on the concept of usernames a little further, some databases partition the database into separate areas, called schemas, in which different users may create tables and manipulate data. Users cannot affect tables and data created by other users. This setup is similar to accounts on a multiuser computer system, in that users may create their own files, which can be manipulated by them, but not necessarily by other users. In fact, users may decide to disallow all access to their files, or tables, from all other users, or allow access to a select group or all users.[39]

[39]In general, this is true. However, some database systems, such as MySQL, support different users but only one schema.

Most major database systems enforce a similar security policy, usually with an administrator having access to an account that allows them to read, modify, and delete any user's tables and data. All other users must connect as themselves. On these systems, your database username may be the same as your system login username, but it doesn't have to be.

More minimal database systems may not have any concept of username-based authentication, but you still need to supply the username and password arguments, typically as empty strings.
The password associated with the supplied username.

In light of these common arguments, the syntax for connecting to databases using DBI is to use the connect() call, defined as follows:

$dbh = DBI->connect( $data_source, $username, $password, \%attr );

The final argument, \%attr, is optional and may be omitted. \%attr is a reference to a hash that contains handle attributes to be applied to this connection. One of the most important items of the information supplied in this hash is whether or not automatic error handling should be supplied by DBI. We will discuss this in further detail in the following section, but the two common attributes are called RaiseError and PrintError , which cause the DBI to die or print a warning automatically when a database error is detected.

This method, when invoked, returns a database handle if the connection has been successfully made to the database. Upon failure, the value undef is returned.

To illustrate the DBI->connect() method, assume that we have an Oracle database called archaeo. To connect to this database, we might use the following code:

#!/usr/bin/perl -w
#
# ch04/connect/ex1: Connects to an Oracle database.

use DBI;            # Load the DBI module

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

exit;

This simple example illustrates the use of the DBI->connect() method to make one connection to the database. We also perform error checking on the call to ensure that the connection occurs; upon failure, the error message will be printed along with the database-specific reason for the failure, which will be contained within the variable $DBI::errstr.[40]

[40]Actually, the error message will be displayed twice for reasons that will be explained in Section 4.5, "Error Handling " later in this chapter.

A more complicated example might be to connect twice to the same database from within the one script:

#!/usr/bin/perl -w
#
# ch04/connect/ex2: Connects to two Oracle databases simultaneously 
#                   with identical arguments. This is to illustrate 
#                   that all database handles, even if identical
#                   argument-wise, are completely separate from
#                   one another.

use DBI;            # Load the DBI module

### Perform the connection using the Oracle driver
my $dbh1 = DBI->connect( "dbi:Oracle:archaeo", "username", "password" )
    or die "Can't make 1st database connect: $DBI::errstr\n";

my $dbh2 = DBI->connect( "dbi:Oracle:archaeo", "username", "password" )
    or die "Can't make 2nd database connect: $DBI::errstr\n";

exit;

or to connect simultaneously to two different databases. For example:

#!/usr/bin/perl -w
#
# ch04/connect/ex3: Connects to two Oracle databases simultaneously.

use DBI;            # Load the DBI module

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

my $dbh2 = DBI->connect( "dbi:Oracle:seconddb", "username", "password" )
    or die "Can't connect to 2nd Oracle database: $DBI::errstr\n";

exit;

This former example is quite interesting, because even though we have used identical arguments to DBI->connect(), the two database handles created are completely separate and do not share any information.

A final example of using DBI->connect() is to connect to two different databases (one Oracle, one mSQL) within the same script. In this case, DBI's automatic error reporting mechanism will be disabled in the mSQL database by passing an attribute hash to the connect() call, as shown here:

#!/usr/bin/perl -w
#
# ch04/connect/ex4: Connects to two database, one Oracle, one mSQL
#                   simultaneously. The mSQL database handle has 
#                   auto-error-reporting disabled.

use DBI;            # Load the DBI module

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

my $dbh2 = DBI->connect( "dbi:mSQL:seconddb", "username", "password" , {
            PrintError => 0
        } )
    or die "Can't connect to mSQL database: $DBI::errstr\n";

exit;

The $username and $password arguments should be specified but may be empty ( '' ) if not required. As discussed previously, the $data_source argument can also be undefined and the value of the environment variable DBI_DSN will be used instead, if it has been set.

4.4.2. 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, "Advanced DBI ", we'll discuss the effect of disconnect() and DESTROY on transactions in more detail.

4.4. Connection and Disconnection

4.4.1. Connection

4.4.2. Disconnection