MySQL in a Nutshell (2008)
Part IV. APIs and Connectors
Chapter 18. Perl API
The easiest method of connecting to MySQL with the programming language Perl is to use the Perl DBI module, which is part of the core Perl installation. You can download both Perl and the DBI module from CPAN (http://www.cpan.org). I wrote this chapter with the assumption that the reader has Perl installed along with DBI.pm and that the reader has a basic knowledge of Perl. Its focus, therefore, is on how to connect to MySQL, run SQL statements, and effectively retrieve data from MySQL using Perl and DBI. This chapter begins with a tutorial on using Perl with MySQL. That’s followed by a list of Perl DBI methods and functions used with MySQL, with the syntax and descriptions of each and examples for most. The examples here use the scenario of a bookstore’s inventory.
Using Perl DBI with MySQL
This section presents basic tasks that you can perform with Perl DBI. It’s meant as a simple tutorial for getting started with the Perl DBI and MySQL.
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 program:
#!/usr/bin/perl -w
use strict;
use DBI;
my $dbh = DBI->connect ("DBI:mysql:bookstore:localhost","russell",
"my_pwd1234")
or die "Could not connect to database: "
. DBI->errstr;
The first two lines start Perl and set a useful condition for reducing programming 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 program 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.
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 can be executed through the API. Continuing the previous example and using a fictitious database of a bookstore, let’s look at how an SQL statement that retrieves a list of books and their authors from a table containing that information might look:
my $sql_stmnt = "SELECT title, author FROM books";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();
The first line 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.
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, MySQL returns rows one at a time and they are usually processed 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 whilestatement 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—and overwritten with each loop. Then the variables are printed to the screen with a newline character after each pair.
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 a good practice to have programs 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, 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 program.
Temporarily Storing Results
Perhaps a method of retrieving data from MySQL that’s cleaner than the one just explained involves capturing all of the data in memory for later use in a program, 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 program, especially when dealing with large amounts of data. 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 (an array of rows of data), and returns the array’s starting location in memory. Here is a Perl program 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 containing rows (arrays)
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. Aforeach statement is employed to extract each reference to each array (i.e., each row, each $book) of the complex array. Each record’s array is parsed into separate variables ($title and $author). The values of the variables are displayed using print. Incidentally, to learn more about references, see Randal Schwartz’s book, Intermediate Perl (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 addresses and telephone numbers) and to save a reference to the data in memory. Next, 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 program 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
JOIN inventory USING(book_id)
WHERE author = 'Henry James'
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 program because they are the same as in the previous program. 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. In the SELECT statement we’re hardcoding in the author’s name. We really should replace that with a variable (e.g., $author) and allow the user to set the variable earlier in the program. Once the book inventory information 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 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 it’s 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 Perl goes 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 it, 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).
Perl DBI Reference
The following is a list of DBI methods and functions in alphabetical order. The syntax and an explanation of each as well as examples for most are provided. However, to save space, the examples are only excerpts and are missing some components, such as the calling of the DBI module and the creation of a database handle. Also, to focus on the particular method or function described, we’ll use a very simple table containing a list of books and the names of their authors with the same SELECT statement. See the previous section (the tutorial) for an example of a complete, albeit simple, Perl DBI program. In addition to passing parameters, you can affect the behavior of several methods by setting global values called attributes. See the end of this chapter for a list of attributes.
Name
available_drivers()
Synopsis
DBI->available_drivers([nowarn])
This function returns a list of available DBD drivers. You can suppress any warning messages by providing the text nowarn as an argument. Here is an example:
...
my @drivers = DBI->available_drivers();
foreach my $driver(@drivers) {
print "$driver \n";
}
Name
begin_work()
Synopsis
$dbh->begin_work()
This funciton is used for transactions with a database. It temporarily turns AutoCommit off until commit() or rollback() is run. There are no arguments to this database handle method. In MySQL, this is similar to executing the SQL statement BEGIN or BEGIN WORK. It will only be effective with a transactional storage engine like InnoDB. At the time of this writing, there is a bug in this function: it returns an error if AUTOCOMMIT is already set. The error begins, Transactions not supported by database....
Name
bind_col()
Synopsis
$sth->bind_col(index, \$variable[, \%attri|type])
This funciton associates or binds a column from a statement handle to a given variable. The values are updated when the related row is retrieved using a fetch method, without extra copying of data. Here is an example:
...
my $sql_stmnt = "SELECT title, author FROM books";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();
$sth->bind_col(1, \$title);
$sth->bind_col(2, \$author);
while($sth->fetch()) {
print "$title by $author \n";
}
In this example, we’re specifying that the first (1) column be bound to the variable $title and the second to $author. A separate statement has to be issued for each bind. To bind multiple columns in one statement, use bind_columns().
To specify the column data type to use for the variable—this can potentially change the data—give the desired SQL standard type as the third argument:
...
my $sql_stmnt = "SELECT title, author FROM books";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();
$sth->bind_col(1, \$title, { TYPE=>SQL_VARCHAR } );
$sth->bind_col(2, \$author, { TYPE=>SQL_VARCHAR } );
while($sth->fetch()) {
print "$title by $author \n";
}
To get a list of SQL standard data types available on your server, run this program:
#!/usr/bin/perl -w
use DBI;
foreach (@{ $DBI::EXPORT_TAGS{sql_types} }) {
printf "%s=%d\n", $_, &{"DBI::$_"};
}
Name
bind_columns()
Synopsis
$sth->bind_columns(@variables)
This function associates or binds columns from a statement handle to a given list of variables (@variables). The values are updated when the related row is retrieved using a fetch method without extra copying of data. The number of variables given must match the number of columns selected and the columns are assigned to variables in the order the columns are returned. Here is an example:
...
my $sql_stmnt = "SELECT title, author FROM books";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();
$sth->bind_columns(\$title, \$author);
while($sth->fetch()) {
print "$title by $author \n";
}
Name
bind_param()
Synopsis
$sth->param(index, values[, \%attr|type])
This function associates or binds a value in an SQL statement to a placeholder. Placeholders are indicated by ? in SQL statements and are numbered in the order they appear in the statement, starting with 1. The first argument indicates which placeholder to replace with a given value, i.e., the second argument. The data type may be specified as a third argument. Here is an example:
...
my $sql_stmnt = "SELECT title, publisher
FROM books WHERE author = ?
AND status = ?";
my $sth = $dbh->prepare($sql_stmnt);
$sth->bind_param(1, $author);
$sth->bind_param(2, $status);
$sth->execute();
while(my ($title,$publisher) = $sth->fetchrow_array()) {
print "$title ($publisher) \n";
}
In this example, a placeholder (a question mark) is given in the SQL statement and is replaced with the actual value of $author using bind_param(). This must be done before the execute() is issued.
Name
bind_param_array()
Synopsis
$sth->bind_param_array(index, {array_ref|string}[, \%attri|type])
This function associates or binds an array of values in an SQL statement within a prepare() using placeholders. The first argument indicates which placeholder to replace with the array of given values, i.e., the second argument. The values are updated when the related row is retrieved using a fetch method. Attributes may be added or the data type given as a third argument. Here is an example:
...
my @old_names = ('Graham Green', 'Virginia Wolf');
my @new_names = ('Graham Greene', 'Virginia Woolf');
my $sql_stmnt = "UPDATE books
SET author = ?,
status = ?
WHERE author = ?";
my $sth = $dbh->prepare($sql_stmnt);
$sth->bind_param_array(1,\@new_names);
$sth->bind_param_array(2, 'active');
$sth->bind_param_array(3, \@old_names);
$sth->execute_array(undef);
$sth->finish();
Notice in this example that the first array contains all of the new author names, the corrected ones. It’s not a pairing or a grouping by row. Instead, it’s all of the values to be used for the first placeholder in the SQL statement. The second array bound contains the old names in the same order to be used in the WHERE clause. Incidentally, the backslash before each array shown here is necessary because an array reference must be given. The second bind_param_array() set in the example uses just a string (i.e., 'active'). That value will be used for all rows updated.
Name
bind_param_inout()
Synopsis
$sth->bind_param_inout(index, \$value, max_length[, \%attri|type])
This function associates or binds a value in an SQL statement using a placeholder. The first argument indicates which placeholder to replace with a given value, i.e., the second argument. It must be given as a reference (a variable preceded by a backslash). The values are updated when the related row is retrieved using a fetch method. The maximum length of a value is given in the third argument. Attributes may be added or the data type may be given as a fourth argument. This function is generally used with stored procedures.
Name
can()
Synopsis
$handle->can($method_name)
This function returns true if the method named is implemented by the driver. You can use this method within a program to determine whether a method for a handle is available. In the following example, after starting the program and setting the database and statement handles, we use thecan() method:
...
my @methods = qw(fetchrow_array fetchrow_arrays);
foreach $method(@methods) {
if($sth->can($method)) { print "\$sth->$method is implemented.\n"; }
else { print "\$sth->$method is not implemented.\n\n"; }
}
Here are the results from running this part of the program. Notice that the second, fictitious method named is not available:
$sth->fetchrow_array is implemented.
$sth->fetchrow_arrays is not implemented.
Name
clone()
Synopsis
$dbh->clone([\%attri])
Use this function to create a new database handle by reusing the parameters of the database handle calling the method. Additional attributes may be given with the method. Their values will replace any existing values. Any attributes given in the original database handle will be used in the new handle. Here is an example:
my $dbh1 = $dbh->clone({AutoCommit=>1});
The value of this method is that you can create a second MySQL session with it without having to restate the parameters from earlier. You can also use it if the disconnect() has already been issued for the original database handle.
Name
column_info()
Synopsis
$dbh->column_info($catalog, $database, $table, $column)
This function returns a statement handle for fetching information about columns in a table. Here is an example:
...
my $sth = $dbh->column_info(undef, 'bookstore', 'books', '%');
my $col_info = $sth->fetchall_arrayref();
foreach my $info(@$col_info) {
foreach (@$info) {
if($_) { print $_ . "|"; }
}
print "\n";
}
This program excerpt will produce a list of columns in the books table of the bookstore database. Here are a couple of lines of the program results:
bookstore|books|book_id|4|INT|11|10|4|1|NO|1|int(11)|
bookstore|books|title|12|VARCHAR|50|1|12|2|YES|varchar(50)|
...
The values of the fields in order are: TABLE_CAT (usually empty), TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH, DECIMAL_DIGITS, NUM_PREC_RADIX, NULLABLE, REMARKS, COLUMN_DEF, SQL_DATA_TYPE,SQL_DATETIME_SUB, CHAR_OCTET_LENGTH, ORDINAL_POSITION, and IS_NULLABLE.
Name
commit()
Synopsis
$dbh->commit()
This function commits or makes permanent changes to a database for transactional tables (e.g., InnoDB). It’s disregarded if AutoCommit is already enabled; a warning message saying “Commit ineffective while AutoCommit is on” will be issued.
Name
connect()
Synopsis
DBI->connect(DBI:server:database[:host:port],
username, password[, \%attri])
Use this method to establish a connection to MySQL and to select the default database. The first argument is a list of required values separated by colons: the module (DBI), the driver (mysql) for a MySQL server, and the database name. The hostname or IP address and port number are optional. The second argument is the username and the third is the user’s password. You can substitute any of these settings or values with variables—just be sure to enclose each argument containing variables with double quotes so that the values will be interpolated. Finally, you may give attributes in the fourth argument. Here is an example:
my $dbh = DBI->connect('DBI:mysql:bookstore:localhost',
'paola','caporalle1017', {AutoCommit=>0});
In this excerpt, Perl is connecting to the MySQL server with the username paola and the password caporalle1017, with the database bookstore. The attribute AutoCommit is set to off so that changes to the data may be undone using rollback(). See the end of this chapter for a list of attributes.
If you don’t specify the username or the user’s password (i.e., if undef is given instead), the value of the environment variables, DBI_USER and DBI_PASS, will be used if they are defined.
Name
connect_cached()
Synopsis
DBI->connect_cached(DBI:server:database[:host:port],
username, password[, \%attri])
This method is similar to connect(), except that the database handle is stored in a hash with the given parameters. This allows the database handle to be reused if connect_cached() is called again. You can access and eliminate a cache with the CachedKids attribute. This method can cause problems with a database system by inadvertently opening too many connections.
Name
data_diff()
Synopsis
DBI::data_diff(string, string[, length])
This function returns the results of both data_string_desc() and data_string_diff(), describing the difference between the two given strings. It returns an empty string if the strings given are identical. Here is an example:
...
my $previous_author = 'Graham Greene';
my $sql_stmnt = "SELECT book_id, author
FROM books
WHERE author LIKE 'Graham%'
LIMIT 1";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();
while( my($book_id,$author) = $sth->fetchrow_array()) {
my $diff = DBI->data_diff($previous_author, $author);
if($diff) {
print "$previous_author <=> $author\n$diff \n";
$previous_author = $author;
}
}
Here are the results of running this program:
Graham Green <=> Graham Greene
a: UTF8 off, ASCII, 3 characters 3 bytes
b: UTF8 off, ASCII, 12 characters 12 bytes
Strings differ at index 0: a[0]=D, b[0]=G
Name
data_sources()
Synopsis
DBI->data_sources([driver, \%attri])
This function returns a list of databases associated with a given driver. If none is specified, the driver named in the environment variable DBI_DRIVER is used. Attributes may be given as a second argument. Here is an example:
...
my @drivers = DBI->available_drivers();
|| die "No drivers found.";
foreach my $driver(@drivers) {
my @sources = DBI->data_sources($driver);
foreach my $source(@sources) {
print "$driver: $source\n";
}
}
Name
data_string_desc()
Synopsis
DBI::data_string_desc(string)
This function returns a description of a given string:
...
print DBI->data_string_desc('Graham Greene');
Here are the results:
UTF8 off, ASCII, 3 characters 3 bytes
Name
data_string_diff()
Synopsis
DBI::data_string_diff(string[, length])
This function returns a description of the difference between two given strings. It returns an empty string if the strings given are identical. Here is an example:
...
my $diff = DBI->data_string_diff($previous_author, $author);
...
Using the example shown in data_diff() earlier in this section, but using this function instead, here are the results of running this program:
(Graham Green, Graham Greene)
Strings differ at index 0: a[0]=D, b[0]=G
Name
disconnect()
Synopsis
$dbh->disconnect()
This function disconnects a Perl program from a database; it ends a MySQL session. There are no arguments for this function. Depending on your system, it may or may not commit or roll back any open transactions started by the database handle. To be sure, intentionally commit or roll back open transactions before disconnecting. Also, be sure to close any open statement handles by executing finish() for each statement handle before disconnecting, like so:
$sth->finish();
$dbh->disconnect();
Name
do()
Synopsis
$dbh->do($sql_stmnt[, \%attri, @values])
This function executes an SQL statement without having to use the prepare() method. It returns the number of rows changed. The first argument contains an SQL statement. If placeholders are used in the SQL statement, their values are provided in a comma-separated list or in an array in the third argument. Statement handle attributes may be given for the second argument. You would use this method only with SQL statements that do not return data values (e.g., use with UPDATE, not SELECT). Here is an example:
...
my $sql_stmnt = "UPDATE books SET publisher = ?
WHERE publisher = ?";
my @values = ('Oxford Univ. Press', 'OUP');
$dbh->do($sql_stmnt, undef, @values);
$dbh->disconnect();
In this example, the initials of a particular publisher are changed to the publisher’s name. The SQL statement is executed without a prepare() or an execute()—that is, without a statement handle. Therefore, a finish() isn’t required, just a disconnect(). If you want to know the number of rows changed, change the example like so:
...
my $rows_changed = $dbh->do($sql_stmnt, undef, @values);
print "Rows Changed: $rows_changed";
Name
dump_results()
Synopsis
$sth->dump_results(length, row_delimiter, column_delimiter, filehandle})
This function displays the results of a statement using the neat_list() function on each row for the statement handle given. The first argument is the maximum length of each column’s display. For columns containing more characters than the maximum length, the excess will be omitted and ellipses will be presented in its place. The default length is 35 characters. For the second argument, the delimiter for each row may be given—the default is \n. The delimiter for columns may also be changed from the default of a comma and a space in the third argument. In the last argument of the function, a file handle that specifies where to direct the results of the function may be given. If one is not specified, stdout is used. Here is an example:
...
my $sql_stmnt = "SELECT title, authors
FROM books
WHERE author= 'Henry James' LIMIT 3";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();
$results = $sth->dump_results(10, "\n", '|');
...
The results of this program would look like this:
'The Boston...'|'Henry James'
'The Muse'|'Henry James'
'Washington...'|'Henry James'
3 rows
Name
err()
Synopsis
$handle->err()
This function returns any error codes from the last driver method call. Here is an example:
...
my $dbh = DBI->connect('DBI:mysql:bookstore:localhost','russell',
'wrong_password')
|| die DBI->err();
Notice the err() method is added to the end of the database handle as part of the die function from Perl. Here are the results of executing this connect() with the wrong password:
DBI connect('bookstore:localhost','russell',...) failed:
1045 at ./dbi_test_program.plx line 8...
Notice that the function err() only returns the error code 1045 from the MySQL server. The rest of the text is from Perl in general.
Name
errstr()
Synopsis
$handle->errstr()
This function returns any error messages from the last driver method called.
...
my $dbh = DBI->connect('DBI:mysql:bookstore:localhost','username',
'wrong_password')
|| die DBI->errstr;
Notice the errstr() method is added to the end of the database handle as part of the die function from Perl. Here are the results of executing this connect() with the wrong password:
DBI connect('bookstore:localhost','russell',...) failed:
Access denied for user 'russell'@'localhost' (using password: YES)
at ./dbi_test_program.plx line 8...
Notice that the error message does not display the password given.
Name
execute()
Synopsis
$sth->execute([@values])
This function executes a statement handle that has been processed with the prepare() method. A value of undef is returned if there’s an error. It returns true if successful, even when the results set is blank or zero. For statements other than SELECT statements, the number of rows affected is returned. Here is an example:
...
my $dbh = DBI->connect ("$data_source","$user","$pwd")
my $pub_year = '1961';
my $genre = 'novel';
my $sql_stmnt = "SELECT title, author
FROM books
WHERE pub_year = '$pub_year'
AND genre = '$genre'";
my $sth = $dbh->prepare($sql_stmnt);
my $rows_chg = $sth->execute();
while( my($title,$author) = $sth->fetchrow_array()) {
print "$title by $author \n";
}
You can use placeholders in the SQL statement (e.g., for $pub_year and $genre) by giving the values with execute():
. . .
my @values = ('1961','novel');
my $sql_stmnt = "SELECT title, author
FROM books
WHERE pub_year = ?
AND genre = ?";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute(@values);
while( my($title,$author) = $sth->fetchrow_array()) {
print "$title by $author \n";
}
You don’t have to put values into an array for use with this method. You can put the strings inside the parentheses of the function (e.g., $sth->execute($pub_year,$genre);).
Name
execute_array()
Synopsis
$sth->execute_array(\%attri[, @values)
Use this function to execute a prepared statement multiple times, once for each set of values given either as the second argument of the method or from previous uses of the bind_param_array() method. If you use the bind_param_array() method, you won’t provide the array values with this execute_array() method. For an example of this statement’s use with the bind_param_array() method, see that description earlier in this chapter. Here is an example without that method:
...
my @old_names = ('Graham Green', 'Virginia Wolf');
my @new_names = ('Graham Greene', 'Virginia Woolf');
my $sql_stmnt = "UPDATE books
SET author = ?,
status = ?
WHERE author = ?";
my $sth = $dbh->prepare($sql_stmnt);
my ($tuple, $rows_chg) = $sth->execute_array(undef, \@new_names, 'active',
\@old_names);
$sth->finish();
Notice that we are able to capture the number of rows changed by the SQL statement. Since we didn’t specify any attributes, the $tuple variable will be empty. A tuple is an ordered list of values or objects.
Name
execute_for_fetch()
Synopsis
execute_for_fetch($fetch[, \@status)
Use this method to execute multiple statements given as the argument of the method, as a sub method. You may give a reference to a subroutine that returns an array of arrays of data. Or you may give the array of arrays as shown in the following example. Tuple status may be given as an array reference for the second argument:
...
my @engl = ('one','two','three');
my @ital = ('uno','due','tre');
my @germ = ('eins','zwei','drei');
my @count_values =(\@engl, \@ital, \@germ);
my $sth = $dbh->prepare("INSERT INTO count_three
(col1, col2, col3)
VALUES (?,?,?)");
my ($rc) = $sth->execute_for_fetch( sub { shift @count_values }, undef);
The value of $rc is 3. Since the tuple’s status is undefined in this example, there is none. However, if you were to give one with the method, you could capture the tuple status as well (e.g., my ($tuple,$rc) = $sth->execute_for_fetch(...);). Here are the contents of the test table after running this Perl program:
SELECT * FROM count_three;
+------+------+-------+
| col1 | col2 | col3 |
+------+------+-------+
| one | two | three |
| uno | due | tre |
| eins | zwei | drei |
+------+------+-------+
Name
fetch()
Synopsis
$sth->fetch()
This function returns a reference to an array of one row from the results of a statement handle. It’s similar to fetchrow_array() except that it requires the use of bind_col() or bind_columns() for setting variables to values fetched. There are no arguments for this function. Here is an example:
...
my $sql_stmnt = "SELECT title, author FROM books";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();
my ($title, $author);
$sth->bind_columns(\$title, \$author);
while( $sth->fetchrow_array()) {
print "$title by $author \n";
}
...
Name
fetchall_arrayref()
Synopsis
$sth->fetchall_arrayref()
This function captures the results of a statement and returns a reference to the data. The results are a complex data structure: an array of references, with each reference to an array for each row of data retrieved. You can finish the statement handle after executing this method, since the results are stored in memory. Here is an example:
...
my $sql_stmnt = "SELECT title, author FROM books";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();
my $books = $sth->fetchall_arrayref();
$sth->finish();
foreach my $book (@$books) {
my ($title, $author) = @$book;
print "$title by $author \n";
}
$sth->finish();
Notice that after fetchall_arrayref() is called, finish() is used before the data is parsed. Using foreach, first the array reference is dereferenced (i.e., @$books) and the reference to each array containing a row from the results is stored in a variable ($book). Then that array reference is deferenced (@$book) to parse the fields into variables for use.
Name
fetchall_hashref()
Synopsis
$sth->fetchall_hashref(key_column)
This method captures the result of an SQL statement and returns a reference to the data. The result is a complex data structure: it returns a reference to a hash using the name of the key column given as its key and the value of the key column given as its value. Each key column value is then used as the key to another hash with a reference to yet another hash for each. This final hash has the column names from the SQL statement as its keys and the values of each row of data retrieved as their respective hash values. The unraveling of such a hash reference may become clearer if you study the following code excerpt:
...
my $sql_stmnt = "SELECT book_id, title, author
FROM books";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();
my $books = $sth->fetchall_hashref('book_id');
$sth->finish();
foreach my $book(keys %$books) {
my $book_id = $books->{$book}->{'book_id'};
my $title = $books->{$book}->{'title'};
my $author = $books->{$book}->{'author'};
print "$title ($book_id) by $author\n";
}
Notice for the SQL statement we are able to select more than two columns—that’s because this is not a simple hash, but rather a hash of hashes (a key/value pairing is created ultimately from the column names and their respective values). Notice also with the fetchall_hashref() that the primary key column of book_id is given within quotes, as a string. Since this is a hash, a column with unique values is given. Looking at the foreach, we use keys to extract just the keys to the dereferenced hash reference. We don’t need the values (which are the hashes for each row of data from the results set) at this point: we’ll get to that value within the code block of the foreach statement. Each key is then stored in the variable $book. Using that key, we can extract the hashes that are referenced by an object oriented method: $hash_ref->{$key_col}->{'col_name'}. It might help a bit if I show you the preceding code but with the keys and values of the first hash and with more verbose results. We’ll have to use a while statement with the each function:
...
while( my ($book_key,$book_values) = each(%$books)) {
my $book_id = $books->{$book_key}->{'book_id'};
my $title = $books->{$book_key}->{'title'};
my $author = $books->{$book_key}->{'author'};
print "$books\->$book_key\->$book_values\->\n
{book_id->'$book_id',title->'$title',author->'$author'}\n\n";
}
Here are two lines of the results of the program. You can see the two hashes mentioned earlier. After the first hash, notice the value of book_id is the key to the hash for the row of data. The book_id is also included in the final hash:
HASH(0x81e09e4)->1000->HASH(0x81e0b10)->
{book_id->'1000', title->'Mrs. Dalloway', author->'Virginia Woolf'}
HASH(0x81e09e4)->1001->HASH(0x81e0a20)->
{book_id->'1001', title->'The End of the Affair', author->'Graham Greene'}
Name
fetchrow_array()
Synopsis
$sth->fetchrow_array()
This statement handle method returns one row, the next from the results of an SQL statement in the form of an array, each of whose element is a field of data. Null values retrieved are returned as undefined. An empty value is returned when there is an error or when there are no more rows remaining in the results set. Therefore, if used in a flow control statement such as while, the empty value returned will end the loop statement. Here is an example:
...
my $sql_stmnt = "SELECT title, author FROM books";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();
while (my ($title, $author) = $sth->fetchrow_array(){
print "$title by $author \n";
}
$sth->finish();
If you know that the SQL statement will return only one row, you won’t need the while statement. Instead, you can save the values directly into a tight list of variables:
...
my $sql_stmnt = "SELECT title, author
FROM books LIMIT 1";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();
my ($title, $author) = $sth->fetchrow_array();
print "$title by $author \n";
...
Name
fetchrow_arrayref()
Synopsis
$sth->fetchrow_arrayref()
This function returns a reference to a place in memory containing an array of one row, the next row from the results of a statement handle. There are no arguments for this function. Null values retrieved are returned as undefined. An empty value is returned when there is an error or when there are no more rows remaining in the results set. Therefore, if used in a flow control statement such as while, the empty value returned will end the loop statement. Here is an example:
...
my $sql_stmnt = "SELECT title, author FROM books";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();
while (my $book = $sth->fetchrow_arrayref()) {
my ($title, $author) = @$book;
print "$title by $author \n";
}
$sth->finish();
Notice that fetchrow_arrayref() is reused at the beginning of each pass through the while statement. This is because a reference to one row is retrieved at a time. The same reference is used for each row retrieved: the array is replaced with each loop. If you want to use array references, you might want to use fetchall_arrayref() instead.
Name
fetchrow_hashref()
Synopsis
$sth->fetchrow_hashref([name] )
This function returns a reference to a place in memory containing a hash of keys and values for one row from the results of a statement handle. The optional argument of this method is to give the statement handle name attribute: NAME (the default), NAME_lc, or NAME_uc. See the end of this chapter for a description of these attributes. The name must be given within quotes if given as a string. Null values retrieved by this method are returned as undefined. An empty value is returned when there is an error or when there are no more rows remaining in the results set. Therefore, if used in a flow control statement such as while, the empty value returned will end the loop statement. Here is an example:
...
my $sql_stmnt = "SELECT title, author FROM books";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();
while (my $book_ref = $sth->fetchrow_hashref('NAME_uc')) {
print "$book_ref->{'TITLE'} by $book_ref->{'AUTHOR'} \n";
}
$sth->finish();
Notice that the name given here with this method instructs the hash to use all uppercase letters for the key names. Therefore, when calling the particular data, the column names are given in all uppercase letters. If no parameter was given, we could use the column names as they are in the table. The fetchrow_hashref() method is much simpler than fetchall_hashref(), but you can’t close the statement handle until you’re finished processing the results. Therefore, you may want to consider using fetchall_hashref() instead.
Name
finish()
Synopsis
$sth->finish()
This method ends a statement handle given that was established by the prepare() method. There are no arguments to the method. It can sometimes help to free system resources. It should be issued only when the statement handle is not going to be reused or is to be replaced. Here is an example:
...
my $sql_stmnt = "REPLACE INTO books (title, author)
VALUES(?,?)";
my $sth = $dbh->prepare($sql_stmnt);
while( my ($title,$author) = each(%books) ) {
$sth->execute($title,$author);
}
$sth->finish();
$dbh->disconnect();
Since we’re reusing the statement handle here (assuming %books defined earlier in the program has plenty of data), we especially don’t need to call the finish() method after each execution of the SQL statement. Although a statement handle may have been closed with finish(), more statement handles may be created and executed as long as the database handle has not been closed using disconnect().
Name
foreign_key_info()
Synopsis
$dbh->foreign_key_info($pk_catalog, $pk_database, $pk_table,
$fk_catalog, $fk_database, $fk_table[, \%attri])
This function returns a statement handle for fetching information about foreign keys in a given table. It’s still fairly new and does not seem to be well integrated into MySQL yet.
Name
func()
Synopsis
$handle->func(@arguments, function_name)
This function calls private nonportable and nonstandard methods for handles. The name of the function is given as the last argument. Any arguments for the function specified are given first. You can give certain private built-in functions: _ListDBs with the hostname and optionally the port as the first parameter (use data_sources() instead); _ListTables (deprecated; use tables() instead); _CreateDB with the database name as the first parameter; and _DropDB with the database name as the first parameter. Here is an example:
...
my @tables = $dbh->func('_ListTables');
foreach my $table(@tables) {
print $table, "\n";
}
As this syntax indicates, you can create your own private DBI functions with this method. It’s not well supported in MySQL, though.
Name
get_info()
Synopsis
$dbh->get_info(type)
This function returns information about the database handle for the numeric code type (based on SQL standards) given as an argument to the method. Information can include the driver and the capabilities of the data source. The function returns undef for an unknown type. Here is an example:
...
use DBI::Const::GetInfoType;
...
if($dbh->get_info($GetInfoType{SQL_DBMS_VER}) lt '5.0') {
print "Old version of MySQL. Upgrade!"
};
To see a list of other parameters available and their values on your server, run the following from your server:
...
use DBI;
use DBI::Const::GetInfoType;
...
while( my ($key,$value) = each(%GetInfoType) ) {
my $info = $dbh->get_info( $GetInfoType{"$key"} );
print "$key\->$info \n";
}
Name
installed_drivers()
Synopsis
DBI->installed_drivers([nowarn])
This function returns a hash listing driver names and handles loaded for the current process. These are only the drivers that are loaded for the program that’s running, not all that are available and installed. For information on those, use available_drivers(). Here is an example:
...
my %drivers = DBI->installed_drivers();
while( my ($key,$values) = each(%drivers)) {
print "$key -> $values \n";
}
Name
installed_versions()
Synopsis
DBI->installed_versions()
This function returns a list of installed drivers. There are no arguments to this method. Although it can be used from within a program, it works easily and best from the command line. Enter the following from the command line of the server:
perl -MDBI -e 'DBI->installed_versions'
Name
last_insert_id()
Synopsis
$dbh->last_insert_id($catalog, $database, $table, $column[, \%attr])
This function returns the value stored in the row identification column of the most recent row inserted for the current MySQL session, provided the identification number was incremented using AUTO_INCREMENT in MySQL. It works like the LAST_INSERT_ID() function in MySQL. No arguments for this function are necessary with MySQL: if given, their values are ignored, although undef is required at a minimum. Other systems may require other options. This function doesn’t work with MySQL before version 1.45 of DBI. It returns undefined if it cannot retrieve the number (which must be retrieved after the insert, and before another statement in MySQL) or if the driver does not support this function.
Here is an example:
...
my $sth = $dbh->prepare("INSERT INTO books (title, author)
VALUES (?,?)");
$sth->execute($title,$author);
my $book_id = $dbh->last_insert_id(undef,undef,undef,undef,undef);
print "New Book ID: $book_id \n";
$sth->finish();
Name
looks_like_number()
Synopsis
DBI->looks_like_number(@array)
This method is used for testing a given array to determine whether each element seems to be a number or not. It returns 1 for each element in an array that appears to be a number; 0 for those that do not. It returns undefined if the element is empty or undefined. Here is an example:
...
my $sql_stmnt = "SELECT book_id, title, author, isbn
FROM books LIMIT 1";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();
my (@book) = $sth->fetchrow_array();
my @num_assessment = DBI->looks_like_number(@book);
my $cnt = 0;
foreach (@num_assessment) {
if($_) { print "Array Element $cnt looks like a number.\n" };
++$cnt;
}
The results of this code will show that elements 1 and 4 appear to be numbers.
Name
neat()
Synopsis
DBI::neat(string[, length])
This function returns a string given as the first argument, placed in quotes, for an optional maximum length given as the second argument. It will not escape quotes within the string. If given a numeric value instead of a string, it will not return the results within quotes. It will return NULL values as undefined. Here is an example:
...
my $test = "This is Russell's test!";
print "Test: " . DBI::neat($test, 24) . "\n";
Here are the results:
Test: 'This is Russell's t...'
Notice that the results are in single quotes, that the text was truncated even though the maximum length given was enough to just encompass the string. If a value of 25—one more than needed—had been given, then the full text would have been displayed without ellipses. To neaten a list of strings, use the neat_list() function.
Name
neat_list()
Synopsis
DBI::neat_list(\@strings[, length, delimiter])
This function returns a list of strings given as the first argument, placed in quotes, each truncated to an optional maximum length given as the second argument. An optional third argument can specify a delimiter to place between the elements of the list or the array given in the first argument. A comma and a space will be used by default if no delimiter is specified. Here is an example:
...
my @test = ("This is a test.", "Another test");
print "Test: " . DBI::neat_list(\@test, 12, '|');
Here are the results:
Test: 'This is...'|'Another...'
Name
parse_dsn()
Synopsis
DBI->parse_dsn($data_source_name)
This function returns the components of the DBI Data Source Name (DSN) values: the scheme (dbi); the driver ($ENV{DBI_DRIVER}); an optional attribute string; a reference to a hash with the attribute names and values; and the DBI DSN string. Here is an example:
...
use DBI;
my $dsn = "DBI:mysql:database=bookstore;host=localhost;port=3306";
my $dbh = DBI->connect ($dsn,$user,$pwd) or die DBI->errstr;
my ($scheme, $driver, $attr_string, $attr_hash, $driver_dsn) =
DBI->parse_dsn($dsn);
print "DSN: ($scheme, $driver, $attr_string, $attr_hash, $driver_dsn) \n";
Here are the results:
DSN: (dbi, mysql, , , database=bookstore;host=localhost;port=3306)
Name
parse_trace_flag()
Synopsis
$handle->parse_trace_flag($settings)
This function returns a bit flag for a trace flag name given as an argument. To parse a list of trace flags, see parse_trace_flags() next.
Name
parse_trace_flags()
Synopsis
$handle->parse_trace_flags($settings)
Use this function to parse a string given as an argument that contains a list of trace settings. These settings are either trace flag names or integers representing trace levels.
Name
ping()
Synopsis
$dbh->ping()
Use this function to determine whether a MySQL server is still running and the database connection is still available. There are no arguments for this method. Here is an example:
...
$sth->finish();
my $alive = $dbh->ping();
if($alive) { print "MySQL connection is still alive.\n"}
else{ print "MySQL connection is not alive.\n"}
$dbh->disconnect();
if($dbh->ping()) { print "MySQL connection is still alive.\n"}
else{ print "MySQL connection is not alive.\n"}
...
The results will show that the connection is alive after the finish() is called, but not after disconnect().
Name
prepare()
Synopsis
$sth = $dbh->prepare(statement[, \%attr])
This function creates a statement handle by preparing an SQL statement given as the first argument for subsequent execution with execute(). It returns a reference to the statement handle. The second argument is a hash of attributes and is optional. A prepared statement or a statement handle may be used multiple times until the disconnect() is issued or until the statement handle value is overwritten by another call to prepare() for the same statement handle variable. More than one statement handle can be prepared if different variables are used for storing the handle references. Here is an example:
my $dbh = DBI->connect ("$dsn","$user","$pwd")
my $sql_stmnt = "SELECT title, author FROM books";
my $sth = $dbh->prepare($sql_stmnt, {RaiseError => 1, ChopBlanks => 1});
Warning messages are enabled here and trailing spaces of fixed-width character columns are trimmed. See the end of this chapter for a list of attributes.
Name
prepare_cached()
Synopsis
$dbh->prepare_cached($sql_standard[, \%attr, $active])
This function creates a statement handle like prepare() does, but it stores the resulting statement handle in a hash. Attributes for the statement handle may be given in the second argument in the form of a hash. The third argument of the method changes the behavior of the handle if an active statement handle is already in the cache. Table 18-1 lists the four choices for this argument.
The statement handle that this method generates is used in basically the same way as the statement handle generated by prepare(). However, it can potentially cause system problems if not used properly. Therefore, use prepare() instead.
Table 18-1. Active argument for prepare_cached()
|
Active value |
Result |
|
0 |
Warning messages will be issued, and finish() for the statement handle will be employed. |
|
1 |
No warning will be displayed, but finish() will be executed. |
|
2 |
Disables checking for an active handle. |
|
3 |
Causes the new statement handle to replace the active one. |
Name
primary_key()
Synopsis
$dbh->primary_key($catalog, $database, $table)
This function is meant to return a list of primary key column names for a given table. If there are no primary keys, it will return an empty list. This method does not yet seem to be supported in MySQL.
Name
primary_key_info()
Synopsis
$dbh->primary_key_info($catalog, $database, $table)
This function is meant to return a statement handle for fetching information about primary key columns for a table. The values are part of a hash for the statement handle: TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and KEY_SEQ. If there is no primary key for the table given, it returns no rows. This method does not yet seem to be supported in MySQL.
Name
private_attribute_info()
Synopsis
$handle->private_attribute_info($settings)
This function is meant to return a reference to a hash containing the private attributes available for the handle from which it is called. There are no parameters of this method, and it does not yet seem to be supported in MySQL.
Name
quote()
Synopsis
$dbh->quote(string[, data_type)
Use this method to escape special characters contained in a given string. It’s useful in SQL statements, particularly for unknown user input that might contain metacharacters that would cause undesirable behavior in MySQL. You can specify the data type as a second parameter. Don’t use this method with bind values and placeholders. Here is an example:
...
my $comment = shift;
my $quoted_comment = $dbh->quote($comment);
my $sql_stmnt = "UPDATE books SET comment = ?";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute($quoted_comment);
print "Original: $comment \n Quoted: $quoted_comment \n";
Here are the command line results:
Original: Henry James' book "The Muse" is wonderful!
Quoted: 'Henry James\' book \"The Muse\" is wonderful!'
Name
quote_identifier()
Synopsis
$dbh->quote_identifier({$name|$catalog, $database[, $table, \%attri]})
Use this function to escape special characters of an identifier (e.g., a database, table, or column name) for use in an SQL statement. You can provide only the first parameter (a string containing an identifier name), or you can provide the catalog name (undef is acceptable with MySQL), a database name, a table name, and optionally provide database attributes. Here is an example:
my $col1 = $dbh->quote_identifier('author');
my $col2 = $dbh->quote_identifier('title');
my $table = $dbh->quote_identifier('books');
my $sql_stmnt = "SELECT $col1, $col2 FROM $table";
print $sql_stmnt;
Here is the resulting SQL statement:
SELECT `author`, `title` FROM `books`
Name
rollback()
Synopsis
$dbh->rollback()
Use this function to undo a transaction that has not yet been committed. This can only be used with transactional (e.g., InnoDB or BDB) tables. It requires that the database handle was created with the AutoCommit attribute set to false or 0, and that the changes were not committed using thecommit() function or by any other method that might unintentionally commit a transaction.
Name
rows()
Synopsis
$sth->rows()
This function returns the number of rows affected by the last statement handle executed. It works with UPDATE, INSERT, and DELETE dependably. It doesn’t work effectively with SELECT statements unless all rows in a table are selected. If the number of rows is unknown, –1 is returned. There are no arguments to this method. Here is an example:
...
my $sql_stmnt = "UPDATE books SET author = 'Robert B. Parker'
WHERE author = 'Robert Parker'";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();
my $change_count = $sth->rows();
print "$change_count rows were changed.";
This program displays the following when run:
2 rows were changed
Name
selectall_arrayref()
Synopsis
$dbh->selectall_arrayref($statement[, \%attri][, @bind_values])
This function returns a reference to an array, which is the results set of the SQL statement executed. For each row of the results, another reference to an array is returned for each row of data. An optional second argument can specify any of the attributes allowed for the statement handle. If placeholders are used in the SQL statement, their values may be given as an array for the final argument. This method combines prepare(), execute(), and fetchall_arrayref(). Here is an example showing how it might be dereferenced:
my $sql_stmnt = "SELECT title, author
FROM books WHERE book_id = ?";
my $books = $dbh->selectall_arrayref($sql_stmnt, undef, '1234');
foreach my $book (@$books) {
my ($title, $author) = @$book;
print "$title by $author \n";
}
Notice that the prepare() method isn’t called to prepare the SQL statement or to create a statement handle. This means that finish() doesn’t need to be called. However, instead of giving an SQL statement, you can give a statement handle. Since the result is an array reference, it must be deferenced in order to extract the data (i.e., the @$books). Using the foreach Perl function, each element of the array is extracted (the array reference for each row), which is then deferenced within the code block (@$book). From this, the values for the individual fields can be parsed and saved to variables.
Name
selectall_hashref()
Synopsis
$dbh->selectall_hashref($statement, $key_field[, \%attri][, @bind_values])
This function returns a reference to a hash of references to hashes, one for each row from the results of an SQL statement given. This method combines prepare(), execute(), and fetchall_hashref(). A unique key field must be given for the second argument. This will be used for the key of the main hash of rows from the results set. An optional third argument can specify any of the attributes allowed for a statement handle. If placeholders are used in the SQL statement, their values must be given as an array for the final argument.
Here is an example:
...
my $sql_stmnt = "SELECT rec_id, title, author
FROM books";
my $books = $dbh->selectall_hashref($sql_stmnt, 'book_id');
foreach my $book_id (keys %$books) {
print "$books->{$book_id}{title}
by $books->{$book_id}{author} \n";
}
Notice that the prepare() method isn’t called to prepare the SQL statement or to create a statement handle. This means that finish() doesn’t need to be called. However, instead of giving an SQL statement, you can give a statement handle. Since the result is a hash reference, it must be deferenced in order to extract the data (i.e., the %$books). Using the foreach and the keys Perl functions, each key of the hash is extracted (the hash reference for each row), which is then deferenced within the code block (%$book). From this, the values for the individual fields can be extracted by the object oriented method.
Name
selectcol_arrayref()
Synopsis
$dbh->selectcol_arrayref($sql_statement[, \%attri][, @bind_values])
This returns a reference to an array containing a value in the first column of each row selected. The SQL statement is given as the first argument of the function. This can be particularly useful if the first column is a key field. This function performs prepare() and execute() on the SQL statement. Here is an example:
...
my $sql_stmnt = "SELECT * FROM books";
my $book = $dbh->selectcol_arrayref($sql_stmnt);
foreach my $author_id (@$book){
print "$author_id \n";
}
The prepare() method isn’t called to create a statement handle if the SQL statement is given with this method, making finish() unnecessary. However, a statement handle could be given instead. Since the result is an array reference, it must be deferenced to extract the data (i.e.,@$book). Using foreach, each element of the array is extracted, one element per row of the results set, and the value of each temporarily stored in a variable here ($author_id, the first column of the table).
Name
selectrow_array()
Synopsis
$dbh->selectrow_array($sql_statement[, \%attri, @values])
This function returns one row from the results of an SQL statement in the form of an array, where each column returned is represented by an element of the array, in order. This method combines prepare(), execute(), and fetchrow_array(). No statement handle is created, sofinish() is unnecessary. An optional second argument can specify any of the attributes allowed for a statement handle. If placeholders are used in the SQL statement, their values must be given as an array for the third argument. Here is an example:
...
my $sql_stmnt = "SELECT title, author
FROM books WHERE book_id = ?";
my ($title, $author) = $dbh->selectrow_array($sql_stmnt, undef, '1234');
print "$title by $author \n";
No attributes are given for the SQL statement in this example, so undef is used for the second argument. The third argument provides the book_id value for the placeholder in the SQL statement. Notice that this select_ type of database handle method does not require the use of a control statement to parse the data because it retrieves only one row of data. The prepare() method isn’t called to create a statement handle if the SQL statement is given (as it is here), which means finish() is unnecessary. However, a statement handle could be given instead.
Name
selectrow_arrayref()
Synopsis
$dbh->selectrow_arrayref($sql_statement[, \%attri][, @values])
This function returns a reference to an array of one row from the results of an SQL statement given. This method combines prepare(), execute(), and fetchrow_arrayref(). An optional second argument can specify any of the attributes allowed for a statement handle. If placeholders are used in the SQL statement, their values must be given as an array for the third argument. Here is an example:
...
my $sql_stmnt = "SELECT title, author
FROM books WHERE book_id = ?";
my $book = $dbh->selectrow_arrayref($sql_stmnt, undef, '1234');
my ($title, $author) = @$book;
print "$title by $author \n";
The prepare() method isn’t called to create a statement handle if the SQL statement is given (as it is here), which means finish() is unnecessary. However, a statement handle could be given instead.
Name
selectrow_hashref()
Synopsis
$dbh->selectrow_hashref($sql_statement[, \%attri, @values])
This function returns a reference to a hash of one row from the results of an SQL statement given. This method combines prepare(), execute(), and fetchrow_hashref(). However, a statement handle could be given. Attributes that may be given for a statement handle may be provided in a hash for the second argument of this method. If placeholders are used in the SQL statement, their values may be given as an array for the third argument.
Here is an example:
...
my $sql_stmnt = "SELECT title, author
FROM books WHERE book_id = ?";
my $book_ref = $dbh->selectrow_hashref($sql_stmnt, undef, '1234');
print "$book_ref->{title} by $book_ref->{author} \n";
Notice that this method captures the names of the columns as the keys to the values in the hash generated. Notice also that because only one row is captured, a control statement is unnecessary.
Name
set_err()
Synopsis
$handle->set_err($err, $errstr[, $state[, $method[, $return_value]]])
This function sets the values for err, errstr, and state for the handle. The method (e.g., RaiseError) can be changed as well. It returns undef unless a different return value is given as the fifth argument to this method. You can use this manually to return an error message to a user. Here is an example:
...
my $book_id = shift;
my $books = &get_data_ref($book_id)
or print "Error: " . DBI->err . DBI->errstr;
...
sub get_data_ref {
my $book_id = shift;
if($book_id =~ m/\D/g) {
return $dbh->DBI::set_err(500, "\nYou entered '$book_id'.\nBad Book
ID!");
last;
}
...
}
Notice in the subroutine that if it is given a book identifier that contains any nonnumeric characters, it does not proceed and instead returns the error as set by set_err. The line of code at the top of the excerpt that calls the subroutine will display the results if true, or display the error number and string. Here are the results of the program when a user enters a book ID that contains a letter:
Error: 500
You entered '100g'?
Bad Book ID!
Name
state()
Synopsis
$handle->state()
This method returns the error code of an error in a five-character format, in the SQLSTATE format. It doesn’t seem to be supported in MySQL yet, so it returns either an empty value or, for a general error, S1000. Here is an example:
$state = $dbh->state();
print "SQLSTATE: $state";
Name
statistics_info()
Synopsis
$dbh->statistics_info($catalog, $database, $table, unique_only, quick)
This method is meant to return an active statement handle that can be used to retrieve statistical information about a given table and its indexes. It’s experimental at the time of this writing and its syntax, results, and usage may change. The unique_only argument may be set to 1 or 0 to indicate whether or not information only on unique indexes should be retrieved. If quick is set to 1, then some information is not returned unless it can be retrieved quickly.
Name
swap_inner_handle()
Synopsis
$handle->swap_inner_handle($handle)
This method is used to swap handles. However, it’s better to create new handles or use some other method within your program. Both the handle that calls the method and the other handle given for the parameter of this method must be of the same type and have the same parent (i.e., $dbh is parent of both $sth1 and $sth2).
Name
table_info()
Synopsis
$dbh->table_info($catalog, $database, $table, $type[, \%attri])
This function returns a statement handle for fetching information about the tables in a given database. In MySQL, any parameters given are ignored, the values from the database handle are used instead, and a list of tables and views for the database is returned. Here is an example:
...
my $dbinfo = $dbh->table_info();
while( my($qualifier,$owner,$name,$type,$remarks) =
$dbinfo->fetchrow_array()) {
foreach ($qualifier,$owner,$name,$type,$remarks) {
$_ = '' unless defined $_;
}
print "$qualifier $owner $name $type $remarks \n";
}
Name
tables()
Synopsis
$dbh->tables($catalog, $database, $table, $type)
This function returns an array containing a list of tables and views for a database handle. In MySQL, the parameters are ignored and the values are drawn from the database handle. Here is an example:
my @tables = $dbh->tables();
foreach $table(@tables) {
print "$table \n";
}
Name
take_imp_data()
Synopsis
$dbh->take_imp_data($catalog, $database, $table)
This method severs the database handle that calls it from the API connection data. It returns a binary string of implementation data from the driver about the connection that was severed. This method can cause problems and shouldn’t typically be used. It’s primarily used when programming a multithreaded connection pool.
Name
trace()
Synopsis
$handle->trace(level[, log]) |
DBI->trace()
This method sets the trace level for a handle. A level of 0 disables tracing; level 1 traces the execution of the database handle; level 2 provides more details including parameter values. If a filename is given as the second argument, trace information will be appended to that log file instead ofstderr. If DBI->trace() syntax is used instead of a statement handle, it will set the trace level globally. It will return the trace settings it had before it was called as well.
Name
trace_msg()
Synopsis
$handle->trace_msg(message[, minimum_level]) |
DBI->trace_msg()
This function adds text given in the first argument to trace data. A minimum trace level (see the trace() method discussed previously) required for the message to be used may be specified as a second argument. The DBI->trace_msg() syntax uses the given message globally.
Name
type_info()
Synopsis
$dbh->type_info([$data_type])
This function returns a hash containing information on a given data type. If no data type is given, or if SQL_ALL_TYPES is given, all will be returned in the hash. The following example shows how this method might be used and lists all the possible results:
...
my $dbinfo = $dbh->type_info();
while(my($key, $value) = each(%$dbinfo)){
print "$key => $value\n";
}
Name
type_info_all()
Synopsis
$dbh->type_info_all()
This function returns a reference to an array of all data types supported by the driver. The following program excerpt shows how it may be used and shows the results of the method:
my @dbinfo = $dbh->type_info_all();
my $dbinfo_hashref = $dbinfo[0];
while( my($key,$value) = each(%$dbinfo_hashref)){
print "$key => @$value\n";
}
Attributes for Handles
This section lists the attribute keys and values that can be given in many Perl DBI methods, as indicated in the previous section with %attri in each method’s syntax. The basic syntax to set an attribute is $handle->attribute=>'setting'. Attribute key/value pairs are separated by commas and are all contained within a pair of curly braces. For example, to instruct DBI not to return error messages for a database handle, you would do the following when it’s created:
my $dbh = DBI->connect('DBI:mysql:bookstore:localhost',
'paola','caporalle1017', {RaiseError=>0});
To retrieve a setting, use $handle->{attribute}. This can be stored to a variable or printed:
print "dbh->{RaiseError=>" . $dbh->{RaiseError} . "}";
If you try this simple line of code, keep in mind that an attribute set to 0 will return an empty value.
Attributes for All Handles
You can use the following attributes with both database handles and statement handles:
Active (boolean, read-only)
This attribute indicates that the handle is active. In the case of a database handle, it indicates that the connection is open. The disconnect() method sets this attribute to 0 in a database handle; finish() sets it to 0 in a statement handle.
ActiveKids (integer, read-only)
This attribute provides the number of active handles under the handle that employed the attribute. If called by a driver handle, the number of database handles will be returned. If called by a database handle, the number of active statement handles will be returned.
CacheKids (hash ref)
This attribute returns a reference to a hash containing child handles for a driver or for a database handle that was created by the connect_cached() or prepare_cached() methods, respectively.
ChildHandles (array ref)
This attribute returns a reference to an array to all accessible handles created by the handle that called this method. These are weak references and the referenced arrays may not be dependably available.
ChopBlanks (boolean, inherited)
This attribute trims trailing spaces from fixed-width character fields (i.e., CHAR fields of results sets).
CompatMode (boolean, inherited)
This attribute makes emulation layers compatible with a driver handle. It is not normally used in applications.
ErrCount (unsigned integer)
This attribute keeps a count of the number of errors logged by set_err().
Executed (boolean)
This attribute determines whether a handle or one of its children has been executed.
FetchHashKeyName (string, inherited)
This attribute instructs fetchrow_hashref() calls to convert column names to either all lowercase (NAME_lc) or all uppercase (NAME_uc) letters. The default is NAME, which indicates no conversion should be performed.
HandleError (code ref, inherited)
This attribute customizes the response to an error caused by the handle. You could use this attribute to run a subroutine in the event of an error:
$dbh->{HandleError=> \&my_sub_routine });
HandleSetErr (code ref, inherited)
This attribute customizes the settings for err, errstr, and state values of an error caused by the handle. It’s similar to the HandleError attribute, but it relates to set_err().
InactiveDestroy (boolean)
This attribute prevents the server from destroying a handle that is out of scope, unless it is closed intentionally with a function such as finish() or disconnect().
Kids (integer, read-only)
This attribute provides the number of all handles (active and inactive) under the handle that employed the attribute. If it’s called by a database handle, the number of statement handles will be returned. If it’s called by a driver handle, the number of database handles will be returned.
LongReadLen (unsigned integer, inherited)
This attribute sets the maximum length of data retrieved from long data type columns (i.e., BLOB and TEXT).
LongTruncOK (boolean, inherited)
If this attribute is set to true, it may prevent a fetch method from failing if a column’s data length exceeds the maximum length set by the LongReadLen attribute.
PrintError (boolean, inherited)
If this attribute is set to 1, error codes and error messages associated with the handle will be logged. If it’s set to 0, they won’t be logged.
PrintWarn (boolean, inherited)
Setting this attribute to 1 will instruct DBI to log warning messages for the handle. Setting it to 0 will instruct it not to log them.
private_*
This attribute stores information on the handle as a private attribute with a customized name starting with private_.
Profile (inherited)
This attribute enables the logging of method call timing statistics.
RaiseError (boolean, inherited)
This attribute instructs DBI to raises exceptions when errors are associated with the handle. By default it’s set to 0. If set to 1, any DBI error will cause the program to die. If you set this attribute to true, you should also set PrintError to true.
ReadOnly (boolean, inherited)
Setting this attribute on a handle to true indicates that all actions with the handle afterward will be read-only activities—data won’t be changed using the handle.
ShowErrorStatement (boolean, inherited)
If set to true, this attribute specifies that the SQL statement text of a statement handle should be appended to error messages stemming from the PrintError, PrintWarn, and RaiseError attributes being set to true.
Taint (boolean, inherited)
This attribute combines TaintIn and TaintOut attributes. Whatever value you set with this attribute will be set for the other two attributes.
TaintIn (boolean, inherited)
This attribute instructs DBI to check whether method calls are tainted, when Perl is running in taint mode.
TaintOut (boolean, inherited)
This attribute instructs DBI to assume that data fetched is tainted, when Perl is running in taint mode.
TraceLevel (integer, inherited)
This attribute sets trace levels and flags for a handle. It’s an alternative to the trace() method.
Type (scalar, read-only)
This attribute is used to determine the type of handle. It returns dr for a driver handle, db for a database handle, and st for a statement handle.
Warn (boolean, inherited)
This attribute enables or disables warning messages for poor database procedures.
Attributes Only for Database Handles
AutoCommit (boolean)
This attribute allows the rollback() function to be used if the attribute is set to 0. At the time of this writing, a bug sometimes produces an error when using this attribute.
Driver (handle)
This attribute provides the name of the parent driver: $dbh->{Driver}->{Name}.
Name (string)
This attribute provides the name of the database for the database handle.
RowCacheSize (integer)
This attribute is used to suggest a cache size for rows generated for SELECT statements. If it’s 0, DBI automatically determines the cache size. A value of 1 disables local row caching.
Statement (string, read-only)
This attribute provides the last SQL statement prepared with the database handle, regardless of whether it succeeded.
Username (string)
This attribute provides the name of the user for the database handle.
Attributes Only for Statement Handles
CursorName (string, read-only)
This attribute returns the name of the cursor for the statement handle.
Database (dbh, read-only)
This attribute returns the database handle of the statement handle.
NAME (array-ref, read-only)
This attribute contains a reference to an array containing the names of the columns of the SQL statement from the statement handle.
NAME_hash (hash-ref, read-only)
This attribute returns a reference to a hash containing column name information.
NAME_lc (array-ref, read-only)
This attribute returns a reference to an array containing column name information. The keys are the column names in lowercase letters.
NAME_lc_hash (hash-ref, read-only)
This attribute returns a reference to a hash containing column name information. The keys are the column names in lowercase letters.
NAME_uc (array-ref, read-only)
This attribute returns a reference to an array containing column name information. The keys are the column names in uppercase letters.
NAME_uc_hash (hash-ref, read-only)
This attribute returns a reference to a hash containing column name information. The keys are the column names in uppercase letters.
NULLABLE (array-ref, read-only)
This attribute returns a reference to an array indicating whether each column in the SQL statement of the handle may contain a NULL value.
NUM_OF_FIELDS (integer, read-only)
This attribute returns the number of columns in the SQL statement of the handle.
NUM_OF_PARAMS (integer, read-only)
This attribute returns the number of placeholders in the SQL statement of the handle.
ParamArrays (hash ref, read-only)
This attribute returns a reference to a hash containing the names of placeholders as keys and their associated values for calls made to bind_param_array() and execute_array().
ParamTypes (hash ref, read-only)
This attribute returns a reference to a hash containing information about placeholders that are bound by calls made to bind_param(). The placeholder names are used as the keys of the hash.
ParamValues (hash ref, read-only)
This attribute returns a reference to a hash of bound parameters and their values.
RowsInCache (integer, read-only)
This attribute returns the number of unfetched rows in the cache if the driver supports row-level caching.
Statement (string, read-only)
This attribute is the SQL statement passed to prepare().
TYPE (array-ref, read-only)
This attribute contains a reference to an array of codes for international standard values for data types (e.g., 1 for SQL_CHAR, 4 for SQL_INTEGER).
PRECISION (array-ref, read-only)
This attribute contains a reference to an array containing the length of columns (as set in the table definition) in the SQL statement of the handle.
SCALE (array-ref, read-only)
This attribute contains a reference to an array containing the number of decimal places for columns in the SQL statement of the handle.
DBI Dynamic Attributes
These attributes are related to the last handle used, regardless of the type of handle. The syntax of each of these is $DBI::attribute:
err
This attribute is synonymous with $handle->err.
errstr
This attribute is synonymous with $handle->errstr.
lasth
This attribute returns the handle used by the last method call.
rows
This attribute is synonymous with $handle->rows.
state
This attribute is synonymous with $handle->state.