Introduction to the Perl Database Interface

Throughout the book, I've mentioned a number of reasons for the success of XML ”platform independence, easy to read, easy to adapt, widespread acceptance, and a number of other reasons. Unfortunately, the same statements don't apply to the RDBMSs used today. All the major RDBMSs are based on proprietary code and usually include a proprietary programming interface. The problem with proprietary interfaces (in addition to the learning curve) is that they can tie your application to a particular RDBMS. The Perl DBI was developed by Tim Bunce to address these problems; it provides a consistent, platform-independent API to a large number of the most popular RDBMSs. In the next section, we'll take a look at the Perl DBI architecture.

Perl Database Interface Architecture

The Perl DBI provides an API to a RDBMS by abstracting (and hiding) the differences between RDBMSs from different vendors . So, to you (and your application), the API remains the same, regardless of the database with which your Perl script is communicating. A high-level view of the Perl DBI is shown in Figure 6.1.

Figure 6.1. Perl DBI architecture.

As you can see in the figure, the Perl DBI provides an API between your Perl application and the various RDBMSs. It is important to note that the API between your application and the Perl DBI remains consistent, regardless of the RDBMS. What does change, however, is a Database Dependent (DBD) driver that acts as the interface between the Perl DBI and the RDBMS. The DBD driver, as you can probably guess from the name , varies per database. It provides a translation between the RDBMS-independent DBI calls and the corresponding database-specific calls. As long as you load the appropriate DBD driver (which is just a Perl module) for your database, you only need to concern yourself with the API between your application and the Perl DBI ” the underlying DBD drivers handle the rest.

Perl Database Interface Example

Let's take a look at a short example showing a Perl DBI script. This example is using the MySQL open source database. Because we're demonstrating the Perl DBI, you can use any supported database. The only change that you will need to make to the example is to load and reference the appropriate driver for your database.

Back in Chapter 4, "Tree-Based Parser Modules," we discussed an example that used a short XML document containing contact information. The XML document from Chapter 4 is shown in the following as Listing 6.1.

Listing 6.1 XML document containing contact information. (Filename: ch6_contacts.xml)

 <?xml version="1.0" encoding="UTF-8"?>  <contacts>     <client>        <name>Mark</name>        <phone>111-222-3333</phone>     </client>     <client>        <name>Joseph</name>        <phone>222-333-4444</phone>     </client>  </contacts> 

Assume that this information is stored in a simple database table with two fields named name and phone. So, the database table would structure the contents as shown in Table 6.1.

Table 6.1. Structure and contents of the contacts database table.

Name	Phone
Mark	111-222-3333
Joseph	222-333-4444

The Perl program that demonstrates a simple database connection using the Perl DBI is shown in Listing 6.2. As you can see, it is a short program. However, it provides connectivity to a database and retrieves the data stored in a table named "contacts." Let's take a closer look at the example.

Listing 6.2 Perl program that demonstrates the Perl DBI. (Filename: ch6_dbi_sample.pl)

 1.   use strict;  2.   use DBI;  3.  4.   # Initial required parameters.  5.   my $database_name  = "example";  6.   my $db_server      = "127.0.0.1";  7.   my $db_port        = "3306";  8.   my $db_user        = "mark";  9.   my $db_password    = "mark's password";  10.  my $databaseString = "DBI:mysql:$database_name:$db_server:$db_port";  11.  my $array_ref;  12.  13.  # Connect to the RDBMS.  14.  my $dbh = DBI->connect($databaseString, $db_user, $db_password)  15.    or die "Can't connect to RDBMS: $DBI::errstr\n";  16.  17.  # This is the SQL statement we want to execute.  18.  my $sql_statement = "SELECT * FROM CONTACTS";  19.  20.  # Prepare and then execute the SQL statement.  21.  my $sth = $dbh->prepare($sql_statement);  22.  $sth->execute()  23.    or die "Can't execute SQL statement : $dbh->errstr";  24.  25.  # Loop through the array of results.  26.  while ( $array_ref = $sth->fetchrow_arrayref) {  27.    print "Name: $array_ref->[0]\nPhone: $array_ref->[1]\n\n";  28.  }  29.  30.  # Disconnect from the database.  31.  $dbh->disconnect; 

1 “11 The opening section of the program includes the standard use strict pragma. Because this example uses the Perl DBI, we need to also have the use DBI pragma to load the Perl DBI module.

 1.   use strict;  2.   use DBI;  3.  4.   # Initial required parameters.  5.   my $database_name  = "example";  6.   my $db_server      = "127.0.0.1";  7.   my $db_port        = "3306";  8.   my $db_user        = "mark";  9.   my $db_password    = "mark's password";  10.  my $databaseString = "DBI:mysql:$database_name:$db_server:$db_port";  11.  my $array_ref; 

We'll need a few pieces of information to connect to the database. As you can see, we need to identify the following parameters:

• Database name ” Name of the database.

• Database server ” IP address or DNS resolvable hostname.

• Database port ” Default port used by the database API.

• Database user ” Username with appropriate permissions on the RDBMS.

• Database password ” Password associated with the database username.

13 “23 After we've provided the appropriate information, we can go ahead and connect to the database. Note that if you're using a RDBMS other than MySQL, you'll need to insert the appropriate driver name.

After we're connected, we can start executing Structured Query Language (SQL) statements. In this example, because it is such a small table, we're going to execute the following SQL statement:

 SELECT * FROM CONTACTS 

This statement returns all the columns and rows in the CONTACTS table. We then use the prepare and execute methods to make the database query. The prepare statement sends the query statement to the database, which then verifies that it is properly formatted. After the statement has been prepared, we can execute the statement.

 13.  # Connect to the RDBMS.  14.  my $dbh = DBI->connect($databaseString, $db_user, $db_password)  15.    or die "Can't connect to RDBMS: $DBI::errstr\n";  16.  17.  # This is the SQL statement we want to execute.  18.  my $sql_statement = "SELECT * FROM CONTACTS";  19.  20.  # Prepare and then execute the SQL statement.  21.  my $sth = $dbh->prepare($sql_statement);  22.  $sth->execute()  23.    or die "Can't execute SQL statement : $dbh->errstr"; 

25 “31 After the SQL statement has been executed, we can retrieve a reference to the matching row(s) in the table. We then loop through all the matching rows and print out the results.

 25.  # Loop through the array of results.  26.  while ( $array_ref = $sth->fetchrow_arrayref) {  27.    print "Name: $array_ref->[0]\nPhone: $array_ref->[1]\n\n";  28.  }  29.  30.  # Disconnect from the database.  31.  $dbh->disconnect; 

In our case, we had a very small table, and the SQL query yields the following results:

 Name: Joseph  Phone: [phone]  Name: Mark  Phone: [phone] 

After making the query, we call the disconnect() method to disconnect from the database.

Even though this was a simple example, you should be able to see that the Perl DBI is a very powerful tool, especially when combined with the XML modules that I'll discuss in the following sections. Let's start by looking at the XML::Generator::DBI module. It generates XML data based on the results of a SQL query.