O'Reilly logo

Programming the Perl DBI by Alligator Descartes, Tim Bunce

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Connection and Disconnection

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.

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:

  1. 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.

  2. 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.[37]

    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.

  3. 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.[38]

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.



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

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required