12.1. Using Perl DBI with MySQL

This section presents basic tasks that you can perform with Perl DBI.

12.1.1. Connecting to MySQL

To interface with MySQL, first you must call the DBI module and then connect to MySQL. To make a connection to the bookstore database using the Perl DBI, only the following lines are needed in a Perl script:

#!/usr/bin/perl -w use strict; use DBI;     my $dbh = DBI->connect ("DBI:mysql:bookstore:localhost",                         "username","password")           or die "Could not connect to database: "           . DBI->errstr;

The first two lines start Perl and set a useful condition for reducing scripting errors (use strict). The third line calls the DBI module. The next statement (spread over more than one line here) sets up a database handle that specifies the database engine (mysql), the name of the database (bookstore), the hostname (localhost), the username, and the password. Incidentally, the name of the database handle doesn't have to be called $dbh anything will do. Next, the or operator provides alternate instructions to be performed if the connection fails. That is, the script will terminate (die) and then display the message in quotes along with whatever error message is generated by the driver using the errstr method from the DBI the dot (.) merges them together.

12.1.2. Executing an SQL Statement

Making a connection to MySQL does little good unless an SQL statement is executed. Any SQL statement that can be entered from the mysql client may be executed through the API. Continuing the previous example, let's look at how an SQL statement that retrieves a list of books and their authors might look:

my $sql_stmnt = "SELECT title, author                  FROM books";  my $sth = $dbh->prepare($sql_stmnt); $sth->execute( );

The first line (terminated by the semicolon) sets up a variable ($sql_stmnt) to store the SQL statement. The next line puts together the database handle created earlier and the SQL statement to form the SQL statement handle ($sth). Finally, the third line executes the statement handle in the notational method of the DBI module.

12.1.3. Capturing Data

Having connected to MySQL and invoked an SQL statement, what remains is to capture the data results and to display them. MySQL returns the requested data to Perl in columns and rows, as it would with the mysql client, but without table formatting. In Perl, rows are returned one at a time by MySQL and are processed usually by a loop in Perl. Each row is returned as an array, one element per column in the row. For each array, each element can be parsed into variables for printing and manipulation before receiving or processing the next row. You can do this with a while statement like so:

while (my($title, $author) = $sth->fetchrow_array( )) { print "$title ($author} \n"; }

At the core of this piece of code is the fetchrow_array() method belonging to the DBI module. As its name suggests, it fetches each row or array of columns, one array at a time. The while statement executes its block of code repeatedly so long as there are arrays to process. The value of each element of each array is stored in the two variables $title and $author. Then the variables are printed to the screen with a newline character after each pair.

12.1.4. Disconnecting from MySQL

Once there is no longer a need to maintain a connection to the MySQL database, it should be terminated. If the connection stays idle for too long, MySQL will eventually break the connection on its own. To minimize the drain on system resources, however, it's good practice to have scripts end their sessions like so:

$sth->finish( ); $dbh->disconnect( ); exit( );

This first line closes the SQL statement handle. As long as the connection to MySQL is not broken, as it will be in the second line here, more SQL statement handles could be issued, prepared, and executed without having to reconnect to MySQL. The last line of code here ends the Perl script.

12.1.5. Temporarily Storing Results

Perhaps a cleaner method of retrieving data from MySQL than the one just explained involves capturing all of the data in memory for later use, thus allowing the connection to MySQL to end before processing and displaying the data. Putting MySQL on hold while processing each row as shown earlier can slow down a script. It's sometimes better to create a complex data structure (an array of arrays) and then leave the data structure in memory, just passing around a reference number to its location in memory. To do this, instead of using fetchrow_array( ), you'd use the fetchall_arrayref( ) method. As the method's name indicates, it fetches all of the data at once, puts it into an array, and returns the array's starting location in memory. Here is a Perl script that uses fetchall_arrayref( ):

#!/usr/bin/perl -w use strict; use DBI;     # Connect to MySQL and execute SQL statement my $dbh = DBI->connect("DBI:mysql:bookstore:localhost",                        "username","password")           || die "Could not connect to database: "           . DBI->errstr;     my $sql_stmnt = "SELECT title, author                  FROM books"; my $sth = $dbh->prepare($sql_stmnt); $sth->execute( );     # Retrieve reference number to results my $books = $sth->fetchall_arrayref( ); $sth->finish( ); $dbh->disconnect( );         # Loop through array of arrays containing data     foreach my $book (@$books){        # Parse each row and display     my ($title, $author) = @$book;    print "$title by $author\n"; } exit;

Instead of embedding the fetch method within a flow control statement, the results of the SQL statement using fetchall_arrayref( ) are stored in memory. A reference number to the location of those results is stored in the $books variable and the connection to MySQL is then closed. A foreach statement is employed to extract each reference to each array (i.e., each row) of the complex array. Each record's array is parsed into separate variables. The the values of the variables are displayed. Incidentally, to learn more about references, see Randal Schwartz's book Learning Perl Objects, References & Modules (O'Reilly).

This kind of batch processing of an SQL statement has the added advantage of allowing multiple SQL statements to be performed without them tripping over each other, while still performing complex queries. For instance, suppose that we want to get a list of books written by Henry James, ordered by title, then by publisher, and then by year. This is easy enough in MySQL. Suppose that we also want the inventory count of each title, bookstore by bookstore, with some address information to be displayed between the listing for each store. This becomes a little complicated. One way to do this is to use a SELECT statement that retrieves a list of store locations and their relevant information (i.e., their address and telephone number) and to save a reference to the data in memory. Then we could issue another SQL statement to retrieve the book inventory data and then close the MySQL connection. With a flow control statement, we could then print a store header followed by the store's relevant inventory information for each book before moving on to the next store. It would basically look like this:

   ...  # Start script and connect to MySQL     # Retrieve list of stores my $sql_stmnt = "SELECT store_id, store_name,                 address, city, state, telephone                 FROM stores"; my $sth = $dbh->prepare($sql_stmnt); $sth->execute( ); my $stores = $sth->fetchall_arrayref( ); $sth->finish( );     # Retrieve list of books my $sql_stmnt = "SELECT title, publisher,                  pub_year, store_id, quantity                 FROM books, inventory                 WHERE author = 'Henry James'                 AND books.book_id = inventory.book_id                 ORDER BY title, publisher, pub_year"; my $sth = $dbh->prepare($sql_stmnt); $sth->execute( ); my $books = $sth->fetchall_arrayref( ); $sth->finish( ); $dbh->disconnect( );     foreach my $store (@$stores){        my ($store_id, $store_name, $address,         $city, $state, $telephone) =        @$store;    print "$store_name\n           $address\n$city, $state\n           $telephone\n\n";        foreach my $book (@$books){          my ($title, $publisher,           $pub_year, $store, $qty) = @$book;      if($store ne $store_id) { next; }         print "$title ($publisher $pub_year) $qty\n";    } } exit;

To save space, I left out the opening lines for the script, because they are the same as in the previous script. In the first SQL statement here, we're selecting the store information. With the fetchall_arrayref( ) method, we're storing the reference for the data in $stores. If we were to print out this variable, we would see only a long number and not the actual data. Although an SQL statement may retrieve many rows of data, all of the data will be stored in memory. Therefore, we can issue finish() and, as long as we don't disconnect from MySQL, we can issue another SQL statement. The next SQL statement selects the book inventory information. Once this has been collected, the connection to MySQL is terminated and we can begin displaying the data with the use of flow control statements.

The first foreach statement loops through the data of each store and prints out the address information. Within each loop is another foreach loop for processing all of the titles for the particular store. Notice the if statement for the book inventory loop. The first record or array for the first store is read and the basic store information is displayed. Then the first array for the inventory is retrieved from its complex array and the elements parsed into variables. If store (which is the store_id) doesn't match the one that its on, Perl moves on to the next record. The result is that a store header is displayed and all of the inventory information requested is displayed for the store before going on to the next store's data.

You can accomplish this task in many ways some simpler and some tighter but this gives you a general idea of how to perform such a task, without keeping the connection to MySQL open while processing data. For more details on using the Perl DBI with MySQL, see Alligator Descartes and Tim Bunce's book, Programming the Perl DBI (O'Reilly).