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 one day we decide that we want to migrate to another database, such as Oracle, we merely need to change mysql to oracle , and the rest of the script stays exactly the same, assuming the script is not executing a query that is specific to that database server ” certainly the case with the scripts in this book. Design for portability!

If connect() returns false, the script die s, 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 die s.

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.

Be sure to use those question marks as placeholders. This prevents the need to escape quotes and other nasty characters, thus making the code more secure. Also, in this case, the last name is defined in the tables as 20 characters of text. If the user enters more than 20 characters , only the first 20 are used ”hence, no overflow problem (although it wouldn't hurt to double-check the length of the input strings).

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