Review of Perl DBD::mysql Basics
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.
15.1.1. 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.
|
15.1.1.1. 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
15.1.1.2. 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:PerlsitelibautoDBDmysqlmysql.bs Installing C:PerlsitelibautoDBDmysqlmysql.dll Installing C:PerlsitelibautoDBDmysqlmysql.exp Installing C:PerlsitelibautoDBDmysqlmysql.lib Installing C:PerlhtmlsitelibMysql.html Installing C:PerlhtmlsitelibDBDmysql.html Installing C:PerlhtmlsitelibDBDmysqlINSTALL.html Installing C:PerlhtmlsitelibBundleDBDmysql.html Installing C:Perl sitelibMysql.pm Installing C:PerlsitelibMysqlStatement.pm Installing C:PerlsitelibDBDmysql.pm Installing C:PerlsitelibDBDmysqlGetInfo.pm Installing C:PerlsitelibDBDmysqlINSTALL.pod Installing C:PerlsitelibBundleDBDmysql.pm Writing C:PerlsitelibautoDBDmysql.packlist
15.1.2. 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" ); |
15.1.2.1. 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 |
15.1.3. 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; |
15.1.4. 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; |
15.1.5. 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; |
15.1.6. 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; } |
15.1.7. 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] ." ". $row[1] . " "; } $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!).
15.1.8. 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.
15.1.8.1. 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]." ".$row_ref->[1]." "; } $sth->finish; |
15.1.8.2. 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} . " " . $hash_ref->{customer_name} . " "; } $sth->finish; |
15.1.8.3. 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] "; } print " "; } |
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} . " " . $row->{customer_name} . " "; } |
You can also provide array or hash slice references as an argument to fetchall_arrayref to restrict the columns returned.
15.1.8.4. 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' |
15.1.8.5. 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 the bind_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( " ", ( $customer_id, $customer_name ) ), " "; } |
15.1.9. 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] . " Type=" . $sth->{TYPE}->[$colno] . " "; } |
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(" ",@{$sth->{NAME}})," "; 6 7 # Print out the values 8 while ( my @row = $sth->fetchrow_array ) { 9 print join(" ",@row)," "; 10 } 11 $sth->finish; |
Let's examine this example line by line:
|
Line(s) |
Explanation |
|---|---|
|
5 |
Print the names of each column in the result setseparated by tab charactersas a header row. |
|
8-10 |
This loop repeats once for each row in the result set. |
|
9 |
Print out a tab-separated list of column values for a particular row. |
15.1.10. 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 . " "; $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 . " "; $dbh->rollback; } else { printf("transaction succeeded "); $dbh->commit; } } |