MySQL Stored Procedure Programming (2009)

Part III. Using MySQL Stored Programs in Applications

Chapter 15. Using MySQL Stored Programs with Perl

Perl is an open source programming language widely used for system administration tasks, web site development, data manipulation, and reporting. Perl was the brainchild of Larry Wall, who initially developed the language to provide a language for the easy manipulation of text files and the like. Perl rapidly became very popular among the Unix community as a powerful, easy-to-use, general-purpose programming language. During the explosion of the World Wide Web, Perl's ease of use and database connectivity capabilities made it the preferred choice for CGI-based data-driven web sites.

From very early on, Perl was an extensible language and benefited greatly from a wide variety of user-contributed packages allowing it to do everything from handling Unix mail to performing complex statistical analyses. One category of extension showed particularly rapid uptake—extensions that enabled Perl to interact with relational databases, allowing Perl users to manipulate RDBMS data as easily as they could manipulate text files. Initially, these extensions were platform specific—the extension used to access Oracle had little in common with that used to access Sybase, for instance.

Perl's DBI (DataBase Interface) module evolved to provide a common syntax for interacting with relational databases. DBI defines interfaces and utilities common to all databases, while for each specific relational database, we use a DBD (DataBase Driver) module that contains the database-specific implementation of the DBI interface, and may also include database-specific utility routines. The preferred way to use MySQL with Perl is through the DBD::mysql module.

In this chapter we will first provide a general overview of DBD::mysql capabilities and then move on to show how to use DBD::mysql to call MySQL stored programs.

Review of Perl DBD::mysql Basics

Let's start with a review of how to install the DBD::mysql driver, and how to use that driver to perform traditional interactions (i.e., those not using stored programs) with MySQL. These form the building blocks that we can use to work with stored programs. However, if you are already familiar with the Perl DBI, you may wish to skip forward to "Executing Stored Programs with DBD::mysql," later in this chapter.

Installing DBD::mysql

To access MySQL from Perl, you will normally use the DBD::mysql package. DBD::mysql is a Perl package that implements the classes defined by the DBI package that allow Perl to interact with relational databases in a database-independent manner.

The DBI package is probably already included in your Perl distribution. If it is not, you can follow the instructions given in this section.

Tip

Make sure to install the DBI package before installing the DBD::mysql package.

Installing DBD::mysql on Linux or Unix

The easiest way to install DBD::mysql on a Linux/Unix system is to use the CPAN (Comprehensive Perl Archive Network) shell. To invoke the CPAN shell, run the following command from a command line (as root):

[root@guyh3 root]# perl -MCPAN -e 'shell'

This invokes the CPAN command line:

[root@guyh3 root]# perl -MCPAN -e 'shell'

cpan shell -- CPAN exploration and modules installation (v1.61)

ReadLine support enabled

cpan>

You can then type install DBD::mysql to download, build and install the DBD::mysql driver. It's probably best to specify force install, because otherwise the DBD::mysql driver will not install unless it has passed all the built-in tests. Unfortunately, the tests will probably fail if you have a nonstandard database password, so we generally use force install to ensure that the installation succeeds.

The CPAN install session will look something like this:

cpan> force install DBD::mysql

CPAN: Storable loaded ok

Going to read /root/.cpan/Metadata

Database was generated on Wed, 15 Jun 2005 11:57:49 GMT

Running install for module DBD::mysql

Running make for R/RU/RUDY/DBD-mysql-2.9008.tar.gz

CPAN: Digest::MD5 loaded ok

Checksum for /root/.cpan/sources/authors/id/R/RU/RUDY/DBD-mysql-2.9008.tar.gz ok

Scanning cache /root/.cpan/build for sizes

DBD-mysql-2.9008/

DBD-mysql-2.9008/t/

DBD-mysql-2.9008/t/60leaks.t

DBD-mysql-2.9008/t/40listfields.t

DBD-mysql-2.9008/t/10dsnlist.t

*** LOTS of other output ***

Failed 16/18 test scripts, 11.11% okay. 725/732 subtests failed, 0.96% okay.

make: *** [test_dynamic] Error 2

/usr/bin/make test -- NOT OK

Running make install

Installing /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/auto/DBD/mysql/

mysql.so

Files found in blib/arch: installing files in blib/lib into architecture dependent

library tree

Installing /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/mysql.pm

Installing /usr/share/man/man3/DBD::mysql.3pm

Writing /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/auto/DBD/mysql/.

packlist

Appending installation info to /usr/lib/perl5/5.8.0/i386-linux-thread-multi/

perllocal.pod

/usr/bin/make install -- OK

Installing DBD::mysql on Windows

If you are using Perl on Windows, you probably are using the ActiveState binary distribution (http://www.activestate.com). Activestate Perl includes the Perl Package Manager, which can be used to download binary versions of Perl packages from the ActiveState site. To use PPM you simply type ppm from a Windows command prompt. If you are working through a proxy server, you may need to set appropriate values for HTTP_proxy, HTTP_proxy_user, and HTTP_proxy_pass, as shown below:

C:\>set HTTP_proxy=http://something.proxy.com:8080

C:\>set HTTP_proxy_user=myusername

C:\>set HTTP_proxy_pass=mypassword

C:\>ppm

PPM interactive shell (2.1.6) - type 'help' for available commands.

PPM> install DBD::mysql

Install package 'DBD-mysql?' (y/N): y

Installing package 'DBD-mysql'...

Bytes transferred: 597532

Installing C:\Perl\site\lib\auto\DBD\mysql\mysql.bs

Installing C:\Perl\site\lib\auto\DBD\mysql\mysql.dll

Installing C:\Perl\site\lib\auto\DBD\mysql\mysql.exp

Installing C:\Perl\site\lib\auto\DBD\mysql\mysql.lib

Installing C:\Perl\html\site\lib\Mysql.html

Installing C:\Perl\html\site\lib\DBD\mysql.html

Installing C:\Perl\html\site\lib\DBD\mysql\INSTALL.html

Installing C:\Perl\html\site\lib\Bundle\DBD\mysql.html

Installing C:\Perl

\site\lib\Mysql.pm

Installing C:\Perl\site\lib\Mysql\Statement.pm

Installing C:\Perl\site\lib\DBD\mysql.pm

Installing C:\Perl\site\lib\DBD\mysql\GetInfo.pm

Installing C:\Perl\site\lib\DBD\mysql\INSTALL.pod

Installing C:\Perl\site\lib\Bundle\DBD\mysql.pm

Writing C:\Perl\site\lib\auto\DBD\mysql\.packlist

Connecting to MySQL

To connect to MySQL from a Perl program, we first need to issue the use DBI clause to load the DBI driver that forms the foundation for the DBD::mysql driver. We then create a database handle using the DBI->connect() method.

The connect method has the following syntax:

Database_handle=DBI->connect(DataSourceName,UserName,PassWord,[Attributes]);

The resulting database handle is used in all subsequent interactions with the database.

The DataSourceName specifies the database details for the connection. The syntax depends on the type of database used, but for MySQL it has the following format:

dbi:mysql:database:host:port

where hostname indicates the hostname or IP address of the machine hosting the MySQL instance, port defines the port on which the MySQL server is listening (3306 by default), and database specifies the database within the server to which the connection is being made.

Attributes defines some optional attributes for the connection; we'll discuss attributes in the next section.

In Example 15-1 we connect to a database prod on the MySQL server on the local machine localhost at port 3306. We connect as root with the password secret.

Example 15-1. Connecting to a MySQL database from Perl

use strict;

use DBI;

my $dbh = DBI->connect( "DBI:mysql:prod:localhost:3306", "root", "secret" );

Connection attributes

DBD:MySQL allows you to specify the following attributes at connection time:

AutoCommit

Determines whether each SQL statement will automatically commit following execution. This is relevant only for transactional databases such as InnoDB.

PrintError

Determines whether MySQL errors will be printed as warnings.

RaiseError

Determines whether MySQL errors will terminate execution.

These attributes are represented as an associative array within the connect() method, and each takes an argument of either 1 (true) or 0 (false). Example 15-2 shows how to set up a connection in which automatic commits are suppressed and in which any errors encountered are reported without terminating execution.

Example 15-2. Setting database handle attributes on connection

my $dbh = DBI->connect( "DBI:mysql:prod:localhost:3306",

"root", "secret", { AutoCommit => 0, PrintError => 1, RaiseError => 0 } )

You can modify any of these database handle attributes during execution, as shown in Example 15-3.

Example 15-3. Enabling autocommit

$dbh->{AutoCommit} = 1; #Enable autocommit

Handling Errors

As shown earlier, we can set up some basic error-handling defaults at connection time that will control whether MySQL errors cause immediate termination of a program. However, we will often want to check the error status of a DBD::mysql call immediately after execution and take appropriate action if the call fails.

Usually, a DBI method will return true if it is successful, or false otherwise, and so we can check that return status to determine whether the call was successful, as shown in Example 15-4. Details about the actual status of execution can be found in the err and errstr properties of the database handle. These properties can be used to determine the root cause of the error or to report the error to the user.

Example 15-4. Checking for errors in a DBI statement

my $dbh = DBI->connect( "DBI:mysql:prod:localhost:3306",

"root", "secret", { AutoCommit => 0, PrintError => 0, RaiseError => 0 } )

|| die "Connection error: ".$DBI::errstr;

Issuing a Simple One-off Statement

The DBI do() method allows us to execute a simple statement that returns no result sets and takes no parameters. Example 15-5 shows the use of the do() method to set the value for a user variable.

Example 15-5. Using do( ) to execute a simple SQL

$dbh->do('set @myvariable=10')||die $DBI::errstr;

Preparing a Statement for Reuse

To execute a statement more than once, or to execute a SQL statement that retrieves a result set, we first need to prepare, and then execute, the statement. Example 15-6 shows the use of prepare() and execute() rather than do() to execute a simple SQL statement.

Example 15-6. Using prepare( ) and execute( )

my $sth=$dbh->prepare('set @myvariable=9')||die $DBI::errstr;

$sth->execute||die $DBI::errstr;

Using Bind Variables

One of the advantages of using prepared statements is that they can be re-executed with altered parameters without having to be redefined each time. Bind variables— also known as substitution variables —are indicated within a SQL statement by ? placeholders. Prior to execution, we call the bind_param() method to set the values of these variables.

In Example 15-7 we prepare a statement and then bind and execute() the statement 10 times in a loop. Each execution inserts unique rows into the appropriate table.

Example 15-7. Using bind_param( ) to set placeholder values

my $sth=$dbh->prepare('INSERT INTO bind_example(col1,col2) VALUES(?,?)')

||die $DBI::errstr;

for (my $i=1; $i<=10;$i++) {

$sth->bind_param(1,$i);

$sth->bind_param(2,'Row# '.$i);

$sth->execute||die $DBI::errstr;

}

$sth->finish;

Alternatively, we can specify the bind variables in the execute method, as shown in Example 15-8.

Example 15-8. Specifying bind values in the execute( ) method

my $sth = $dbh->prepare('INSERT INTO bind_example(col1,col2) VALUES(?,?)')

|| die $DBI::errstr;

for ( my $i = 1 ; $i <= 10 ; $i++ ) {

my $col2_value = 'Row2#' . $i;

$sth->execute( $i, $col2_value ) || die $DBI::errstr;

}

Issuing a Query and Retrieving Results

In line with the core philosophy of Perl —There's More Than One Way To Do It?— Perl DBI and the DBD::mysql driver provide a number of ways to retrieve rows from a query. In Example 15-9, we use the fetchrow_array method, which is probably the most commonly used approach.

Example 15-9. Retrieving rows with fetchrow_array

my $sql =

"SELECT customer_id,customer_name FROM customers WHERE sales_rep_id=1";

my $sth = $dbh->prepare($sql) || die $DBI::errstr;

$sth->execute || die $DBI::errstr;

while ( my @row = $sth->fetchrow_array ) {

print $row[0] ."\t". $row[1] . "\n";

}

$sth->finish;

After we have prepared and executed a SQL statement that returns a result set (SELECT, SHOW STATUS, etc.), we can use the fetchrow_array method to retrieve each row into a Perl array. We can then refer to the column values as numbered elements in that array (starting with element 0, of course!).

There's More Than One Way To Do It

Perl DBI offers at least five other ways of retrieving rows from a statement handle, described in the following subsections.

fetchrow_arrayref method

The fetchrow_arrayref method, shown in Example 15-10, is similar in usage to fetchrow_array, and has the advantage of returning a reference to an array, rather than the array itself. This has a small positive impact on performance for each row, since the data is not copied into a new array.

Example 15-10. Retrieving rows with fetchrow_arrayref

my $sql =

"SELECT customer_id,customer_name FROM customers WHERE sales_rep_id=1";

my $sth = $dbh->prepare($sql) || die $DBI::errstr;

$sth->execute || die $DBI::errstr;

while ( my $row_ref = $sth->fetchrow_arrayref ) {

print $row_ref->[0]."\t".$row_ref->[1]."\n";

}

$sth->finish;

fetchrow_hashref method

The fetchrow_hashref method, shown in Example 15-11, returns the row as an associative array in which each element of the array is keyed by the column name, rather than the column position. This has the advantage of improving readability, although you have to know the column names that will be returned by the query.

Example 15-11. Retrieving rows with fetchrow_hashref

my $sql =

"SELECT customer_id,customer_name FROM customers WHERE sales_rep_id=1";

my $sth = $dbh->prepare($sql) || die $DBI::errstr;

$sth->execute || die $DBI::errstr;

while ( my $hash_ref = $sth->fetchrow_hashref ) {

print $hash_ref->{customer_id} . "\t" .

$hash_ref->{customer_name} . "\n";

}

$sth->finish;

fetchall_arrayref method

The fetchall_arrayref method allows you to retrieve an entire result set in a single operation. For noninteractive applications where the result set can fit into available memory, this can be a very efficient way to retrieve a result set. However, it is not necessarily appropriate for interactive applications where the user may wish to view only the first page of data before looking at the rest (for instance, on a web search page you rarely scroll through the entire list of matching sites). If the result set is too large for available memory, this method may degrade overall system performance as memory is swapped out to disk.

There are two main modes for the fetchall_arrayref method. In the first and simplest case, shown in Example 15-12, no arguments are provided to the method, and the method passes a reference to an array. Each element in the array contains references to an array containing the column values for a particular row.

Example 15-12. Retrieving rows with fetchall_arrayref

my $sql =

"SELECT customer_id,customer_name FROM customers WHERE sales_rep_id=1";

my $sth = $dbh->prepare($sql) || die $DBI::errstr;

$sth->execute || die $DBI::errstr;

my $table = $sth->fetchall_arrayref||die $DBI::errstr;

for my $i ( 0 .. $#{$table} ) {

for my $j ( 0 .. $#{ $table->[$i] } ) {

print "$table->[$i][$j]\t";

}

print "\n";

}

Providing {} as the argument to fetchall_arrayref returns the columns as hashes, indexed by column name. In Example 15-13, we repeat our previous query but access our columns as hash references.

Example 15-13. Using fetchall_arrayref, returning hash references

my $sql =

"SELECT customer_id,customer_name FROM customers WHERE sales_rep_id=1";

my $sth = $dbh->prepare($sql) || die $DBI::errstr;

$sth->execute || die $DBI::errstr;

my $table = $sth->fetchall_arrayref({}) || die $DBI::errstr;

foreach my $row (@$table) {

print $row->{customer_id} . "\t" . $row->{customer_name} . "\n";

}

You can also provide array or hash slice references as an argument to fetchall_arrayref to restrict the columns returned.

dump_results method

The dump_results method provides a quick-and-dirty way to print the output of a query. By default, dump_results will output all of the rows from a statement handle to standard output, surrounding the values in quotes, separating with commas, terminating each row with a line feed, and truncating columns (if necessary) to a maximum of 35 bytes per value. These default behaviors can be changed by providing arguments to dump_results:

my $Rowcount=$statement_handle->dump_results(

[column_length],[line separator],[column separator],[file handle]);

Example 15-14 shows dump_results in action.

Example 15-14. Using dump_results to display a result set

my $sql =

"SELECT customer_id,customer_name FROM customers WHERE sales_rep_id=1";

my $sth = $dbh->prepare($sql) || die $DBI::errstr;

$sth->execute || die $DBI::errstr;

my $row_count = $sth->dump_results;

$sth->finish;

The output of dump_results is shown in Example 15-15.

Example 15-15. Output from dump_results

'398', 'BELL INDUSTRIES INC.', 'DAHL', 'PHILIPPA'

'2985', 'GEORGIA-PACIFIC CORPORATION', 'OBRIEN', 'DOYLE'

'4776', 'CFC INTERNATIONAL INC', 'KINDRED', 'TOM'

'8756', 'INFODATA SYSTEMS INC', 'WEATHERFORD', 'KRISTIE'

'10746', 'ADTRAN INC.', 'EATON', 'RAYBURN'

bind_col and fetch methods

The final method we're going to look at differs from all the preceeding techniques: instead of the fetch( ) method returning an array or a reference to an array, we associate Perl variables ahead of time to each column that will be returned by the query. We perform this association with thebind_col method. Then we call the fetch method, which automatically deposits the values of the columns concerned into the variables nominated earlier. The Perl variables must be passed by reference (preceded by a \ character), which results in a theoretical performance advantage.

Example 15-16 provides an example of using this technique.

Example 15-16. Using bind_col and fetch( ) to retrieve data from a query

my ( $customer_id, $customer_name );

my $sql =

"SELECT customer_id,customer_name FROM customers WHERE sales_rep_id=1";

my $sth = $dbh->prepare($sql) || die $DBI::errstr;

$sth->execute || die $DBI::errstr;

$sth->bind_col( 1, \$customer_id );

$sth->bind_col( 2, \$customer_name );

while ( $sth->fetch ) {

print join( "\t", ( $customer_id, $customer_name ) ), "\n";

}

Getting Result Set Metadata

We don't necessarily always know the exact structure of the result set that will be returned by a SQL statement: the SQL might have been built up dynamically or even supplied by the user. To allow for this possibility, DBI lets us retrieve details about the result set using attributes of the statement handle. The NUM_OF_FIELDS statement handle attribute returns the number of columns in the result set, while the NAME and TYPE attributes are arrays containing the names and data types of each column.

Example 15-17 shows how we can use these attributes to print out the structure of a result set.

Example 15-17. Retrieving result-set metadata

my $sth = $dbh->prepare($sql) || die $DBI::errstr;

$sth->execute || die $DBI::errstr;

foreach my $colno ( 0 .. $sth->{NUM_OF_FIELDS} - 1 ) {

print "Name= "

. $sth->{NAME}->[$colno]

. "\tType="

. $sth->{TYPE}->[$colno] . "\n";

}

These attributes let us write code that can handle dynamically any result set that might be returned. For instance, the code in Example 15-18 will print the result set returned from a SQL statement contained within the $sql variable, without knowing in advance the structure of the result set that SQL might return.

Example 15-18. Handling a dynamic result set

1 my $sth = $dbh->prepare($sql) || die $DBI::errstr;

2 $sth->execute || die $DBI::errstr;

3

4 # Print a title row

5 print join("\t",@{$sth->{NAME}}),"\n";

6

7 # Print out the values

8 while ( my @row = $sth->fetchrow_array ) {

9 print join("\t",@row),"\n";

10 }

11 $sth->finish;

Let's examine this example line by line:

Performing Transaction Management

If you're using a transactional storage engine such as InnoDB, you may want to implement transactional logic within your Perl code. While you can do that by issuing the MySQL START TRANSACTION, ROLLBACK , and COMMIT statements with the DBI do() method, DBI provides some native routines that might be more convenient.

The AutoCommit attribute of the connection handle can be set to 0 to disable automatic commits after each statement, while the rollback() and commit() methods of the connection handle can be used to explicitly roll back or commit transactions.

Example 15-19 uses these methods to control transaction logic in a simple Perl script.

Example 15-19. DBI transaction management commands in action

$dbh->{AutoCommit} = 0;

$dbh->do(

"UPDATE account_balance

SET balance=balance-$tfer_amount

WHERE account_id=$from_account"

);

if ($DBI::err) {

print "transaction aborted: ".$DBI::errstr . "\n";

$dbh->rollback;

}

else {

$dbh->do(

"UPDATE account_balance

SET balance=balance+$tfer_amount

WHERE account_id=$to_account"

);

if ($DBI::err) {

print "transaction aborted: ".$DBI::errstr . "\n";

$dbh->rollback;

}

else {

printf("transaction succeeded\n");

$dbh->commit;

}

}

Executing Stored Programs with DBD::mysql

We can use the techniques we've discussed in the previous sections for executing stored programs, although there are some circumstances in which you will need to use some additional techniques—specifically, if you need to retrieve multiple result sets or retrieve the value of an output parameter.

To execute a simple, one-off stored procedure that returns no result sets, we can simply invoke it with the do() method of the database handle, as shown in Example 15-20.

Example 15-20. Executing a very simple stored procedure

my $sql = 'call simple_stored_proc( )';

$dbh->do($sql)||die $DBI::errstr;

Stored procedures that return only a single result set can be treated in the same manner as simple SELECT statements. Example 15-21 shows a stored procedure that returns just one result set.

Example 15-21. Simple stored procedure with a result set

CREATE PROCEDURE department_list( )

SELECT department_name,location from departments;

Example 15-22 shows how we would retrieve that result set in Perl. The approach is exactly the same as the one we would use for a SELECT statement or other SQL that returns a result set.

Example 15-22. Fetching a single result set from a stored procedure

my $sth = $dbh->prepare('call department_list( )') || die $DBI::errstr;

$sth->execute || die $DBI::errstr;

while ( my @row = $sth->fetchrow_array ) {

print join("\t",@row),"\n";

}

$sth->finish;

Input parameters can be treated in the same way as placeholders in standard SQL. Input parameters are indicated in the prepare statement as ? characters, and the values are set using the bind_param method.

Example 15-23 shows a simple stored procedure that accepts an input parameter.

Example 15-23. Simple stored procedure with an input parameter

CREATE PROCEDURE customer_list(in_sales_rep_id INTEGER)

SELECT customer_id,customer_name

FROM customers

WHERE sales_rep_id=in_sales_rep_id;

In Example 15-24 we use bind_param to set that value before executing the stored procedure and retrieving the result set. The example executes the stored procedure nine times, supplying 1-9 for the sales_rep_id parameter.

Example 15-24. Specifying an input parameter

my $sth = $dbh->prepare('call customer_list(?)') || die $DBI::errstr;

for ( my $sales_rep_id = 1 ; $sales_rep_id < 10 ; $sales_rep_id++ ) {

print "Customers for sales rep id = " . $sales_rep_id;

$sth->execute($sales_rep_id) || die $DBI::errstr;

while ( my @row = $sth->fetchrow_array ) {

print join( "\t", @row ), "\n";

}

}

$sth->finish;

Handling Multiple Result Sets

Since stored procedures may return multiple result sets , DBI provides a method—more_results—to move to the next result set in a series. The DBD::mysql driver implementation of this method was still experimental at the time of writing (it is available in developer releases 3.0002.4 and above). We'll keep you updated on the status of DBD::mysql at this book's web site (see the Preface for details).

Example 15-25 shows a simple stored procedure that returns two result sets.

Example 15-25. Stored procedure with two result sets

CREATE PROCEDURE sp_rep_report(in_sales_rep_id int)

BEGIN

SELECT employee_id,surname,firstname

FROM employees

WHERE employee_id=in_sales_rep_id;

SELECT customer_id,customer_name

FROM customers

WHERE sales_rep_id=in_sales_rep_id;

END

Because we know in advance the number and structure of the result sets returned by the stored procedure, it is relatively simple to process the results. In Example 15-26, we simply retrieve the first result set as usual, call more_results, and then process the next result set.

Example 15-26. Fetching two result sets from a stored procedure

my $sth = $dbh->prepare("CALL sp_rep_report(?)") || die $DBI::errstr;

$sth->execute($sales_rep_id) || die $DBI::errstr;

# first result set: employee_id,surname,firstname

print 'Employee_id' . "\t" . 'Surname' . "\t" . 'Firstname' . "\n";

while ( my $row = $sth->fetchrow_hashref ) {

print $row->{employee_id} . "\t" .

$row->{surname} . "\t" .

$row->{firstname} . "\n";

}

$sth->more_results;

# second result set: customer_id,customer_name

print 'Customer_id' . "\t" . 'Customer Name' . "\n";

while ( my $row = $sth->fetchrow_hashref ) {

print $row->{customer_id} . "\t" . $row->{customer_name} . "\n";

}

$sth->finish;

Handling Dynamic Result Sets

A stored program can return a variable number of result sets, and the structure and number of those result sets can be unpredictable. To process the output of such stored programs, we need to combine the more_results method with the DBI attributes that contain result set metadata; these were outlined in the earlier section "Getting Result Set Metadata." The more_results method returns false if there are no further result sets, so we can continue to call more_results until all of the result sets have been processed. Example 15-27 illustrates this technique.

Example 15-27. Dynamically processing multiple result sets

1 sub execute_procedure( ) {

2 my ( $dbh, $stored_procedure_call ) = @_;

3 my $sth = $dbh->prepare($stored_procedure_call)

4 || die $DBI::err . ": " . $DBI::errstr;

5 $sth->execute || die DBI::err . ": " . $DBI::errstr;

6 my $result_set_no = 0;

7

8 do {

9 print "\n", ( '=' x 20 ) . " Result Set # ",

10 ++$result_set_no . ( '=' x 20 ), "\n\n";

11

12 print join( "\t", @{ $sth->{NAME} } ),"\n", ( '-' x 54 ), "\n";

13

14 while ( my @row = $sth->fetchrow_array( ) ) {

15 print join( "\t", @row ), "\n";

16 }

17 }until ( !$sth->more_results );

18 }

Let's step through this code:

Handling Output Variables

A stored procedure may contain OUT or INOUT parameters that can return individual scalar values from the stored procedure call. The DBI specification provides the bind_param_inout method for retrieving the values of such parameters. Unfortunately, this method is not implemented in the DBD::mysql driver as we write this—we'll keep you posted on the status of this method for MySQL at the book's web site.

Luckily, we don't need the bind_param_inout method to retrieve the value of an output parameter. We can pass in a user variable (see Chapter 3) to receive the output parameter value, and then select the value of that variable in a subsequent SELECT. Example 15-28 shows an example of this technique as an alternative to using bind_param_inout.

Example 15-28. Retrieving an output parameter without the bind_param_inout method

my $sql =

'call sp_rep_customer_count(1,@customer_count)'; #watch out for the "@"!

my $sth = $dbh->prepare($sql);

$sth->execute( ) || die $DBI::errstr;

$sth->finish;

# Now get the output variable

my @result = $dbh->selectrow_array('SELECT @customer_count')

|| die $DBI::errstr;

print "customer_count=", $result[0], "\n";

Watch out when creating strings that include user variables in Perl. By default, the @ symbol indicates a Perl array and—if the @ appears in a double-quoted string—Perl will attempt to replace the apparent array with a Perl value. So you should always include these types of strings in single quotes or escape the user variable reference by preceding the @ symbol with "\" (e.g., SELECT \@user_var).

Also, remember that if the stored program includes any result sets, you must process all of these result sets before attempting to retrieve the values of an output parameter.

A Complete Example

In this section we'll put all of the techniques we have described so far into an example procedure that implements a simple web-based MySQL server status display. The example will prompt the user for MySQL server details and return selected status information about that server. The information will be provided by a single stored program that returns multiple result sets and includes both input and output parameters.

The stored procedure is shown in Example 15-29. The stored procedure returns, as result sets, the output of various SHOW statements and—if a valid database name is provided as an input parameter—details about objects in that particular database. The server version is returned as an output parameter.

Example 15-29. Stored procedure that generates an employee report

CREATE PROCEDURE sp_mysql_info

(in_database VARCHAR(60),

OUT server_version VARCHAR(100))

READS SQL DATA

BEGIN

DECLARE db_count INT;

SELECT @@version

INTO server_version;

SELECT 'Current processes active in server' as table_header;

SHOW full processlist;

SELECT 'Databases in server' as table_header;

SHOW databases;

SELECT 'Configuration variables set in server' as table_header;

SHOW global variables;

SELECT 'Status variables in server' as table_header;

SHOW global status;

/* See if there is a matching database */

SELECT COUNT(*)

INTO db_count

FROM information_schema.schemata s

WHERE schema_name=in_database;

IF (db_count=1) THEN

SELECT CONCAT('Tables in database ',in_database) as table_header;

SELECT table_name

FROM information_schema.tables

WHERE table_schema=in_database;

END IF;

END;

To help us generate a well-formatted report, the stored procedure outputs a header row for each of the result sets it returns. This header row is issued as a single-row, single-column result set in which the column name is table_header.

Our Perl example is contained in Example 15-30. This is a Perl CGI script, designed to be run from the "CGI bin" directory of a web server such as Apache or Microsoft IIS. The program generates HTML to prompt for user input, connects to MySQL, runs the stored procedure, and generates the HTML to output the results.

Example 15-30. Perl CGI program to display server status information

1 #!/usr/bin/perl

2 use CGI qw(:standard);

3 use HTML::Table;

4 use DBI;

5 use strict;

6 if ( !param( ) ) {

7 my $form_tbl = new HTML::Table( );

8 $form_tbl->addRow( "Hostname:", textfield( 'hostname', 'localhost' ) );

9 $form_tbl->addRow( "Username:", textfield( 'username', 'root' ) );

10 $form_tbl->addRow( "Password:", password_field('password') );

11 $form_tbl->addRow( "Database:", textfield('database') );

12 $form_tbl->addRow( "Port:", textfield( 'port', 3306 ) );

13 print header, start_html('MySQL Server Status'),

14 h1('Enter MySQL Server details'), start_form, $form_tbl->getTable,

15 submit,end_form, hr;

16 }

17 else {

18 my $hostname = param('hostname');

19 my $username = param('username');

20 my $password = param('password');

21 my $db = param('database');

22 my $port = param('port');

23 my @html_body;

24

25 my $dbh = DBI->connect( "DBI:mysql:$db:$hostname:$port",

26 "$username", "$password", { PrintError => 0 } );

27 if (DBI::err) {

28 print header, start_html("Error"), $DBI::errstr;

29 }

30 else {

31 my $sth = $dbh->prepare('call sp_mysql_info(?,@server_version)')

32 || die $DBI::err . ": " . $DBI::errstr;

33 $sth->bind_param( 1, $db );

34 $sth->execute || die DBI::err . ": " . $DBI::errstr;

35 do {

36 if ($sth->{NAME}->[0] eq "table_header" ) {

37 my @row = $sth->fetchrow_array( );

38 push( @html_body, h2( $row[0] ), p );

39 }

40 else {

41 my $table = new HTML::Table( );

42 $table->setBorder(1);

43 foreach my $colno ( 0 .. $sth->{NUM_OF_FIELDS} ) {

44 $table->setCell( 1, $colno + 1, $sth->{NAME}->[$colno] );

45 $table->setCellBGColor( 1, $colno + 1, "silver" );

46 }

47 my $rowno = 1;

48 while ( my @row = $sth->fetchrow_array( ) ) {

49 $rowno++;

50 foreach my $colno ( 0 .. $#row ) {

51 $table->setCell( $rowno, $colno + 1, $row[$colno] );

52 }

53 }

54 push( @html_body, $table->getTable );

55 }

56 } until ( !$sth->more_results );

57

58 $sth = $dbh->prepare('SELECT @server_version') || die $DBI::errstr;

59 $sth->execute( ) || die $DBI::errstr;

60 my @row = $sth->fetchrow_array( );

61 my $mysql_version = $row[0];

62

63 print header, start_html('MySQL Server Status'),

64 h1('MySQL Server Status');

65 print "<b>Server: </b>", $hostname, br, "<b>Port: </b>", $port, br,

66 "<b>Database:</b>", $db, br "<b>Version:</b>", $mysql_version, br;

67 for my $html (@html_body) {

68 print $html;

69 }

70 print end_html;

71 }

72 }

Let's step through this example:

This Perl program first generates the HTML input form, as shown in Figure 15-1.

When the user clicks the Submit button, the CGI Perl script generates output, as shown in Figure 15-2.

Figure 15-1. Input form for our example

Conclusion

In this chapter we reviewed the Perl DBD::mysql package, which allows Perl to connect to MySQL databases. We also showed how to use DBD::mysql to interact with MySQL stored procedures. Perl provides all of the mechanisms necessary for stored procedure processing, although some of these mechanisms were experimental as we wrote this chapter. We'll keep you updated with the status of these extensions at this book's web site.

Figure 15-2. Output from our CGI example