Chapter 9. Perl

9.1.1. Basic Perl Example

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

1. Connect to your database.

2. Build a query.

3. Build a statement handle.

4. Execute the statement handle.

5. Retrieve and process the results.

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;

1. A data source, containing information that varies depending on the database engine you use. For MySQL, this argument includes the following information, separated by colons:

   1. The string dbi.

   2. The driver name, mysql.

   3. The database name, which is Books in our example.

   4. The host, which is localhost in our example. The local host is the most common choice, because most sites put the client application (such as a CGI program on a web server) on the same system as the MySQL server.

2. A username, which is andy in our example. This is actually an abbreviation of andy@localhost. MySQL assumes localhost as the hostname if you don't specify one.

3. A password, which is ALpswd in our example.

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 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.

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.

9.1.2. Setting Up the Database and Program

9.1.3. Error Handling and Attributes

my $dbh = DBI->connect("dbi:mysql:$db:$server", $username, $password,
                       { RaiseError => 1 } );

If you want to do something fancier with attributes, such as set them at runtime, here is the syntax for specifying the attribute argument (it is a reference to a hash):

my %attr = ( RaiseError => 1 );
my $dbh = DBI->connect("dbi:mysql:$db:$server", $username, $password,
                       \%attr);

In either case, running a program with an incorrect password produces the following output (we inserted a line break so the message would fit on the page):

$ perl perl_raise.pl
DBI->connect(Books:localhost) failed: Access denied for user: \
  'andy@localhost' (Using password: YES) at perl_raise.pl line 11
$

The program terminates automatically.

Let's look at an alternative way to handle errors. Suppose you want to trap the connection error in your application gracefully and try another course of action. We'll turn off both error attributes and catch the error in typical Perl fashion.

my %attr = ( PrintError => 0, RaiseError => 0 );
my $dbh = DBI->connect("dbi:mysql:$db:$server", $username, $password,
                       \%attr)
or do {
  warn "Cannot connect to dbi:mysql:$db:$server;
        trying another method" ;
  ...
  }

The connect method returns undef in case of error. So we use an or clause to run some recovery code. Running the program with incorrect connection arguments produces the following output:

$ perl perl_check.pl
Cannot connect to dbi:mysql:Books:localhost; trying another method \
at perl_check.pl line 13.

And the program continues within the do loop.

9.1.4. Introducing Bind Variables and Optimizations

SELECT author FROM Titles WHERE ISBN = '156592567X'

you can create a generalized query with a question mark as a bind variable:

SELECT author FROM Titles WHERE ISBN = ?

and plug in 156592567X or any other ISBN you want before executing the statement.

We'll show a trivial example using a bind variable in this section. More sophisticated examples appear later in the chapter.

The new example alters the previous example to use a WHERE clause in the SELECT statement so that we can limit results to a particular publication date. Additionally, we request just two columns (ISBN and title) instead of all columns.

my $query = q{
    SELECT isbn,title FROM Titles 
        where author like ?
}; 

The q{} syntax is an alternative to using single quotes. The string ends with a question mark, which holds a place for a bind variable. We can plug in the actual value as an argument when we execute the statement:

$sth->execute('%Tim Bunce%'); #just books published in 2001

Assume you had to issue the same query many times using different values for bind variables (during the generation of a daily report, for instance, which retrieves values for many different books or authors). You would prepare the statement with prepare( ), then run a loop that executes the statement repeatedly with different values.

When run, the output is:

$ perl perl2.pl
1565926994      Programming the Perl DBI

my $sth = $dbh->prepare($query);
$sth->bind_param (1, '%Tim Bunce%');
$sth->execute( );

This bind_param( ) call sets the first parameter (our only one) to '%Tim Bunce%'. A third, optional argument allows you to specify the SQL type of the parameter, and we'll see that argument in use later in the chapter.

my $row;
my $result = $dbh->selectall_arrayref($sth);
foreach $row (@$result) {
    print join("\t", @$row),"\n";
}