Our first program is about as simple as Perl DBI can get. Example 9-1 is a program you can run at the command line to query a database and display the results. We’ll describe how it works in this section, but you won’t actually be able to run it until you’ve done the setup described in the next section.

#!/usr/bin/perl -w
  
use strict;
use DBI;
  
my $server = 'localhost'
my $db = 'Books';
my $username = 'andy' ;
my $password = 'ALpswd' ;
  
my $dbh = DBI->connect("dbi:mysql:$db:$server", $username, $password);
  
my $query = "SELECT * FROM Titles"; 
my $sth = $dbh->prepare($query);
$sth->execute(  );
  
while (my $row = $sth->fetchrow_arrayref) {
    print join("	", @$row),"
";
}
  
$dbh->disconnect;

The basic sequence of events (which will be the same for nearly any Perl DBI application) is:

Now for a look at the code, line by line.

As with all Perl modules, you must issue a use statement to get access to the DBI module:

use DBI;

All interactions between Perl and MySQL are conducted through what is known as a database handle . This is an object that implements all the methods used to communicate with the database. You may have as many database handles open at once as you wish, limited only by your system resources.

The first DBI method you invoke, therefore, is a connect( ) method that creates the database handle. It takes three arguments, the first of which consists of several parts.

The call allows a fourth optional argument that can be used to change the default attributes of the connection. We’ll show a bit of its use later.

The $dbh variable is returned by the DBI module and forms our handle into the MySQL server. The variable can be named anything you want, but $dbh is traditional. The handle can be used to to issue queries, updates, or any SQL statements we want. However, the SQL must be wrapped in Perl code.

Our example reads all the titles from the Titles table. We create the SQL query in the $query variable, wrapped up as a string in Perl. Then we prepare a statement handle $sth from the query. Finally, we execute the statement.

Note that prepare( ) is a method provided by $dbh, the database or connection handle, while execute( ) is a method provided by $sth, the statement handle. In other words, the database handle prepares a statement, which then executes itself.

The prepare( ) method takes an SQL query and stores it (either locally or on the database server) until execution. On database servers that store the query on the database server itself, one can perform operations on the query before executing it. However, MySQL does not support that ability yet; it simply stores prepared queries within the database driver until execution. The execute( ) method causes the query to be sent to the database server and executed.

The result of executing a query depends on the type of query. If the query is a non-SELECT query that returns no data (such as INSERT, UPDATE, or DELETE), the execute() method returns the number of rows that were affected by the query. That is, for an INSERT query that inserts one row of data, the execute( ) method will return 1 if the query is successful.

For SELECT queries, the execute( ) method simply returns a true value if the query is successful and a false value if there is an error. The data returned from the query is then available using various methods provided by the statement handle.

So we can now retrieve all the results of our query by issuing calls to the statement handle. We are finished with SQL and with our database, in this sample program. We use DBI methods and Perl arrays to manipulate the data from now on.

Most applications retrieve rows from a statement handle one at a time. The statement handle provides a fetchrow_arrayref( ) method to do this. A typical application issues fetchrow_arrayref( ) in a loop and processes each row in the body of the loop. For our first, simple program, we’ll just display the data returned.

The fetchrow_arrayref( ) method graciously acts like a typical Perl function, returning undef when there are no more rows to fetch. Therefore, we can write a while loop that terminates at an undefined return value.

Because each row consists of multiple fields, the fetchrow_arrayref( ) method returns its results as a reference to an array. Each element of the array is a field in the row. We use @$row syntax to retrieve the array of fields from the reference $row.

Now that all the data has been printed, we can close the database handle. For our trivial program, this is not necessary, because Perl will automatically destroy the handle when the application exits. However, since cleaning up one’s resources is necessarily in some environments (such as when using the Apache mod_perl module), it is a good habit to get into.

Before we run the program, we have to set up the andy user account and the database itself, which we’ll do in the next section.