Programming the Perl DBI: Database Programming with Perl by Alligator Descartes, Tim Bunce, Tim Bunce

From the Publisher

One of the greatest strengths of the Perl programming language is its ability to manipulate large amounts of data. Database programming is therefore a natural fit for Perl, not only for business applications but also for CGI-based Web and Intranet applications.

The primary interface for database programming in Perl is DBI. DBI is a database-independent package that provides a consistent set of routines regardless of what database product is actually in use -- Oracle, Sybase, Ingres, Informix, you name it. The design of DBI is to separate the actual database drivers (DBDs) from the programmer's API, so any DBI program can work with any database, or even with multiple databases by different vendors simultaneously.

Programming the Perl DBI is coauthored by Alligator Descartes, one of the most active members of the DBI community, and by Tim Bunce, the inventor of DBI. For the uninitiated, the book explains the architecture of DBI and shows you how to write DBI-based programs. For the experienced DBI dabbler, this book explains DBI's nuances and the peculiarities of each individual DBD.

The book includes:

• An introduction to DBI and its design
• How to construct queries and bind parameters
• Working with database, driver, and statement handles
• Debugging techniques
• Coverage of each existing DBD
• A complete reference to DBI

This is the definitive book for database programming in Perl.

Booknews

"The DBI is a database interface module for Perl. It defines a set of methods, variables and conventions that provide a consistent database interface independent of the actual database being used", explains Bunce, the architect and inventor of DBI. He and Descartes, one of the most active members of the DBI community, explain the architecture of DBI and show how to write DBI-based programs. For the DBI expert, they cover the nuances and peculiarities of each individual database driver. The basics of databases are well-covered, but some experience programming with Perl is assumed. Annotation c. Book News, Inc., Portland, OR (booknews.com)

Product Details

• Table of Contents
• Read an Excerpt

Table of Contents

Read an Excerpt

Chapter 4: Programming with DBI

A more common way in which these attributes are used is to specify them in the optional attribute hash supplied to DBI->connect( ) when connecting to a database. Automatic error-checking is the recommended style in which to write DBI code, so PrintError is enabled by default in DBI->connect( ). You can think of this as training wheels for novices and grease for quick-and-dirty script writers. Authors of more significant works usually either enable RaiseError or disable PrintError and do their own error checking.

A short example illustrating the use of RaiseError instead of manual error-checking is:

  #!/usr/bin/perl -w   #   # ch04/error/ex2: Small example using automatic error-handling with   #                 RaiseError, ie, the program will abort upon detection   #                 of any errors.   use DBI;            # Load the DBI module   my ($dbh, $sth, @row);   ### Perform the connection using the Oracle driver   $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" , {   PrintError => 0,   ### Don't report errors via warn(  )   RaiseError => 1    ### Do report errors via die(  )   } );   ### Prepare a SQL statement for execution   $sth = $dbh->prepare( "SELECT * FROM megaliths" );   ### Execute the statement in the database   $sth->execute(  );   ### Retrieve the returned rows of data   while ( @row = $sth->fetchrow_array(  ) ) {   print "Row: @row\n";   }   ### Disconnect from the database   $dbh->disconnect(  );   exit;

This example is both shorter and more readable than the manual error-checking shown in a following example. The actual program logic is clearer. The most obvious additional benefit is that we can forget to handle error-checking manually after a DBI operation, since the DBI will check for errors for us.

Mixed error checking

You can mix error-checking styles within a single program, since automatic error-checking can be easily enabled and disabled on a per-handle basis. There are plenty of occasions where mixed error-checking is useful. For example, you might have a program that runs continously, such as one that polls a database for recently added stock market quotes every couple of minutes.

Disaster occurs! The database crashes! The ideal situation here is that the next time the program tries connecting to the database and fails, it'll wait a few minutes before re-trying rather than aborting the program altogether. Once we've connected to the database, the error-checking should now simply warn when a statement fails and not die.

This mixed style of error-checking can be broken down into two areas: manual error-checking for the DBI->connect( ) call, and automatic error-checking via PrintError for all other statements. This is illustrated in the following example program:

  #!/usr/bin/perl -w   #   # ch04/error/mixed1: Example showing mixed error-checking modes.   use DBI;            # Load the DBI module   ### Attributes to pass to DBI->connect(  ) to disable automatic   ### error-checking   my %attr = (   PrintError => 0,   RaiseError => 0,   );   ### The program runs forever and ever and ever and ever....   while ( 1 ) {   my $dbh;   ### Attempt to connect to the database. If the connection   ### fails, sleep and retry until it succeeds...   until (   ) {   }   eval {      ### Catch _any_ kind of failures from the code within   };   warn "Monitoring aborted by error: $@\n" if $@;   ### short sleep here to avoid thrashing the database   sleep 5;   }   exit;

This program demonstrates that with DBI, you can easily write explicit error-checking and recovery code alongside automatic error-checking.

Error Diagnostics

The ability to trap errors within the DBI is very useful, with either manual or automatic error-checking, but this information is only marginally useful on its own. To be truly useful, it is necessary to discern exactly what the error was in order to track it down and debug it.

To this end, DBI defines several error diagnostic methods that can be invoked against any valid handle, driver, database, or statement. These methods will inform the programmer of the error code and report the verbose information from the last DBI method called. These are:

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

These various methods return the following items of information that can be used for more accurate debugging of errors:

• $h->err() returns the error number that is associated with the current error flagged against the handle $h. The values returned will be completely dependent on the values produced by the underlying database system. Some systems may not support particularly meaningful information; for example, mSQL errors always have the error number of -1. Oracle is slightly more helpful: a connection failure may flag an ORA-12154 error message upon connection failure, which would return the value of 12154 by invoking $h->err(). Although this value is usually a number, you should not rely on it.
• $h->errstr() is a slightly more useful method, in that it returns a string containing a description of the error, as provided by the underlying database. This string should correspond to the error number returned in $h->err().

  For example, mSQL returns -1 as the error number for all errors, which is not particularly useful. However, invoking $h->errstr() provides far more useful information. In the case of connection failure, the error:

  ERROR : Can't connect to local MSQL server

  might be generated and returned by $h->errstr(). Under Oracle, a connection failure returning the error number of 12154 will return the following string as its descriptive error message:

  ORA-12154: TNS:could not resolve service name (DBD ERROR: OCIServerAttach)
• $h->state() returns a string in the format of the standard SQLSTATE five-character error string. Many drivers do not fully support this method, and upon invoking it to discern the SQLSTATE code, the value: S1000

  will be returned. The specific general success code 00000 is translated to 0, so that if no error has been flagged, this method will return a false value.

The error information for a handle is reset by the DBI before most DBI method calls. Therefore, it's important to check for errors from one method call before calling the next on the same handle. If you need to refer to error information later you'll need to save it somewhere else yourself.

A rewriting of the previous example to illustrate using the specific handle methods to report on errors can be seen in the following code:

#!/usr/bin/perl -w # ch04/error/ex3: Small example using manual error-checking which also uses #                 handle-specific methods for reporting on the errors.   use DBI;            # Load the DBI module   ### Attributes to pass to DBI->connect() to disable automatic ### error-checking my %attr = ( RaiseError => 0, );   ### Perform the connection using the Oracle driver my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" , \%attr ) or die "Can't connect to database: ", $DBI::errstr, "\n";   ### Prepare a SQL statement for execution my $sth = $dbh->prepare( "SELECT * FROM megaliths" ) or die "Can't prepare SQL statement: ", $dbh->errstr(), "\n";   ### Execute the statement in the database $sth->execute or die "Can't execute SQL statement: ", $sth->errstr(), "\n";   ### Retrieve the returned rows of data while ( my @row = $sth->fetchrow_array() ) { print "Row: @row\n"; warn "Problem in fetchrow_array(): ", $sth->errstr(), "\n" if $sth->err();   ### Disconnect from the database $dbh->disconnect or warn "Failed to disconnect: ", $dbh->errstr(), "\n";   exit;

As you can see, it's even more long-winded than using the $DBI::errstr variable, which can at least be interpolated directly into the error messages.

In addition to these three methods, which allow finely grained error-checking at a handle level, there are three corresponding variables that will contain the same information, but at a DBI class level:

$DBI::err $DBI::errstr $DBI::state

Use of these variables is essentially the same as that of $h->err() and friends, but the values referred to are for the last handle used within DBI. They are particularly handy for interpolating into strings for error messages.

Since these variables are associated with the last handle used within the DBI, they have an even shorter lifespan than the handle error methods, and should be used only immediately after the method call that failed. Otherwise, it is highly likely they will contain misleading error information.

The one case where the variables are very useful is for connection errors. When these errors occur, there's no new handle returned in which to hold error information. Since scripts don't use the internal driver handles, the $DBI::errstr variable provides a very simple and effective way to get the error message from a connect() failure.

In summary, for most applications, automatic error-checking using RaiseError and/or PrintError is recommended. Otherwise, manual checking can be used and $DBI::errstr can easily be interpolated into messages. The handle methods are available for more complex applications.

Utility Methods and Functions

To round off our basic introduction to DBI, we'll tell you about some useful utility methods and functions that will make your life that little bit easier. These include the very useful quote escaping method, DBI execution tracing, and various functions to tidy up your data.

Database-Specific Quote Handling

By far the most important utility method is quote(), which correctly quotes and escapes SQL statements in a way that is suitable for a given database engine.

This feature is important if you have a Perl string that you wish to insert into a database, as the data will be required, in most cases, to have quotation marks around it.

To confuse matters, each database engine tends to have a different format for specifying these surrounding quotation marks. DBI circumvents this problem by declaring the quote() method to be executed against a database handle, which ensures that the correct quotation rules are applied.

This method, when executed against a database handle, converts the string given as an argument according to defined rules, and returns the correctly escaped string for use against the database. In the following example, quote() is used to properly convert the string 'fuzzy':

#!/usr/bin/perl -w # ch04/util/quote1: Demonstrates the use of the $dbh->quote() method   use DBI;   ### The string to quote my $string = "Don't view in monochrome (it looks 'fuzzy')!";   ### Connect to the database my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" , { RaiseError => 1 } );   ### Escape the string quotes... my $quotedString = $dbh->quote( $string );   ### Use quoted string as a string literal in a SQL statement my $sth = $dbh->prepare( "   " ); $sth->execute();   exit;

For example, if you quoted the Perl string of Do it! via an Oracle database handle, you would be returned the value of 'Do it!'. However, the quote() method also takes care of cases such as Don't do it! which needs to be translated to 'Don''t do it!' for most databases. The simplistic addition of surrounding quotes would have produced 'Don't do it!' which is not a valid SQL string literal.

Some databases require a more complex quote() method, and some drivers (though not all) have a quote() method that can cope with multiline strings and even binary data.

As a special case, if the argument is undef, the quote() method returns the string NULL, without quotes. This corresponds to the DBI's use of undef to represent NULL values, and how NULL values are used in SQL.

Tracing DBI Execution

DBI sports an extremely useful ability to generate runtime tracing information of what it's doing, which can be a huge time-saver when trying to track down strange problems in your DBI programs.

At the highest level, you can call the DBI->trace() method, which enables tracing on all DBI operations from that point onwards. There are several valid tracing levels:

0

Disables tracing

1

Traces DBI method execution showing returned values and errors

2

As for 1 but also includes method entry with parameters

3

As for 2 but also includes more internal driver trace information

4

Levels 4 and above can include more detail than is helpful

The trace() method can be used with two argument forms, either by specifying only the trace level or by specifying both the trace level and a file to which the trace information is appended. The following example shows the use of DBI->trace():

#!/usr/bin/perl -w # ch04/util/trace1: Demonstrates the use of DBI tracing.   use DBI;   ### Remove any old trace files unlink 'dbitrace.log' if -e 'dbitrace.log';   ### Connect to a database my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" );   ### Set the tracing level to 1 and prepare() DBI->trace( 1 ); doPrepare();   ### Set trace output to a file at level 2 and prepare() DBI->trace( 2, 'dbitrace.log' ); doPrepare();   ### Set the trace output back to STDERR at level 2 and prepare() DBI->trace( 2, undef ); doPrepare();   exit;   ### prepare a statement (invalid to demonstrate tracing) sub doPrepare { print "Preparing and executing statement\n"; my $sth = $dbh->prepare( " " ); $sth->execute(); return;   exit;

This program generates quite a bit of trace information, of which we'll show just a small fragment:

  -> prepare for DBD::Oracle::db (DBI::db=HASH(0xcd45c)~0xcd4a4 '   ') thr0   <- prepare= DBI::st=HASH(0xcd648) at trace1 line 30.   -> execute for DBD::Oracle::st (DBI::st=HASH(0xcd648)~0x16afec) thr0   dbd_st_execute SELECT (out0, lob0)...   !! ERROR: 942 'ORA-00942: table or view does not exist (DBD ERROR: OCIStmtExecute)'   <- execute= undef at trace1 line 33.   DBD::Oracle::st execute failed: ORA-00942: table or view does not exist (DBD ERROR: OCIStmtExecute) at trace1 line 33.

This trace information was generated with a setting of level 2, and shows the operations that DBI undertook when trying to prepare and execute a statement. Lines prepended with -> are written when the method is being entered, and lines prepended with <- are written when the method is returning. These lines also show the information being returned from the method call. The DBI trace output is indented by four spaces to make it easier to distinguish the trace output from any other program output.

You can see the prepare() method being called along with its parameters: a database handle and the SQL statement to prepare.[8] The next line shows the prepare returning a statement handle. It also shows the file and line number that prepare() was called from. Following that, we see execute() being called, a trace line from the driver itself, and the method returning after logging an error. Finally we see the warning generated by the DBI due to the PrintError attribute, which is on by default.

The trace information generated at level 1 is similar. The main difference is that the method entry lines (->) are not shown.

The one drawback to this form of tracing is that if your program uses a lot of handles, then the volume of tracing information could be quite vast. Similarly, you might have tracked your problem down to a specific database operation that you'd like to trace individually...