Executing Stored Programs with DBD::mysql

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 techniquesspecifically, 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("	",@row),"
";
 }
 $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( "	", @row ), "
";
 }
 }
 $sth->finish;

15.2.1. Handling Multiple Result Sets

Since stored procedures may return multiple result sets , DBI provides a methodmore_resultsto 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' . "	" . 'Surname' . "	" . 'Firstname' . "
";
 while ( my $row = $sth->fetchrow_hashref ) {
 print $row->{employee_id} . "	" .
 $row->{surname} . "	" .
 $row->{firstname} . "
";
 }

 $sth->more_results;

 # second result set: customer_id,customer_name
 print 'Customer_id' . "	" . 'Customer Name' . "
";
 while ( my $row = $sth->fetchrow_hashref ) {
 print $row->{customer_id} . "	" . $row->{customer_name} . "
";
 }
 $sth->finish;

15.2.2. 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 "
", ( '=' x 20 ) . " Result Set # ",
10 ++$result_set_no . ( '=' x 20 ), "

";
11
12 print join( "	", @{ $sth->{NAME} } ),"
", ( '-' x 54 ), "
";
13
14 while ( my @row = $sth->fetchrow_array( ) ) {
15 print join( "	", @row ), "
";
16 }
17 }until ( !$sth->more_results );
18 }

Let's step through this code:

Lines	Explanation
17	Here we define our subroutine, and have it extract a database connection handle ($dbh) and stored procedure call from the parameters passed to the procedure. The stored procedure call is prepared and executed (lines 35).
817	Specify an until loop that will execute until more_results returns false. This loop will execute at least once.
9 and 10	This statement prints a "divider" line to separate each result set returned by the stored procedure.
12	Print out the column names for the current result set.
1416	Loop through the rows in the current result set by calling fetchrow_array to retrieve rows until all rows have been processed.
15	Print the column values for the current row and print each column value.
17	Call more_results to move to the next result set. If more_results returns false, then there are no more result sets to be retrieved and the loop will terminate.

 

15.2.3. 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 thiswe'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], "
";

Watch out when creating strings that include user variables in Perl. By default, the @ symbol indicates a Perl array andif the @ appears in a double-quoted stringPerl 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.

15.2.4. 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 andif a valid database name is provided as an input parameterdetails 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 "Server: ", $hostname, br, "Port: ", $port, br,
66 "Database:", $db, br "Version:", $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:

Line(s)	Explanation
14	Define the path to the Perl executablenecessary for CGI programs and import the Perl packages we are going to use. These packages include the Perl CGI module that assists with HTML formatting, the HTML::Table package to assist us with our HTML tables, and of coursethe DBI package to allow database connectivity.
616	Create the HTML input form as shown in Figure 15-1. Lines 712 create an HTML table that contains our input fields, while lines 1315 print titles and other HTML. All HTML is generated by the CGI package.
1772	Executed once the user clicks the Submit button on our HTML form.
1822	Retrieve the values the user entered on the input form and assign them to Perl variables.
2529	Using the inputs provided by the user, establish a connection to the MySQL database.
3134	Prepare the stored procedure call, bind the database name provided by the user as the first parameter, and execute the stored procedure.
3556	Execute once for each result set returned by the stored procedure.
3639	If the result set contains a column called table_header, then the result set is treated as a title heading for a subsequent result set, and so we generate an H2 heading row. All HTML output is added to the @html_body array to be printed once we have retrieved all result sets and the value for the output variable.
4146	If the result set does not represent a heading, then we initialize an HTML table to display the results. Here we create the heading row for the HTML table. Lines 4346 loop through the column names in the result set and create a corresponding HTML table heading.
4853	Loop through the rows in the result set and generate HTML table rows. The loop commencing on line 48 iterates through each row, and the loop commencing on line 50 iterates through each column in each row. Line 51 sets the value for a specific row/column combination.
54	Add the HTML for our table to the @html_body array.
56	The until clause controls the execution of the loop that commenced on line 35. While the more_results call returns true, indicating that there are more result sets, the loop will continue to execute.
5861	Now that all result sets have been processed, we can retrieve the value of the output parameter. When we prepared the stored procedure on line 31, we provided a user variable'@server_version'to receive the value of the output parameter. Now we issue a SELECT statement to get the value of that variable.
6366	Having retrieved all the result sets and having retrieved the output parameter, we can generate the HTML output. These lines print the heading and server details (including the server version).
6769	Output the HTML that we have accumulated into the @html_body array during our program execution. This includes header rows and HTML tables constructed in our main loop.
70	This completes our HTML output and our Perl 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