Beginning Perl Web Development - From Novice To Professional (2006)

50 C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

The DBI loads one or more database drivers (generally referred to as DBD, for database dependent). The DBD, which will be discussed shortly, has the specific software and code required to access a given type of database. It provides the interface between the DBI and the type of database for the connection.

Without a DBD, the DBI in and of itself isn’t terribly useful. However, the DBI provides the methods that you use to work with the database itself. When coupled with the appropriate DBD, the DBI is the key to making database connections work.

The DBI gives you three objects, called handles, that enable you to work with the database. These are the driver, database, and statement handles. Driver handles are not commonly used within a CGI program, and will not be discussed here. Database handles are used within a program in order to make a connection to the database. Statement handles are children of database handles that are used to send SQL statements to the database. These two types of handles are discussed in detail later in this chapter, in the “Database Handles” and “Statement Handles” sections.

Database Drivers

A database driver provides the database-interaction methods that are specific to the individual database implementation. It is commonly referred to as the DBD, for database dependent, since its code depends on which database is being used. For example, a MySQL database has different syntax than an Oracle database. The DBI operates independently of the database, leaving the implementation-specific bits to the DBD.

You might be curious as to which drivers are installed on your server. The DBI module provides a function for listing all of the currently installed drivers. Listing 3-1 uses the available_drivers() function of the DBI module to retrieve the drivers available on the server.

Listing 3-1. Listing Currently Installed Drivers

#!/usr/bin/perl

use strict; use DBI;

my @drivers;

@drivers = DBI->available_drivers();

foreach my $dbd (@drivers) {

print "$dbd driver is available\n";

}

exit;

You run this program from the command line. The output will look something like this:

ExampleP driver is available Proxy driver is available mysql driver is available

C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

51

The program incorporates the DBI into the namespace with this line:

use DBI;

The available drivers are placed into an array called @drivers with this line:

@drivers = DBI->available_drivers;

Finally, the array is expanded within the foreach loop and printed to STDOUT, producing the output.

As you saw from the output, the MySQL DBD is installed on this server. If you wanted to connect to a different type of database, you would need to obtain the DBD module from your favorite CPAN mirror or install it from your distribution’s repository. For example, Debian 3.0 includes a number of DBDs, a listing of which is available by searching the repository with the command apt-cache search dbd.

Some of the more popular DBDs include the following:

•MySQL: As previously stated, MySQL is one quarter of the prized LAMP (Linux-Apache- MySQL-Perl) development platform that’s so popular around the world.

•PostgreSQL: Another popular open-source database is PostgreSQL. The DBD for PostgreSQL is similar to that of MySQL.

•ODBC: The ODBC DBD is commonly used to connect to databases that run on Windows systems, such as Microsoft SQL Server and Microsoft Access, but the ODBC driver could be used to connect to virtually any database that offers ODBC connectivity.

•Sybase: Another popular DBD is used with the Sybase database server. This server and the DBD for it won’t be covered in this book. For more information about running Sybase on Linux and the DBD for Sybase, see http://www.peppler.org/.

Data Source Names, Credentials, and Attributes

A data source name, or DSN for short, is the information needed by the DBI in order to connect to the database. This information includes the DSN itself, as well as other information such as the hostname of the server hosting the database, the name of the database, a port number, and so on. The exact information that you must supply depends on the DBD that you’re using to connect. For example, an Oracle DBD might require or accept different parameters than the MySQL DBD uses.

The DSN is a simple string, frequently stored in a variable called $dsn. DSNs begin with the characters dbi:, followed by the name of the driver. For example, the DSN for a MySQL database is dbi:mysql. You can expand the code in Listing 3-1 to show the valid DSNs for the given database drivers on your system by using the data_sources() DBI method, as shown in Listing 3-2.

Listing 3-2. Listing Valid DSNs

#!/usr/bin/perl

use strict; use DBI;

52C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

my @drivers;

@drivers = DBI->available_drivers;

foreach my $driver (@drivers) {

print "$driver driver is available\n"; my @dsns = DBI->data_sources($driver); foreach my $dsn (@dsns) {

print "\tDSN: $dsn\n";

}

}

When executed, the output contains valid DSNs corresponding to the drivers that are on the system. For example, here is output for the MySQL driver on my system:

mysql driver is available DSN: DBI:mysql:books DSN: DBI:mysql:maildb DSN: DBI:mysql:music DSN: DBI:mysql:mysql

Here’s a sample DSN for connecting to a MySQL database called books located on a server at the IP address 192.168.1.10.

my $dsn = "dbi:mysql:books:192.168.1.10";

An alternate syntax is more explicit:

my $dsn = "dbi:mysql:database=books;hostname=192.168.1.10"

Following the DSN are optional (but usually required) credentials, including a username and password, for the database connection. When calling the connect() method, the username and password are passed as additional arguments to the method, separated by commas:

$dbh = DBI->connect($dsn,"username","password");

The $dbh refers to the database handle, as discussed in the next section.

Like the DSN, the username and password are frequently placed into variables and passed as such to the DBI’s connect() method:

$dbh = DBI->connect($dsn,$username,$password);

You can send additional attributes along to the connect() method. These are attributes that apply to the handle and can be represented individually or as a hash. Two attributes that are often applied are RaiseError and PrintError. Both of these attributes are discussed in the “Error Handling” section later in this chapter.

Database Handles

As stated previously, the process of connecting to a database is mostly generalized among databases, although there are some considerations for specific types of databases. Connecting to a database creates a database handle object, which is used as a marshalling point to create statement handles and interact with the database server.

C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

53

Database handles represent a connection to the database, and you could have multiple connections to multiple (or the same) database by defining multiple database handles. Database handles are usually referred to in code as $dbh.

Creation of a database handle through the DBI requires a DSN. Most databases today are multiuser, which means that credentials such as a username and password are also required in order to connect to the database and create the database handle. It is good practice to connect to the database as few times as possible (usually once) within a program, rather than connecting and disconnecting for each statement or query.

The DBI connect() method connects to the database and returns the database handle:

$dbh = DBI->connect($dsn, ...);

■Note Though you’ll frequently see the database handle object referred to as $dbh, there is no reason why it couldn’t be called any other valid variable name. The $dbh is a long-standing convention, and I will use it in this book to refer to a database handle.

Statement Handles

Just as database handles are created from the DBI, statement handles are children of a database handle. As the name implies, statement handles are used for individual statements, such as SELECT, INSERT, UPDATE, DELETE, and so on, to be executed on the database server. Multiple statement handles can (and usually are) defined based on one database handle within a given program.

Statement handles are created on a per-statement basis. This means that if you have multiple statements to execute against a given database handle, you will need multiple statement handles. However, you can reuse a statement handle if the statement needs to be rerun against a database. In fact, you can use parameters to execute the same statement but with different values, as explained in the “Binding Parameters” section later in this chapter.

Statement handles are not always necessary in order to issue statements to the database. Statement handles are necessary when you need to retrieve, or fetch, information from the database. However, statements also can be executed directly against the database through the database handle’s do() method, as explained in the “Executing Other SQL Statements” section later in this chapter.

As previously stated, a statement handle is created from the database handle. In practical terms, this means that you create a variable, commonly called $sth, to hold the statement handle object. The statement handle variable’s prepare() method is called on the database handle. Here’s an example:

my $sth = $dbh->prepare("SELECT user,host FROM mysql.user");

By itself, the statement handle created in the code example may not actually do anything! For some database types, including MySQL, interaction with the database has not yet taken place. Rather, the DBI has simply prepared the statement for later execution. This prepare() method call is used to enable parameterizing of the statement before executing the statement

54 C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

later. The statement won’t actually be executed against the database until the execute() method is called on the statement handle, $sth:

$sth->execute();

At this point, the statement—in this case, a SELECT statement—has been run against the database, although no results have been retrieved.

■Note Some databases will parse the statement when prepare() is called and thus can return errors if the server encountered problems while trying to parse the SQL statement given in the prepare() method. Refer to the documentation for the given database driver to determine if statements are parsed at the time of the prepare() call.

Error Handling

It’s important to check and handle error conditions when working with databases. The DBI itself automatically handles errors when it detects that one has occurred. The DBI uses two attributes— PrintError and RaiseError—to automatically report errors. The PrintError attribute uses the warn() function to report errors, and the RaiseError attribute uses the die() function. Automatic error checking is useful for many situations, but as a developer, you may find it desirable to manually check for and handle errors for better programmatic control in case an error pops up.

You can disable both the PrintError and RaiseError attributes by setting the value of each to 0. This can be done at any time, on the fly if you will, or more commonly, at the time of database handle creation and connection to the database. As you might expect, you can also reenable a previously disabled attribute, setting the value to 1. For example, recall the database connection method called earlier. It looks like this:

my $dbh = DBI->connect($dsn,$username,$password);

You can disable the PrintError and RaiseError attributes like so:

my $dbh = DBI->connect($dsn,$username,$password, PrintError => 0, RaiseError => 0);

As previously stated, these values can be set in a hash as well. (If you’re unfamiliar with hash references, refer to Beginning Perl, Second Edition by James Lee.) To set them this way, the hash—let’s call it %attr—first needs to be created and then added to the connect() method call as a hash reference:

my %attr = ( PrintError => 0, RaiseError => 0

);

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

By default, the DBI enables warnings only through the PrintError attribute, whereas RaiseError is not enabled when using the connect() method.

C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

55

Knowing that PrintError and RaiseError are available is helpful. Both PrintError and RaiseError can be enabled on any handle, although they are most often used with database handles. As previously stated, the attributes can be enabled and disabled on the fly, as needed. Assume that you have a database handle called $dbh. You can set the attributes on this handle at any time in this way:

$dbh->{PrintError} = 1; $dbh->{RaiseError} = 1;

To turn the attributes off, change the value from 1 to 0.

Even with PrintError and RaiseError, it may be easier to manually call the die() function. This is especially important when initially connecting to the database. The DBI connect() method returns an undef when the connection fails, which makes it easy to check to ensure that the connection was successful. A common method for doing so is to use die() with the DBI connect() method call:

my $dbh = DBI->connect($dsn,$username,$password)

or die "Cannot connect to database: $DBI::errstr";

This example also uses the $DBI::errstr method, which returns a description of the actual error.

■Caution It’s a best practice to not fail quite so loudly when programming a web application. In other words, you wouldn’t want to give too verbose of an error message and possibly divulge information to a web site visitor when a connection fails. For that reason, if you will be outputting errors to the browser, don’t include the $DBI::errstr variable in that output.

There’s nothing preventing you from using a combination of automatic and manual error checking within your code. In addition, you can use three other methods to help when debugging:

•The err() method returns the error number.

•As just noted, the errstr() method returns a string containing a description of the actual error.

•The state() method returns the SQLSTATE error string.1

You’ll likely find that the errstr() method is the most useful of the three debugging methods.

Using SQL Databases with the DBI

Now that you have a grasp of the objects involved in database connectivity through Perl, including the DBI, various DBDs, database handles, and statement handles, it’s finally time to

1. This method is not supported by all drivers.

56 C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

connect to the database and do something. This section examines the steps necessary to connect to a database, retrieve some results, and perform other actions necessary for successful database interaction.

Connecting to the Database

The first step is to load the DBI into your namespace and then create a database handle by connecting to the database. For example, putting everything together to form a connection string and create a database handle for connecting to a MySQL database called mysql on the host 192.168.1.10 looks like this:

#!/usr/bin/perl

use DBI; use strict;

my $dbh = DBI->connect("dbi:mysql:mysql:192.168.1.10","dbuser","dbpassword");

Alternatively, if you’ve stored the DSN information in its own variable and stored the username and password in variables, you can create the database handle as follows:

#!/usr/bin/perl

use DBI; use strict;

my $username = "dbuser";

my $password = "dbpassword";

my $dsn = "dbi:mysql:mysql:192.168.1.10";

my $dbh = DBI->connect($dsn,$username,$password);

This database connection does not perform any fatal error checking, relying instead on the DBI’s internal error checking, which in this case, will be the PrintError attribute. The result will be that any connection errors are only warnings, rather than fatal errors. To cause a fatal error on connection failure, you can call the die() function explicitly:

my $dbh = DBI->connect($dsn,$username,$password)

or die "Cannot connect to database: $DBI::errstr";

From this database handle, $dbh, you create statement handles that all tie back to this database handle. This means that any queries or other SQL statements run will do so with the permissions and privileges of the database user for that database handle. If the user that you used to connect to the database has only SELECT privileges, you will not be able to perform another operation, such as INSERT, through that database handle.

In addition, it’s important to note that database handles connect to an individual database. While you can use as many tables within that database as your privileges permit, you cannot (usually) work with tables in other databases. If you need to work with multiple users to gain additional privileges or work with more than one database, you’ll need to create additional database handles and subsequent statement handles from there.

C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

57

Disconnecting from the Database

When you’re finished executing queries and working with the database handle, it’s good practice to explicitly disconnect from the database. Database servers such as MySQL may automatically end the session after N seconds of idle time and N seconds of session lifetime, where N is dependent on the server configuration. However, most servers will also have a connection limit as well, which could easily be reached under heavy load or when something goes wrong and a program continues to execute. For these reasons, explicitly disconnecting makes life easier in the long run.

The disconnect() method is a database handle method and thus, when you’re finished executing queries, retrieving results, and flushing statement handles (as explained in the following sections), you can disconnect from the database by calling disconnect():

$dbh->disconnect();

Executing a Query

Executing a query against a relational database using the DBI is a multistep process:

1.You create a statement handle by calling the prepare() method of the database handle.

2.For databases such as MySQL, you call the execute() method on the statement handle in order to actually execute the SQL statement on the server.

3.To retrieve the results of the query, you use one or more methods of the statement handle. Retrieving the results is explained in the next section.

Let’s build on the statement handle you saw earlier:

my $sth = $dbh->prepare("SELECT user,host FROM mysql.user");

Executing the query against the database requires calling the execute() method against the statement handle:

$sth->execute();

It’s a great idea to check for fatal errors when executing a SQL statement due to a plethora of issues that can avail themselves at this late hour. Therefore, if you haven’t enabled RaiseError on the database handle (remember, PrintError is enabled by default, but is only a warn() level), you should check for success by using or with the statement handle’s execute() method:

$sth->execute() or die "Cannot execute sth: $DBI::errstr";

■Note Even though you’ve executed the statement on the database, it may still be running on the server. Queries that return a large number of rows, or otherwise overtax servers, might not be able to complete immediately, but will continue executing as you fetch the results.

58 C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

Retrieving the Results

What fun would executing queries against a database be without actually being able to process the results of the query? Results from a query are returned to the statement handle. It’s your job to programmatically iterate through the results. This is usually accomplished by looping through the result set and performing some action on each row as it is retrieved. The method usually used for this purpose is the fetchrow_array() method2 of the statement handle.

Let’s continue with the sample statement handle prepared and then executed:

my $sth = $dbh->prepare("SELECT user,host FROM mysql.user"); $sth->execute or die "Cannot execute sth: $DBI::errstr";

This query will retrieve the username and the host from the MySQL user table, which holds the usernames and passwords for the MySQL server. To iterate through this result set, construct a while loop to call the fetchrow_array() method on each loop:

while (my($username,$hostname) = $sth->fetchrow_array()) { print "Username is $username. Host is $hostname\n";

}

The entire program is shown in Listing 3-3.

Listing 3-3. Retrieving Query Results Listing MySQL Users and Hosts

#!/usr/bin/perl

use DBI; use strict;

my $username = "dbuser";

my $password = "dbpassword";

my $dsn = "dbi:mysql:mysql:192.168.1.10";

my $dbh = DBI->connect($dsn,$username,$password)

or die "Cannot connect to database: $DBI::errstr";

my $sth = $dbh->prepare("SELECT user,host FROM mysql.user");

$sth->execute() or die "Cannot execute sth: $DBI::errstr";

while (my($username,$hostname) = $sth->fetchrow_array()) { print "Username is $username. Host is $hostname\n";

}

$dbh->disconnect();

2.There also are variations of the fetchrow_array() method, including fetchrow_arrayref() to retrieve an array reference, fetchrow_hashref() to retrieve a reference to a hash, and others. For more information about these other methods, refer to the documentation on the DBI (perldoc DBI).

C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

59

When run, the program will retrieve each username and hostname defined in the MySQL user table. Here’s some sample output from one of my servers:

Username is postfix Host is localhost

Username is root Host is localhost

Username is Host is localhost

Username is testuser Host is localhost

Username is user Host is localhost

■Note Some fields may be blank, depending on what’s defined in the MySQL user table.

Within the while loop to retrieve results, you can perform any action necessary on the data retrieved. The example in Listing 3-3 prints the results. A common task is to push the results into an array for later use, as shown in the example in Listing 3-4, which looks for hosts with the MySQL wildcard character, %.

■Note A percent sign within the MySQL user table indicates a wildcard. In other words, it stands for any host. For example, a wildcard host entry of simply % means essentially any host, anywhere, with that user. A wildcard host entry of 192.% would require only the first octet of the IP address to match in order for the connection to be allowed from that host. Of course, a valid username (and hopefully a password) would also be required.

Listing 3-4. Pushing Query Results to an Array to Find Wildcard Hosts

#!/usr/bin/perl

use DBI; use strict;

my $username = "dbuser";

my $password = "dbpassword";

my $dsn = "dbi:mysql:mysql:192.168.1.10";

my $dbh = DBI->connect($dsn,$username,$password)

or die "Cannot connect to database: $DBI::errstr";

my $sth = $dbh->prepare("SELECT host FROM mysql.user");

$sth->execute() or die "Cannot execute sth: $DBI::errstr";