Open Source Web Development with LAMP Using Linux, Apache, MySQL, Perl, and PHP [Electronic resources] نسخه متنی

5.3 Database Independent Interface

Running MySQL commands from the shell is well and good the first 12 times it has to be done. After that, the typical lazy programmer starts thinking of ways to automate the process. Here, the answer is Perl and the DataBase Independent interface (DBI). DBI enables one to write programs to automate database maintenance and to write other scripts to interface with MySQL.

DBI is a Perl module that provides methods to manipulate SQL databases. With DBI, one can connect to a database within a Perl script and issue all kinds of queries, including SELECT, INSERT, and DELETE. For now, we create Perl scripts that can be run from the shell. Later, we'll use CGI, mod_perl, Embperl, Mason, and PHP to hook database independent interfaces into web programs.

First, a quick example. We put all these DBI examples in a directory that is under /var/www/ so that the examples are downloadable from www.opensourcewebbook.com/. In the real world, we do not suggest you create a directory under /var/www/ to create arbitrary Perl programs, but for our purposes, it just makes life easier when downloading all the examples. Create the directory and go there:

$ mkdir /var/www/bin 
$ cd /var/www/bin 

The first example demonstrates how to connect to a database. This code is stored in the file /var/www/bin/connect.pl and online at http://localhost/mysql/connect.pl or www.opensourcewebbook.com/mysql/connect.pl. The content of connect.pl is:

#!/usr/bin/perl -w 
# connect.pl 
# use the DBI module 
use DBI; 
# use strict, it is a Good Idea 
use strict; 
# connect to the database, assigning the result to $dbh 
my $dbh = DBI->connect(´DBI:mysql:people´, ´apache´, ´LampIsCool´); 
# die if we failed to connect 
die "Can't connect: " . DBI->errstr() unless $dbh; 
# all is well! 
print "Success: connected!\n"; 
# disconnect from the MySQL server 
$dbh->disconnect(); 

First, the use DBI method tells Perl to use the DBI module. This allows us to use all the methods in this class.

Calling the connect() method causes the Perl script to connect to the MySQL database using the Perl DBI class. The first argument to this method is the database to which you want to connect. In this example, the string DBI:mysql:people indicates that it should connect with the DBI module to the database people, which is housed on the local MySQL server. The second and third arguments to the connect() method are the username and password used to connect. Here user apache and the supersecret password are passed. If successful, connect() returns a database handle that is assigned to $dbh.

If connect() returns false, the script dies, printing the error string returned by the errstr() method. If the script doesn't die, it prints a message stating that all is well. This gives us a warm, fuzzy feeling (for maximum fuzzy feeling, perhaps we should have printed "hello, world").

The last thing done is to execute the disconnect() method, allowing the Perl script and database to properly shut down the connection. This is only polite, and if you don't call disconnect(), the script may generate an error message, and the MySQL server will not like you.

Executing this program from the shell produces:

$ ./connect.pl 
Success: connected! 

We've connected. But by itself, connecting isn't exceptionally useful, so let's see what records are in the age_information table. Create (or download) the script /var/www/bin/show_ages.pl. Online, it is at http://localhost/mysql/show_ages.pl or www.opensourcewebbook.com/mysql/show_ages.pl. Its contents are as follows:

#!/usr/bin/perl -w 
# show_ages.pl 
use DBI; 
use strict; 
# connect to the server, and if connect returns false, 
# die() with the DBI error string 
my $dbh = DBI->connect(´DBI:mysql:people´, ´apache´, ´LampIsCool´) 
or die "Can't connect: " . DBI->errstr(); 
# prepare the SQL, die() if the preparation fails 
my $sth = $dbh->prepare(´SELECT * FROM age_information´) 
or die "Can't prepare SQL: " . $dbh->errstr(); 
# execute the SQL, die() if it fails 
$sth->execute() 
or die "Can't execute SQL: " . $sth->errstr(); 
# loop through each record of our table, 
# $sth->fetchrow() returns the next row, 
# and we store the values in $ln, $fn and $age 
my($ln, $fn, $age); 
while (($ln, $fn, $age) = $sth->fetchrow()) { 
print "$fn $ln, $age\n"; 
} 
# finish the statement handle, disconnect from the server 
$sth->finish(); 
$dbh->disconnect(); 

Failure to connect is handled differently by this program. It executes connect() and uses the or to mimic an unless. If the connect() fails, the script dies.

The script then prepares the SQL query "SELECT * FROM age_information". The query is just like that we might have typed into the MySQL program in the earlier examples (except the command terminator ; is not required in the prepare() method). The prepare() method returns a statement handle object that can then be used to execute the SQL query by calling the execute() method. Note that with each of these calls, failure is handled with the or die() code.

The results of the SELECT query are handled with a while loop. The fetchrow() method returns a list of data for the next row of data that is returned by the query, which is then assigned to $ln (last name), $fn (first name), and $age. The information is then printed.

At the end, the finish() method is executed to properly clean up and because it is the right thing to do. Running this from the shell produces:

$ ./show_ages.pl 
Larry Wall, 48 
Linus Torvalds, 31 
Eric Raymond, 40 

How might we enter a new record into the table? This code is in the file /var/www/bin/insert.pl. The entire contents of this program can be found online at http://localhost/mysql/insert.pl or www.opensourcewebbook.com/mysql/insert.pl. Here is the good part:

# print a nice dashed line 
print ´-´ x 40, "\n\n"; 
# now, prompt for and read in the data for the new record 
print ´Enter last name: ´; 
chomp($ln = <STDIN>); 
print ´Enter first name: ´; 
chomp($fn = <STDIN>); 
print ´Enter age: ´; 
chomp($age = <STDIN>); 
# prepare SQL for insert 
$sth = $dbh->prepare(´INSERT INTO age_information 
( 
lastname, 
firstname, 
age 
) 
VALUES 
( 
?, 
?, 
? 
)´) 
or die "Can't prepare SQL: " . $dbh->errstr(); 
# insert the record - note the arguments to execute() 
$sth->execute($ln, $fn, $age) 
or die "Can't execute SQL: " . $sth->errstr(); 
# print another dashed line 
print "\n", ´-´ x 40, "\n\n"; 

Before new data is inserted into the table, the script connects to the server and shows the current contents, just as in show_ages.pl.

Then the script asks the user to enter the last name, first name, and age of the person for the new record and chomp()s the newlines.

The next step is to prepare SQL for the INSERT query. Again, it looks much like what one would have typed in directly to SQL, with whitespace characters for readability, except that it has those three question marks. Those question marks are placeholders for the contents of the variables in the execute() method. The variables $ln, $fn, and $age are inserted into the query where the question marks are, in that order.

To check that the insert worked, the script displays the contents of the table after the INSERT is executed. Then the script cleans up after itself by finishing the statement handle and disconnecting from the MySQL server.

Executing that code produces:

$ ./insert.pl 
Larry Wall, 48 
Linus Torvalds, 31 
Eric Raymond, 40 
----------------------------------------
Enter last name: Ballard 
Enter first name: Ron 
Enter age: 31 
----------------------------------------
Larry Wall, 48 
Linus Torvalds, 31 
Eric Raymond, 40 
Ron Ballard, 31