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:
Connect to your database.
Build a query.
Build a statement handle.
Execute the statement handle.
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;
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.
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:
The string dbi
.
The driver name, mysql
.
The database name, which is Books
in our example.
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.
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.
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 $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.