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