17.4 Database programming with Database Interface (DBI)

17.4 Database programming with Database Interface (DBI)

17.4.1 What is DBI?

Compared to ODBC, DBI is a relatively new technology to handle platform-independent database applications. To understand DBI, let's quote a statement from its founder:

DBI is a database access Application Programming Interface (API) for the Perl Language. The DBI API Specification defines a set of functions, variables and conventions that provide a consistent database interface independent of the actual database being used.

Tim Bunce, www.perl.com

From functional and structural points of view, DBI is similar to ODBC. It is also a database interface allowing users to access multiple database types transparently. Whether you are using Oracle, Informix, Sybase, MySQL, or whatever, you don't need to know the underlying vendor-specific codes. The API defined by DBI will work on all database types provided the corresponding DBI driver modules are included in your Perl program.

Similar to the Perl package and MySQL database, DBI is free with open source code. In many cases it is included in the distribution with Perl so that installation and configuration are automatic. At the time of writing, the driver modules (DBD) in Table 17.4 are available and supported by DBI.

Table 17.4. DBI driver modules

DBD::ADO	DBD::Informix	DBD::PrimeBase
DBD::ASAny	DBD::Ingres	DBD::RAM
DBD::Adabas	DBD::InterBase	DBD::SearchServer
DBD::Altera	DBD::LDAP	DBD::Solid
DBD::CSV	DBD::MySQL	DBD::Sprite
DBD::DB2	DBD::ODBC	DBD::Sybase
DBD::Empress	DBD::Oracle	DBD::Unify
DBD::Illustra	DBD::Pg	DBD::XBase

If you don't have DBI installed on your system or you need some of the driver modules, you can download them from Perl's official site: www.perl.com.

From this table, you can see that DBI supports a large number of database types including MySQL and ODBC. This means that you can also access your Microsoft Access and SQL server databases using the DBD::ODBC module. The important feature of DBI is that whether you are using Microsoft or UNIX/LINUX systems, the program codes are the same.

Also, the action of DBI is more straightforward and there is no need for any additional layer or administrator to handle database registrations.

17.4.2 The structure of DBI

DBI is designed to save you time and from programming the details of the database vendor libraries. It has a very simple interface to get the SQL queries you want and to handle the results returned by the database. The main function of DBI is to locate and load the corresponding DBI drivers or DBD (database driver) modules. The DBD modules have vendor libraries designed to talk to the real databases. You can say that DBI works simply because of DBD modules. In effect, there is one DBD module for every different database.

In a Web page or application, when you make a query through the DBI, it sends the query to the appropriate DBD module. When it gets the results back, it passes them to DBI so that you can process them. Figure 17.16 shows how DBI works.

Since there is no additional layer between your Web page and the actual database, you don't need to perform any database registration before you can use them. All you need is to load and activate the associated DBD module. Because of that, DBI works for almost any operating system and is a truly platform-independent technology for handling databases on the Web.

17.4.3 Using DBI and DBD::MySQL

To use DBI with a particular database, you need to install the DBI and the associated driver module (DBD) into the Perl processor. For example, if you are using Active Perl (a Perl package) on a Windows system, you can use the following PPM interactive shell to install DBI and DBD::mysql:

C:/Perl/bin/> ppm
PPM> install DBI
PPM> install DBD::mysql

This will fetch the modules via the HTTP (Internet) and install them. For this procedure to work, you will need to have a connection to the Internet. If the above procedure doesn't work, you may need to upgrade to the latest version of Active Perl.

If you are using UNIX/LINUX such as Red Hat LINUX, the installation (version 7.1 or later) comes with MySQL and DBD::mysql. You need to ensure that the following RPMs are installed:

shell> mysql
mysql> perl-DBI
mysql> perl-DBD::MySQL

If you want to install DBI at source code level, information on installation and configuration can be found on the official site www.Perl.com/CPAN.

Basically, a Web interface for SQL databases can be developed by the following procedures:

• Get the user SQL query string.
  
  
• Connect to the remote database server.
  
  
• Execute the SQL query string.
  
  
• Interpret the returned results.
  
  

To get the user SQL query string is simple. All you have to do is to write a Web page similar to example ex17-08.htm. In order to save some coding time, you can take example ex17-09.htm and replace line 16 by

Accessing MySQL Database(s) via DBI<br />and DBS::MySQL<br /><br />

This line changes the information to indicate that DBI and the DBD::MySQL module are used. Another line you need to change is line 30:

<form action = "ex1711.pl" method="post">

This form element will activate another Perl program, i.e., ex17-11.pl, to perform the SQL processing. We call this new page ex17-11.htm.

To connect to the remote database server and execute the SQL statement, we need the first part of the program ex17-11.pl listed below:

Example: ex17-11.pl - The Perl Script For ex17-11.htm (Part One)

 1: #! /usr/bin/perl
 2:
 3: use DBI;
 4: use CGI qw (:standard);
 5: print "Content-type:text/html\n\n";
 6:
 7: my $querySt = param(querySt);
 8:
 9: if(!($dbh = DBI->connect('dbi:mysql:personnel:www.pwt-ex.com',
10:              'johnsmith', 'johnsmith')))
11: {
12:   print "Connection Error ..";
13:   exit();
14: }
15:
16: if (!($sth = $dbh->prepare($querySt)))
17: {
18:   print "SQL Statement Preparation Error ..";
19:   exit();
20: }
21:
22: if (!($sth->execute()))
23: {
24:   print "SQL Statement Error ..";
25:   exit();
26: }

In order to use DBI, the statement "use DBI;" is used in line 3. Once this DBI module is included in the Perl program, the function DBI->connect() can be used to connect to the database server. The calling format for this function is (see lines 910)

DBI->connect('dbi:mysql:personnel:www.pwt-ex.com','johnsmith','johnsmith')

This function uses the DBI driver dbi:mysql to connect to the remote site www.pwt-ex.com and access the MySQL database called personnel. The connection is made with user name johnsmith and password johnsmith.

If the connection is successful, this function will return a database handle represented by the variable $dbh. If the connection fails, the statement in lines 1213 will be executed to display the message "Connection Error..."

The prepare() function in line 16

prepare($statement)

prepares an SQL statement for execution by the registered database engine. This function returns a statement handle ($sth), which you can use to invoke the execute method to perform the SQL query, i.e.,

$sth->execute()

If the SQL query is not successfully processed, the statements in lines 2425 are used to return a message to the user and terminate the program. If the query is successful, all the rows (or records) affected by the execute method are returned. The next step is to get these rows and display them in the browser window. Consider the second part of the program ex17-11.pl:

Listing: Continuation Of The Perl Script ex1711.pl (Part Two)

27:
28: print << "mypage";
29:  <?xml version="1.0" encoding="iso-88591"?>
30:  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
31:     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
32:  <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
33:  <head><title></title></head>
34:  <style>
35:   .txtSt{font-family:arial;font-weight:bold;font-size:13pt;color:#ffff00}
36:  </style>
37:  <body class="txtSt" style="background:#000088;font-size:18pt;
38:       text-align:center" >
39:   Based On Your SQL Query String:<br />
40:   <div style="font-size:14pt" align="center">
41:   <table><tr>
42:     <td class="txtSt" style="color:#ffffff;width:500px;
43:       text-align:center;font-size:14pt"><br />
44:     $querySt </br><br /></td></tr>
45:   </table>
46:       Query O.K. And The Returned Results Are As Follows:<br /><br />
47:   </div>
48:   <img src="line1.gif" width="600"
49:       height="6" alt="pic" /><br />
50:   <table cellspacing="5" class="txtSt" align="center" width="570">
51: mypage
52:
53: my @data;
54: my $count =0;
55:
56: $colNames = $sth->{NAME};
57: print "<tr>";
58: for ($ii = 0; $ii < length($colNames); $ii++)
59: {
60:  print "<td style=\"color:#00ff00;font-size:16pt\">$colNames->[$ii]</td>";
61: }
62: print "</tr>";
63:

Soon after the XHTML page fragment in lines 2851, an array variable @data is declared to store the database records. Before this array @data is populated with database data, we want to display the name of each field first. To get the field name of each column, you can use the following (line 56):

$sth->{NAME};

This statement returns an array reference of field names. Armed with this information, the for-loop in lines 5861 displays each field name correctly.

To populate the array @data with database data, let's consider the third part of the program below:

Listing: Continuation Of The Perl Script ex1711.pl (Part Three)

64: while (@data = $sth->fetchrow_array())
65: {
66:  print "<tr>";
67:  for ($ii=0;$ii<@data;$ii++)
68:  {
69:   print "<td>$data[$ii]</td>";
70:  }
71:  print "</tr>";
72:
73:  $count++
74: }
75:
76: if ($count == 0)
77: {
78:    print "<tr><td style=\"color:#00ff00;font-size:16pt\">
79:    No record is matched `xxxx'.</td></tr>";
80: }
81:
82: $sth->finish;
83: $dbh->disconnect;
84:
85: print << "endpage";
86:   </table><br />
87:   <img src="line1.gif" width="600"
88:      height="6" alt="pic" /><br />
89:   Total Entries = $count<br /><br />
90:   </body>
91:   </html>
92: endpage

To get one row of data, you can use the fetchrow_array() function as

@data = $sth->fetchrow_array()

This function fetches one row of data and assigns them to the array element @data. The for-loop used in lines 6770:

for ($ii=0;$ii<@data;$ii++)
{
 print "<td>$data[$ii]</td>";
}

displays the data $data[$ii] inside an XHTML table. The counting variable $count is then incremented by 1 in line 73. If you apply a while-loop on top of this for-loop, you will display all returned database records. If the counting variable $count is zero, an error message is displayed to show that no record is matched.

Soon after all the data are displayed, the finish statement is called:

$sth->finish;

This statement terminates the SQL query and frees up the database handle $sth and any system resources associated with it. The disconnect method used in line 83 disconnects the database handle from the database. The rest of the program is used to complete the XHTML page and return to the user. To call this program ex17-11.pl, you can make a copy of example ex17-09.htm and call it ex17-11.htm. The next step is to modify the form action to call this Perl script. As a result the new example ex17-11.htm will execute ex17-11.pl instead of ex17-09.pl to get the database records. Some screen shots of this example in action are shown in Figs 17.17 and 17.18.

Since the DBD::MySQL module can only be used to access MySQL database systems, the next question is: "How can we use DBI to access ODBC databases such as Microsoft Access and SQL server?"

To answer this question, let's consider an example to use the DBI driver module DBD::ODBC. You will find that to change from DBI::MySQL to DBI::ODBC is surprisingly easy.

17.4.4 Using ODBC with module DBD::ODBC

To develop a DBI example to use ODBC databases, we first make a copy of ex17-11.htm and call it ex17-12.htm. The next step is to modify lines 16 and 30 as below:

16: Accessing MySQL Database(s) via DBI<br />and DBD::ODBC<br /><br />
30: <form action = "ex1712.pl" method="post">

The first line is a message to let the user know that you are using the DBD::ODBC module to access databases on ODBC. The second line activates the Perl program ex17-12.pl to perform the SQL query. To convert example ex17-11.pl to ex17-12.pl, all you have to do is to change one line (i.e., line 9) for DBI::ODBC access. The first 14 lines of ex17-12.pl are listed as follows:

Example: ex17-12.pl - The Perl Script Fragment For ex17-12.htm

 1: #! /usr/bin/perl
 2:
 3: use DBI;
 4: use CGI qw (:standard);
 5: print "Content-type:text/html\n\n";
 6:
 7: my $querySt = param(querySt);
 8:
 9: if(!($dbh = DBI->connect('dbi:ODBC:personnel', 'johnsmith', 'johnsmith')))
10: {
11:   print "Connection Error ..";
12:   exit();
13: }
14:

Basically, the modification in line 9 is to change the name from MySQL to ODBC. Since we have registered the MySQL database personnel on ODBC, the connection is legal and will be executed. That's it: we now have an interface to handle ODBC through DBI. If you put in a multiline SQL string such as

SELECT *
FROM people
WHERE salary > 25000;

all employees whose salary is more than 25,000 will be displayed. Some screen shots are shown in Figs 17.19 and 17.20.

There are a number of differences between Win32::ODBC and the DBI::ODBC module. Basically, Win32::ODBC provides more low-level functions to control your databases. That means you may need to do things yourself. On the other hand, the structure of DBI::ODBC is closer to high-level programming with fewer callable functions. Functions in DBI::ODBC are compatible with all other DBI modules.

Now we have a number of programs using Perl to control databases on the Web, let's consider other technologies and programming techniques to control MySQL, namely, ADO and ASP from Microsoft.