17.4. What Is the Perl DBI?

The DBI is a layer of "glue" between an application and one or more database driver modules.

—Tim Bunce, author of DBI

DBI stands for the Database Independent Interface. DBI is an object-oriented module that allows your Perl application to talk to many different types of databases using the same method calls, variables, and conventions. It locates the database driver module (DBD) for a particular database system and dynamically loads the appropriate DBD module. The database driver contains the libraries necessary to talk to a specific database. For example, to connect to a MySQL database, you need to install the DBD-MySQL driver, and in order to talk to an Oracle database, you need the DBD-Oracle driver. DBI acts as the interface between your Perl script and the database driver modules; i.e., it translates Perl output to code that can be understood by a specific driver whether that driver is Oracle, Sybase, MySQL, etc. You set up the SQL query string and send it via a DBI method to the appropriate database driver, and you get back results that can be managed in your Perl program in the same way no matter what database you are using.

17.4.1. Installing the DBI

DBI-MySQL with PPM

Installation of the DBI is easy if you are using PPM (Perl Package Manager) from ActiveState. PPM is a package-management utility that simplifies finding, installing, upgrading, and removing Perl modules. Today the default version of PPM is a graphical user interface, but you can also use the command-line interface as shown in Example 17.23.

PPM Quickstart

The following example is taken from the PPM beginner's guide to help guide you through the steps for installing and removing modules from CPAN by using PPM at the command line.

Example 17.23.

Code View: (At the command line) ppm help quickstart quickstart -- a beginners' guide to PPM3 Description PPM (Programmer's Package Manager) is a utility for managing software "packages". A package is a modular extension for a language or a software program. Packages reside in repositories. PPM can use three types of repositories: 1) A directory on a CD-ROM or hard drive in your computer 2) A website 3) A remote Repository Server (such as ASPN) Common Commands: To view PPM help: help help <command> To view the name of the current repository: repository To search the current repository: search <keywords> To install a package: install <package_name> Most commands can be truncated; as long as the command is unambiguous, PPM will recognize it. For example, 'repository add foo' can be entered as 'rep add foo'. PPM features user profiles, which store information about installed packages. Profiles are stored as part of your ASPN account; thus, you can easily maintain package profiles for different languages, or configure one machine with your favorite packages, and then copy that installation to another machine by accessing your ASPN profile. For more information, type 'help profile' at the PPM prompt.

Steps to Install with PPM

The simple steps to install the necessary modules are listed here.

Example 17.24.

Code View: 1 $ ppm PPM - Programmer's Package Manager version 3.1. Copyright (c) 2001 ActiveState Corp. All Rights Reserved. ActiveState is a devision of Sophos. Entering interactive shell. Using Term::ReadLine::Stub as readline library. Type 'help' to get started. Setting 'target' set to 'ActivePerl 5.8.4.810'. ppm> 2 ppm> query * Querying target 1 (ActivePerl 5.8.7.813) 1. ActivePerl-DocTools [0.04] Perl extension for Documentation TOC Generation 2. ActiveState-RelocateTree [0.03] Relocate a Perl installation 3. ActiveState-Rx [0.60] Regular Expression Debugger 4. Archive-Tar [1.07] Manipulates TAR archives 5. Compress-Zlib [1.22] Interface to zlib compression library 6. Data-Dump [1.01] Pretty printing of data structures 7. DBD-mysql [2.9003] MySQL driver for the Perl5 Database Interface (DBI) 8. DBI [1.47] Database independent interface for Perl 9. Digest [1.0] Modules that calculate message digests 10. Digest-HMAC [1.01] Keyed-Hashing for Message Authentication 11. Digest-MD2 [2.03] Perl interface to the MD2 Algorithm 12. Digest-MD4 [1.1] Perl interface to the MD4 Algorithm 13. Digest-MD5 [2.20] Perl interface to the MD5 Algorithm 14. Digest-SHA1 [2.06] Perl interface to the SHA-1 Algorithm 15. File-CounterFile [1.01] Persistent counter class 16. Font-AFM [1.18] Interface to Adobe Font Metrics files 17. HTML-Parser [3.34] HTML parser class 18. HTML-Tagset [3.03] Data tables useful in parsing HTML 19. HTML-Tree [3.18] build and scan parse-trees of HTML 20. IO-Zlib [1.01] IO:: style interface to Compress::Zlib 21. libwin32 [0.21] A collection of extensions that aims to provide comp~ 22. libwww-perl [5.75] Library for WWW access in Perl 23. Mail-Sendmail [0.79] Simple platform independent mailer 24. MD5 [2.02] Perl interface to the MD5 Algorithm (obsolete) 25. MIME-Base64 [2.12] Encoding and decoding of base64 strings 26. PPM [2.1.6] Perl Package Manager: locate, install, upgrade softw~ 27. PPM-Agent-Perl [3.0.4] PPM Installer Backend for Perl 28. PPM3 [3.1] Perl Package Manager: locate, install, upgrade softw~ 29. SOAP-Lite [0.55] Library for Simple Object Access Protocol (SOAP) cli~ 30. Storable [1.0.12] persistency for perl data structures 31. Tk [800.024] A Graphical User Interface Toolkit 32. URI [1.27] Uniform Resource Identifiers (absolute and relative) 33. Win32-AuthenticateUser [0.02] Win32 User authentication for domains 34. XML-Parser [2.34] A Perl module for parsing XML documents 35. XML-Simple [2.09] Easy API to read/write XML (esp config files) ppm> 3 ppm> describe DBI ==================== Name: DBI Version: 1.50 Author: Tim Bunce (dbi-users@perl.org) Title: DBI Abstract: Database independent interface for Perl Location: ActiveState PPM2 Repository Available Platforms: 1. MSWin32-x86-multi-thread-5.8 ==================== 4 ppm> describe DBD-mysql ==================== Name: DBD-mysql Version: 3.0002 Author: Patrick Galbraith (patg@mysql.com) Title: DBD-mysql Abstract: A MySQL driver for the Perl5 Database Interface (DBI) Location: ActiveState PPM2 Repository Prerequisites: 1. DBI 0.0 Available Platforms: 1. MSWin32-x86-multi-thread-5.8 ==================== ppm>q To install the DBI (Database Interface for Perl) and DBD-mysql (Driver for MySQL) from CPAN, start PPM and execute the install command as follows: 5 ppm> query * 6 ppm> install DBI 7 ppm> install DBD-mysql

The PPM GUI

When the Perl Package Manager is initially displayed, it will synchronize the ActiveState repository package list from its database. You can view all the currently installed packages or all the packages in the repository and use the search box to find a module you are looking for. If found, it will be highlighted.

Figure 17.20. Package marked for install.

[View full size image]

To install marked packages, click on the green arrow in the right-hand portion at the top of the screen.

Using PPM with Linux

If you are using Linux, you can download Perl 5.8.8 from ActiveState as an rpm (RedHat Package Manager) file or as a tarfile. The instructions for downloading ActivePerl are found at http://aspn.activestate.com/ASPN/docs/Active-Perl/5.8/install.html. Then run the install.sh shell script found in the perl directory. After you run the install script, set your PATH to the perl directory. Then you can use the PPM program to install modules from CPAN just as you would with Windows. This is by far easier than using CPAN with all its questions and issues.

You need to be connected to the Internet to install the required packages. To see the installed packages, type:

     \rpm -i Active-State.....rpm
     sh install.sh

Installing DBI Using CPAN

The primary tool used to maintain a local Perl distribution is the CPAN module, used to access the Comprehensive Perl Archive Network, aka CPAN. At the command-line prompt, type:

     $ perl -MCPAN -e shell

At the CPAN prompt, type "help" and you will see a list of options. To get the latest version of CPAN, type at your CPAN prompt:

     cpan >install Bundle::CPAN

After a barrage of questions, don't be surprised if you end up with:

Stop.
 nmake -- NOT OK
Running make test
 Can't test without successful make
Running make install
 make had returned bad status, install seems impossible

That's why it's easier if you can use PPM.

Try these steps for RedHat:

     perl -MCPAN -e 'install Bundle::DBD::mysql'

Example 17.25.

Code View: cpan> h Display Information command argument description a,b,d,m WORD or /REGEXP/ about authors, bundles, distributions, modules i WORD or /REGEXP/ about anything of above r NONE reinstall recommendations ls AUTHOR about files in the author's directory Download, Test, Make, Install... get download make make (implies get) test MODULES, make test (implies make) install DISTS, BUNDLES make install (implies test) clean make clean look open subshell in these dists' directories readme display these dists' README files Other h,? display this menu ! perl-code eval a perl command o conf [opt] set and query options q quit the cpan shell reload cpan load CPAN.pm again reload index load newer indices autobundle Snapshot force cmd unconditionally do cmd cpan>

17.4.2. The DBI Class Methods

The DBI module is object oriented and comes with a number of methods and variables. The documentation for this module is listed next. The database objects are called handles. Database handles connect to a specific database, and statement handles are used to send SQL statements to the database. Notice that names such as $dbi, $sth, $rc, etc., are use to describe statement handles, return codes, rows of data, etc. (These names are conventions only in the documentation; e.g., $dbh represents a database handle, and $sth a statement handle.)

$ perldoc DBI
 Notation and Conventions
   The following conventions are used in this document:

     $dbh    Database handle object
     $sth    Statement handle object
     $drh    Driver handle object (rarely seen or used in applications)
     $h      Any of the handle types above ($dbh, $sth, or $drh)
     $rc     General Return Code  (boolean: true=ok, false=error)
     $rv     General Return Value (typically an integer)
     @ary    List of values returned from the database, typically a row
of dat

     $rows   Number of rows processed (if available, else -1)
     $fh     A filehandle
     undef   NULL values are represented by undefined values in Perl
     \%attr  Reference to a hash of attribute values passed to methods

   Note that Perl will automatically destroy database and statement
   handle objects if all references to them are deleted.

NAME
   DBI - Database independent interface for Perl

SYNOPSIS
     use DBI;
     @driver_names = DBI->available_drivers;
     @data_sources = DBI->data_sources($driver_name, \%attr);
     $dbh = DBI->connect($data_source, $username, $auth, \%attr);

     $rv  = $dbh->do($statement);
     $rv  = $dbh->do($statement, \%attr);
     $rv  = $dbh->do($statement, \%attr, @bind_values);

     $ary_ref  = $dbh->selectall_arrayref($statement);
     $hash_ref = $dbh->selectall_hashref($statement, $key_field);

     $ary_ref  = $dbh->selectcol_arrayref($statement);
     $ary_ref  = $dbh->selectcol_arrayref($statement, \%attr);

     @row_ary  = $dbh->selectrow_array($statement);
     $ary_ref  = $dbh->selectrow_arrayref($statement);
     $hash_ref = $dbh->selectrow_hashref($statement);

     $sth = $dbh->prepare($statement);
     $sth = $dbh->prepare_cached($statement);

     $rc = $sth->bind_param($p_num, $bind_value);
     $rc = $sth->bind_param($p_num, $bind_value, $bind_type);
     $rc = $sth->bind_param($p_num, $bind_value, \%attr);
     $rv = $sth->execute;
     $rv = $sth->execute(@bind_values);
     $rv = $sth->execute_array(\%attr, ...);

     $rc = $sth->bind_col($col_num, \$col_variable);
     $rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind);

     @row_ary  = $sth->fetchrow_array;
     $ary_ref  = $sth->fetchrow_arrayref;
     $hash_ref = $sth->fetchrow_hashref;

     $ary_ref  = $sth->fetchall_arrayref;
     $ary_ref  = $sth->fetchall_arrayref( $slice, $max_rows );

     $hash_ref = $sth->fetchall_hashref( $key_field );

     $rv  = $sth->rows;

     $rc  = $dbh->begin_work;
     $rc  = $dbh->commit;
     $rc  = $dbh->rollback;

     $quoted_string = $dbh->quote($string);

     $rc  = $h->err;
     $str = $h->errstr;
     $rv  = $h->state;

     $rc  = $dbh->disconnect;

   *The synopsis above only lists the major methods and parameters.*

					  

17.4.3. How to Use DBI

Once you load the DBI module into your program with the use DBI statement, there are only five steps involved: connect to a database, prepare a query, execute the query, get the results, and disconnect.

To connect to MySQL, use the connect() method. This method specifies the type of database (MySQL, Oracle, Sybase, CSV files, Informix, etc.), the database name, host name, user and password with some additional, optional arguments to specify error and transaction handling, etc. It returns a database handle ($dbh is used in the manual page, but you can call it any valid scalar name).

Once connected to the MySQL database, you have a database handle (reference to the database object). Now you can send a query by preparing and executing a SQL statement. This can be done by calling the prepare() and execute() methods or by using the do() method. The prepare() and execute() methods are used for SELECT statements, whereas the do() method is normally used for SQL statements that don't return a result set, such as the INSERT, UPDATE, or DELETE statements. What is returned from these methods depends on what the query returns. For example, successful SELECT queries return a result set (represented as $sth in the DBI manual page); successful INSERT/UPDATE/DELETE queries with do() return the number of rows affected; and unsuccessful queries return an error or undef. Most data is returned to the Perl script as strings, and null values are returned as undef.

Once the query has been sent to the database and a result set returned (reference to the result object), you can extract the data with special methods, such as fetchrow_array() and fetchrow_hashref(). These methods retrieve each record as a Perl array or a Perl hash, respectively.

Finally, when you are done, the finish() method releases the result object returned from prepare() method, and the disconnect() method ends the session by disconnecting from the database.

Now we will go through each of these five steps in detail.

17.4.4. Connecting to and Disconnecting from the Database

Once loaded, the DBI module is responsible for loading the appropriate driver for a given database. Then you will work with the database by using the methods provided by the module listed in the output shown from perldoc DBI. The first method we will use is the connect() method to get a connection to the database, and the last method, disconnect(), to break the connection.

Checking Available Database Drivers for DBI

Example 17.26.

1 use DBI; 2 my @drivers = DBI->available_drivers; 3 print join(", ", @drivers),"\n"; (Output for Windows) DBM, ExampleP, File, Gofer, Proxy, SQLite, Sponge, mysql

The connect() Method

The connect() method establishes a connection to the specified database and returns an object called a database handle. You can make multiple connections to the same database or even to different databases in a program by using multiple connect statements. The connect() method takes several arguments:

   "dbi:$driver:$database,$port,$username,$password"

Example 17.27.

1 $dbh= DBI->connect("dbi:<RDMS>:<database>","<username>","<password>", \%attributes) or die("Couldn't connect"); 2 $dbh=DBI->connect('DBI:mysql:sample_db','root','quigley1') or die "Can't connect"; 3 $dbh=DBI->connect('DBI:mysql:database=sample_db;user=root; password=quigley1'); 4 $dsn = dbi:mysql:northwind; $username="root"; $password="letmein"; $dbh = DBI->connect($dsn, $user, $password, { PrintError => 0, RaiseError => 1, AutoCommit => 0 }); -------------------Using Other Database Systems------------ 5 $dbh = DBI->connect('dbi:Oracle:payroll','scott','tiger'); $dbh = DBI->connect("dbi:Oracle:host=torch.cs.dal.ca;sid=TRCH", $user, $passwd); (Oracle) 6 $dbh = DBI->connect('dbi:odbc:MSS_pubs','sa', '12mw_1'); (MS SQL Server) Explanation The connect method will return a database handle. This is the format you use to connect. At minimum, you must have the DSN string, which is the name of the module, dbi, the name of the database driver, and the name of the database represented as <database>. The username, hostname, password, and other attributes are optional. The arguments to the connect method should be on one line with no spaces around the arguments. DBI->errstr returns the reason why we couldn't connect—"Bad password," for example. This connection will be made to a MySQL database named "northwind", with a user name "root" and password "letmein". The attributes are to turn on error. This connection will be made to an Oracle database called "payroll", with a username of "scott" and a password "tiger". The data source name of "MSS_pubs" uses the username "sa", password "12mw_l", and the ODBC driver.

17.4.5. The disconnect() Method

As you may remember from Chapter 10, after we opened a file with a user-defined filehandle, we closed it with the built-in close function when finished, and had we forgotten to close it, it would be left up to the operating system to do so when the Perl script exited. The same thing is true with closing a database. After you are finished using the database, it is always a good idea to close the connection with the disconnect method. Of course, because the database handle is an object, Perl will automatically remove the reference to it once the program exits or the object goes out of scope.

     $dbh->disconnect();

17.4.6. Preparing a Statement Handle and Fetching Results

The SQL select statement is probably the statement used most when querying a database. When preparing a select statement for the database, the query is sent as a string argument to the DBI prepare method. The query is just as you would type it in the MySQL console but minus the semicolon. The database handle calls the prepare method. The database determines how it will execute the query (creates a "plan"), and DBI returns a statement handle containing the details on how the query will be executed. The statement handle (represented as $sth) encapsulates individual SQL statements to be executed within the database and calls the execute method.

This execute method tells the database to perform the SQL statement (execute its "plan") and to return the results back to the program. An undef is returned if an error occurs. A successful execute always returns true regardless of the number of rows affected, even if zero rows were affected. Once executed, the "plan" is discarded (see the prepare_cache method, on page 664). The number of statement handles that can be created and executed is basically unlimited.

Even though you have executed the plan, you can't see the results unless you use another DBI method to retrieve it, such as dump_results(), fetchrow_array(), or fetch(), etc.

Select, Execute, and Dump the Results

The DBI dump_results method gets all the rows from the statement handle object and prints the results in one simple statement.

Connect, Prepare, Execute, and Dump Results

Example 17.28.

(The Script) use DBI; 1 $db= DBI->connect('DBI:mysql:sample_db;user=root;password=quigley1'); 2 $sth=$db->prepare("SELECT * FROM coaches") or die "Can't prepare sql statement" . DBI->errstr; 3 $sth->execute(); print qq(\n\tContents of "coaches" table\n); 4 $sth->dump_results(); # Display results of the execute 5 $sth->finish(); 6 $dbh->disconnect(); (Output) Contents of "coaches" table '1', 'John Doe', 'Chico Hardhats', 'Head Coach', '2002-12-10' '2', 'Jack Mattsone', 'CHardhats', 'Offensive Coach', '2004-10-05' '3', 'Bud Wilkins', 'Fremont Tigers', 'Head Coach', '1999-09-06' '4', 'Joe Hayes', 'Fremont Tigers', 'Defensive Coach', '1998-06-16' '5', 'George Jones', 'Bangor Rams', 'Offensive Coach', '2003-09-03' '6', 'Jerry O'Connell','Portland Penguins','Head Coach', '2006-02-22' 6 rows Explanation A connection is made to the MySQL database called sample_db and a database handle is returned called $dbh. It is the object that represents the connection. Now we have access to the database. The prepare method is used to prepare the SQL query. It returns a statement handle, an object that encapsulates the query and prepares it for execution. (Note that the SQL statement does NOT end with a semicolon.) The execute method causes the query to actually be excuted. Now we are ready to retrieve the results of the query. The dump_results method is called by the statement handle and prints the results of the query.

Select, Execute, and Fetch a Row as an Array

When the fetchrow_array() method is called, the database will return the first row of results as an array, where each field is an element in the array. Each successive call to the fetchrow_array() method yields the next row of results, until there are no more results and the call yields a value of undef. A while or for loop can be used to fetch all of the rows as shown in Example 17.29. ( An important point to remember is that when the fields are fetched, they are assigned in the order they were listed in the SQL query.)

Connnect, Prepare, Execute, and Fetch the Data with fetchrow_array()

Example 17.29.

Code View: (The Script) use DBI; 1 my $dbh=DBI->connect(qq(DBI:mysql:database=sample_db;user=root; password=quigley1)) or die "Can't connect"; 2 my $sth=$dbh->prepare("SELECT name, wins, losses FROM teams"); 3 $sth->execute(); print "Contents of sample_db, the mysql database.\n\n"; 4 while(my @row=$sth->fetchrow_array()){ # Get one row at a time 5 print "name=$row[0]\n"; # Field one print "wins=$row[1]\n"; # Field two print "losses=$row[2]\n\n"; # Field three } 6 print $sth->rows, " rows were retrieved.\n"; 7 $sth->finish(); 8 $dbh->disconnect(); (The Output) Contents of sample_db, the mysql database. name=Fremont Tigers wins=24 losses=26 name=Chico Hardhats wins=19 losses=25 name=Bath Warships wins=32 losses=3 name=Bangor Rams wins=22 losses=24 4 rows were retrieved. Explanation The connect() method returns a database handle, $dbh, an object that references the MySQL database called sample_db. A SQL select statement is prepared and a statement handle returned, called $sth. The query is sent to the database for execution. The fetch_row_array() method returns the first row from the database, where fields are elements of the array, called @row. To get subsequent rows, a while loop is used. The loop ends when there are no more rows. Each field from a row is assigned to a variable and printed. The rows() method returns the number of rows affected by the statement handle. The finish method releases the statement handle. The disconnect method releases the database handle.

Select, Execute, and Fetch a Row as a Hash

The fetchrow_hashref() method fetches a row from the database table as a hash reference where the keys are the names of the columns and the values are the data stored in that column. The following example is exactly like the previous one, except fetchrow_arrayref() is replaced with fetchrow_hashref().

Example 17.30.

use DBI; 1 $dbh=DBI->connect(qq(DBI:mysql:database=sample_db;user=root; password=quigley1)) or die "Can't connect"; 2 $sth=$dbh->prepare("SELECT name, wins, losses FROM teams") ; 3 $sth->execute(); $count=0; print "Contents of sample_db, the mysql database.\n\n"; 4 while( my $row = $sth->fetchrow_hashref()){ 5 print "Name: $row->{name}\n"; print "Wins: $row->{wins}\n"; print "Losses: $row->{losses}\n\n"; $count++; } 6 print "There are $count rows in the sample database.\n"; 7 $sth->finish(); 8 $dbh->disconnect(); Explanation The connect() method returns a database handle, $dbh, an object that references the MySQL database called sample_db. A SQL select statement is prepared and a statement handle returned, called $sth. The query is sent to the database for execution. The fetch_row_hashref() method returns the first row from the database as a reference to an anonymous hash consisting of key/value pairs. The key is the name of the field in the table and the value is what is stored there. To get subsequent rows, a while loop is used. The loop ends when there are no more rows. Each value from the field, specified as a key, is printed. The finish method releases the statement handle. The disconnect method releases the database handle.

17.4.7. Handling Quotes

When strings are sent to a database, they are enclosed in quotes. Strings themselves may also contain quotes as in the string "Mrs. O'Donnell", and these quotes must be properly escaped when sent to a database. To make things more complicated, different database systems have different rules for handling quotes. The DBI module handles quoting issues with its quote method. This method is used with a database handle to convert a string according to rules defined for a specific database and returns the string correctly escaped.

Example 17.31.

Code View: use DBI; $dbh=DBI->connect(qq(DBI:mysql:database=sample_db;user=root; password=quigley1)) or die "Can't connect"; 1 $namestring=qq(Jerry O'Connell); 2 $namestring=$dbi->quote($string); 3 print $namestring; 4 $sth=$dbi->prepare("SELECT * FROM coaches WHERE name=$namestring") or die "Can't prepare sql statement" . DBI->errstr; $sth->execute(); 5 print qq(\nContents of "coaches" table\n); 6 while(my @val = $sth->fetchrow_array()){ print "\tid=$val[0]\n"; print "\tname=$val[1]\n"; print "\tteam_name=$val[2]\n"; print "\tteam_name=$val[3]\n"; print "\tstart_date=$val[4]\n\n"; } $sth->finish(); $dbh->disconnect(); (Output) 2 'Jerry O\'Connell' 5 Contents of "coaches" table 6 id=6 name=Jerry O'Connell team_name=Portland Penguins team_name=Head Coach start_date=2006-02-22 Explanation 1 A string variable, $namestring, is assigned a string of characters containing a single quote. 2 A DBI quote method is used to prepare the string for the mysql database by enclosing the string in quotes and escaping the single quote with a backslash. 3 This line shows you how the quote method prepared the string. Notice the apostrophe in O'Connell is escaped with a backslash. 4 In the WHERE clause the properly quoted string will be used to test whether its value matches the name of a coach in the coaches table. The following line illustrates how data is inserted at the MySQL client. You can see how quotes are managed in the string; i.e., single quote is embedded in double quotes: mysql> insert into coaches values(',',"Jerry O'Connell",'Portland Penguins', 'Head Coach','2006-2-22'); 5, 6 The table is displayed.

17.4.8. Getting Error Messages

It is important to know what went wrong when working with DBI. Did the connection fail? Did you prepare the SQL statement correctly? DBI defines several ways to handle errors. You can use automatic error handling with the PrintError and RaiseError attributes for a specific handle, or you can use diagnostic methods and special DBI variables.

Automatic Error Handling

The DBI module provides automatic error handling when you connect to the database. You can either get warnings every time a DBI method fails or have the program send a message and abort. The two attributes most often used with the connect method are PrintError and RaiseError.

The PrintError Attribute

By default, the connect() method sets PrintError to "on" (set to 1) and automatically generates a warning message if any of the DBI methods fails.

The RaiseError Attribute

The RaiseError attribute can be used to force errors to raise exceptions. It is turned off by default. When set to "on," any DBI method that results in an error will cause DBI to die with an error, $DBI::errstr. If you turn RaiseError on, then you would normally turn PrintError off. If PrintError is also on, then the PrintError is done first. Typically, RaiseError is used in conjunction with an eval block so that you can catch the exception that's been thrown. If there is a "die," a compile, or runtime error in the eval block, the special variable $@ is set to the error message and set to null if there is not an error. If $@ has been set, then you can handle the error without exiting DBI. (See Example 17.32.)

Manual Error Handling

If you want to manually check for errors when a particular method fails, you can use either the error diagnostic methods or the error diagnostic variables provided by the DBI module. This gives you control over each method you call to trap the errors if they occur.

Error Diagnostic Methods

First we will look at two error diagnositc methods, err() and errst(). These methods can be invoked against any valid handle, driver, database, or statement. The err() method will return the error code associated with the problem that occurred. The error code is a number that differs depending on the database system being used. The errstr() method corresponds to the error code number but is a string that contains information as to why the last DBI method call failed. Before another method call, the error messages for a handle are reset, so they should be checked right after a specific handle has produced an error. The diagnosic methods are used as discussed next.

$rv  = $h->err();
$str = $h->errstr();

Error Diagnostic Variables

The DBI variables $DBI::err and $DBI::errstr, are class variables and behave similarly to the method described previously, except they have a shorter life span and always refer to the last handle that was used. The $DBI::err contains the error number associated with last method that was called, and $DBI:errstr contains a string describing the error message associated with the error number in $DBI::err. You should generally test the return status of connect and print $DBI::errstr if the connect() method failed.

Example 17.32.

Code View: (The Script) use DBI; $driver="DBI:mysql"; $database="sample_db"; $user="root"; $host="localhost"; $dbh=DBI->connect('dbi:mysql:sample_db','root','quigley1', { 1 RaiseError => 1, # Die if there are errors 2 PrintError => 0, # Warn if there are errors } 3 ) or die $DBI::errstr; # Report why connect failed 4 $sth=$dbh->prepare("SELECT name, wins, losses FROM teams") or die "Can't prepare sql statement" . DBI->errstr; $sth->execute(); print "Contents of sample_db, the mysql database.\n\n"; while(my @val = $sth->fetchrow_array()){ print "name=$val[0]\n"; print "wins=$val[1]\n"; print "losses=$val[2]\n\n"; } 5 print $sth->rows," rows were retrieved.\n"; $sth->finish(); $dbh->disconnect(); Explanation Here we turn on the RaiseError attribute, which will cause the program to die if there is an error from any DBI method call. The PrintError attribute is turned on by default. It sends a warning message if a method fails. It is set to 0 here to turn it off, since RaiseError is turned on. You can have both RaiseError and PrintError turn on or off. If both are turned on, the PrintError sends a warning first, and then RaiseError prints a message, and the program dies. The $DBI::errstr variable will print the reason the connection failed, if it did. This time we use the errstr method to report an error that may have occurred if the prepare method failed; i.e., the SQL statement was incorrectly prepared.

Examples of Error Messsages

Example 17.33.

1 (Bad Database Name; connect failed) DBI connect('ample_db','root',...) failed: Unknown database 'ample_db'at first.dbi line 9 2 (Bad Password; connect failed) DBI connect('sample_db','root',...) failed: Access denied for user 'root'@'localhost' (using password: YES) at first.dbi line 9 3 (Bad SQL Query; execute failed) DBD::mysql::st execute failed: Unknown column 'win' in 'field list' at first.dbi line 23.

Binding Columns and Fetching Values

Binding columns is the most efficient way to fetch data. Binding allows you to associate a Perl variable with a field (column) value in the database. When values are fetched, the variables are automatically updated with the retrieved value, making fetching data fast. DBI provides the bind_columns() method to bind each column to a scalar reference. When the fetch() method is called, the values from the database are assigned to scalars of the same name rather than to arrays or hashes as seen in the previous examples.

Every time the fetch() method is called, the scalars will be updated with values from the current row.

(See bind_col in the DBI documentation for another way to bind columns.)

Binding Columns

Example 17.34.

Code View: use DBI; my $driver="DBI:mysql"; my $database="sample_db"; my $user="root"; my $host="localhost"; my $dbh = DBI->connect("$driver:database=$database;host=$host;user=$user") or die "Can't connect: " . DBI->errstr; 1 my $sth=$dbh->prepare("SELECT name, wins, losses FROM teams") or die "Can't prepare sql statement" . DBI->errstr; 2 $sth->execute() or die "Can't prepare sql statement". $sth->errstr; ; 3 my($name, $wins, $losses);# Scalars that will be bound to columns 4 $sth->bind_columns(\$name,\$wins,\$losses); # scalar references print "\nSelected data for teams.\n\n"; printf"\t%-20s%-8s%-8s\n","Name","Wins", "Losses"; 5 while( $sth->fetch()){ # Fetch a row and return column values as scalars printf " %-25s%3d%8d\n",$name, $wins, $losses; } $sth->finish(); $dbh->disconnect(); (Output) Selected data for teams. Name Wins Losses Bath Warships 34 3 Berkeley Bombers 12 19 Denver Daredevils 23 5 Littleton's Tigers 14 18 Middlefield Monsters 2 32 Palo Alto Panthers 24 17 Portland Penguins 28 14 San Francisco Fogheads 24 12 Sunnyvale Seniors 12 24 Explanation The SQL SELECT statement is prepared and a statement handle returned. The DBI execute() method sends the query to the database for execution. Three scalar variables are created that will be bound to each of the three fields (columns) listed in the SELECT statement. The bind_columns method specifies the names of the scalar variables that will be bound to the individual fields when the result set is retrieved from the database with the fetch method. (In older versions of DBI, the first argument was specified as undef; e.g., $sth->bind_columns(undef,\$name,\$wins,\$losses);); undef is used to represent a null field. The fetch method retrieves a row from the result set and assigns each value to the variables named as arguments in the bind_columns() method. The name of the team is automatically assigned to $name, the number of wins to $wins, and the number of losses to $losses. Each time through the loop, the next row of column values will be assigned to these variables, and so on, until there is no more data.

The ? Placeholder

Placeholders, represented by a ?, are used to optimize how queries are handled. Placeholders provide a template for a query and represent values that will be assigned to fields at a later time. They are used primarily with SELECT, INSERT, UPDATE, and DELETE statements.

When a query is prepared by DBI, the database has to plan how it can best handle the query. The statement handle is used to store the prepared plan for the query, called the "execution plan." Normally, once a query has been executed, the plan is discarded. When placeholders are used, instead of discarding the execution plan, the database accepts the placeholder in a template and makes a plan around it—making the template usable for future queries.

The ? represents values, such as: name = ?, where name is a field name in the database table and its value will be supplied a value later on. ( Remember, the ? represents a value, not a field name: ? = "John" is wrong!) The execute() method takes arguments representing the values of the placeholders, and those values are replaced in the prepared plan each time the method is called. DBI has to figure out the data type of the value. (Not all databases and DBI drivers support placeholders.)

Using a Placeholder

Example 17.35.

Code View: use DBI; my $driver="DBI:mysql"; my $database="sample_db"; my $user="root"; my $host="localhost"; my $dbh = DBI->connect("$driver:$database:$host;user=$user; password=quigley1")or die "Can't connect: ". DBI->errstr; 1 my $sth=$dbh->prepare("SELECT name, wins, losses FROM teams WHERE name = ?") or die "Can't prepare sql statement" . DBI->errstr; print "Enter the team name: "; 2 chomp($team_name=<STDIN>); 3 $sth->execute($team_name); # The value of $team_name replaces the ? print "\nSelected data for team \"$name\".\n\n"; 4 while(my @val = $sth->fetchrow_array()){ print "name=$val[0]\n"; print "wins=$val[1]\n"; print "losses=$val[2]\n\n"; } $sth->finish(); $dbh->disconnect(); (Output) Enter the team name: Chico Hardhats Selected data for team "Chico Hardhats". name=Chico Hardhats wins=18 losses=6 Explanation The SELECT statement contains a WHERE clause with a placeholder ? for the value that will later be assigned to the name field. The statement is prepared and a statement handle returned. The user is asked for a team name, assigned to $team_name, later to be used as an argument to the execute method. The value of $team_name is plugged into the placeholder in the query. The fetchrow_array() method retrieves the values for the team that was specified when the query was executed in line 3.

Using Multiple Placeholders

Example 17.36.

use DBI; my $dbh=DBI->connect("DBI:mysql:host=localhost;user=root; password=quigley1;database=sample_db"); 1 my $sth=$dbh->prepare("INSERT INTO teams(name, wins, losses) VALUES(?,?,?)"); # Preset the values in variables 2 my $team_name="Denver Daredevils"; # set values here my $wins=18; my $losses=5; 3 $sth->execute($team_name, $wins, $losses); print "\nData for team table. \n\n"; 4 $sth=$dbh->prepare("SELECT * FROM teams"); $sth->execute(); 5 while(my @val = $sth->fetchrow_array()){ print "name=$val[0]\n"; print "wins=$val[1]\n"; print "losses=$val[2]\n\n"; } $sth->finish(); $dbh->disconnect(); Explanation This time, three placeholders act as a template for values that will be filled in at some later time with the SQL INSERT statement. Each ? represents a value for the name field, the wins field, and the losses field, respectively. The scalars are assigned the values that will be sent to the database when the SQL statement is executed. The execute() method executes the SQL statement by plugging in the values of these variables where the placeholders are found in the INSERT statement. Another SQL statement is prepared to select all the fields in the table so that we can see if the new data was actually inserted. The result set from the previous query is fetched a row at a time and displayed.

Using Placeholders to Insert Multiple Records

Example 17.37.

Code View: use DBI; my $dbh=DBI->connect("DBI:mysql:host=localhost;user=root; password=quigley1;database=sample_db"); # Using a placeholder. Values will be assigned later 1 my $sth=$dbh->prepare("INSERT INTO teams(name, wins, losses) VALUES(?,?,?)"); # Create a list of new entries 2 my @rows = (['Tampa Terrors', 4, 5], ['Arcata Angels', 3 , 4], ['Georgetown Giants', 1 ,6], ['Juno Juniors', 2, 7], ); 3 foreach my $row (@rows ){ $name = $row->[0]; $wins = $row->[1]; $losses=$row->[2]; 4 $sth->execute($name, $wins, $losses); } print "\nData for team table. \n\n"; 5 $sth=$dbh->prepare("SELECT * FROM teams"); $sth->execute(); while(my @row = $sth->fetchrow_array()){ print "name=$row[0]\n"; print "wins=$row[1]\n"; print "losses=$row[2]\n\n"; } $sth->finish(); $dbh->disconnect(); Explanation Again, three placeholders act as a template for values that will be filled in at some later time with the SQL INSERT statement. Each ? represents a value for the name field, the wins field, and the losses field, respectively. An array of rows is created to represent the new records that will later be inserted into the database. Each row from @row is broken down into its individual fields and the values assigned to scalars representing the value for each field. The execute() method executes the statement by plugging in the values of these variables where the placeholders are found in the INSERT statement. This is done for each row of new data until it is all entered. If any of the teams are duplicates, the execute() method will fail because the name field was earlier assigned to be the primary key.

Binding Parameters and the bind_param() Method

Another convenient and efficient way to use placeholders is with the bind_param() method. The placeholder tells the database that the value represented by the ? will be filled in later. The bound parameter is the value that will be filled in to replace the ? and eliminates sending arguments to the execute() method.

The bind_param() method takes up to three arguments. The first argument represents the position of the parameter in the placeholder; i.e., if the position is 1, then that would be represented by the first ? (placeholder) to be filled in with a value, and if the position is 2, that would be represented by the second ?, etc. The second argument to bind_param() is the actual value that will replace the ?, and last, an optional parameter that hints as to the data type of the replacement value, typically a number or string. The data type for a placeholder cannot be changed after the first bind_param() method call. However, it can be left unspecified, in which case it defaults to the previous value.

Two ways to handle the data type are either as an anonymous hash or as a DBI constant:

$sth->bind_param(1, $value, { TYPE => SQL_INTEGER }); # Hash
$sth->bind_param(1, $value, SQL_INTEGER);  # DBI Constant

See Example 17.44 for how to use this third optional argument.

Example 17.38.

use DBI; my $driver="DBI:mysql"; my $database="sample_db"; my $user="root"; my $password="quigley1"; my $host="localhost"; my $dbh = DBI->connect("$driver:$database:$host","$user", "$password") or die "Can't connect: " . DBI->errstr; 1 $sth=$dbh->prepare("SELECT name, wins,losses FROM teams where name LIKE ?") or die "Can't prepare sql statement" . DBI->errstr; 2 $sth->bind_param(1, "Ch%"); 3 $sth->execute(); 4 $sth->dump_results(); $sth->finish(); $dbh->disconnect(); (Output) 'Cheyenne Chargers', '6', undef 'Chico Hardhats', '21', '25' Explanation A SQL statement is prepared with a placeholder that will serve as a template for the query. The bind_param() method takes two arguments: the position of the placeholder; i.e., the ? represents the first parameter that will be filled and the value that will be assigned to that position, "Ch%". Since the parameters were bound to the statement with bind_param(), the execute() method does not require arguments. The DBI function dump_results() method is used to quickly output the results returned from the database after the query was executed.

Cached Queries

A cache is a temporary storage area where data that is frequently used can be copied and accessed more quickly. Most database servers utilize a cache to improve the performance of recently seen queries. A SQL statement can be cached rather than destroyed after it is executed. If another query identical to the cached statement is executed, the cached query can be reused. The DBI prepare_cached() method is used to cache a query. It is just like the prepare(), except that it looks to see if the same SQL statement has been previously executed, and if so, gives you the cached statement handle rather than a brand new one. (If you are managing multiple connections, see Apache::DBI::Cache.)

Example 17.39.

Code View: use DBI; my $driver="DBI:mysql"; my $database="sample_db"; my $host="localhost"; my $user="root"; my $password="quigley1"; my$dbh=DBI->connect("$driver:database=$database; host=$host;user=$user;password=$password")or die "Can't connect: " . DBI->errstr; 1 sub get_wins{ # Subroutine to handle database query 2 my($dbh, $team) = @_; 3 my $sth=$dbh->prepare_cached("SELECT wins FROM teams WHERE name = ?") or die "Can't prepare sql statement" . DBI->errstr; 4 $sth->execute($team); $wins=$sth->fetchrow_array(); 5 return $wins; } STARTOVER: { 6 print "To see how many wins, please enter the team's name. "; chomp($team_name=<STDIN>); # Call a function to process database query 7 print "$team_name has won ", get_wins($db, $team_name), " games.\n"; print "Do you want to check wins for another team? "; chomp($ans = <STDIN>); 8 redo STARTOVER if $ans =~ /y|yes/i; } $sth->finish(); $dbh->disconnect(); (Output) 5 To see how many wins, please enter the team's name. Tampa Terrors Tampa Terrors have won 3 games. 7 Do you want to check wins for another team? y 5 To see how many wins, please enter the team's name. San Francisco Fogheads San Francisco Fogheads have won 24 games. 7 Do you want to check wins for another team? y 5 To see how many wins, please enter the team's name. Chico Hardhats Chico Hardhats have won 21 games. 7 Do you want to check wins for another team? n Explanation A user-defined function called get_wins will be used to handle the database requests. The @_ contains two values, the database handle and the field name of a team in the database. A statement is prepared and for efficiency, it is cached, rather than being destroyed after it is executed. For repeating the same query many times, this is done to make the processing more efficient. Since this function may be called a number of times, the prepare_cache() method is used. Other than its name, this method is just like the prepare method. The query is executed and the name of the team filled in where the ? appears in the SQL statement. The number of wins for a specified team is retrieved and returned from this function. In this main part of the program, a labeled block is entered and the user is asked to select a team. Within the print statement, the user-defined function called get_wins is called. The database handle and the name of the team selected by the user are passed to the function. If the user wants to see the number of wins for another team, program flow will go back to the beginning of the labeled block and start again.