DBI_error( ) Message Handler

From: Ron Wingfield (rtwingfield_at_archaxis.net)
Date: 12/29/04 

To: "Hardy Merrill" <HMerrill@dhcr.state.ny.us>, <dbi-users@perl.org>
Date: Tue, 28 Dec 2004 17:22:07 -0600

Hello Hardy, and everyone,

The content of my DBI_error.pm file contains the subroutine, DBI_error( ), and I've listed it below. As you can see, it's not that complicated, and does incorporate "stock" code for evaluating the DBI error number, $DBI::err, and the DBI error string, $DBI::errstr :

  # ------------------------------------------------------------------
  # DBI_error.pm -- Library module for standard DBI error handling.
  #
  # Truth Table:
  # Print Print Terminate
  # w/err DBI on
  # $_[1] Value only Error error
  # ========================================
  # 1 : NO NO NO
  # 2 : NO NO YES
  # 3 : NO YES NO
  # 4 : NO YES YES
  # 5 : YES NO NO
  # 6 : YES NO YES
  # 7 : YES YES NO
  # 8 : YES YES YES

  sub DBI_error {

          if ($DBI::err) { # only print when error is TRUE
              if ($_[1] eq 3 || $_[1] eq 4 || $_[1] eq 7 || $_[1] eq 8)
              {
                  print
                  qq(<b>DBI Error $DBI::err:</b>&nbsp&nbsp$DBI::errstr<br>);
              }
          }

          if ($DBI::err) { # only when error is TRUE
              if ($_[0] && $_[1] eq 5 || $_[1] eq 6 || $_[1] eq 7 || $_[1] eq 8)
              {
                  # then get and print optional custom
                  # message text on-error ONLY!
                  # (from the first element of the
                  # subroutine argument array, @_ ).
                  print qq($_[0]<br>);
              }
              else # then always print the custom message text.
              {
                  if ($_[0]) {print qq($_[0]<br>);}
              }

              if ($_[1] eq 2 || $_[1] eq 4 || $_[1] eq 6 || $_[1] eq 8)
              {
                  # then termination is requested.
                  print qq(Process terminated.<br>);
                  exit(0);
              }
          }
          elsif ($_[0] && $_[1] eq 1 || $_[1] eq 2 || $_[1] eq 3 || $_[1] eq 4)
          {
                  # then get and print any
                  # optional custom string text
                  # from the first element of the
                  # subroutine argument array, @_ ,
                  # and print it when requested.
                  print qq($_[0]<br>);
          }
  }
  return 1; # Always return TRUE (. . .probably moot.)
  # ---------------------------------------------------------------

Include and use the DBI_error.pm subroutine in your program as follows:
(The example illustrates an attempt to connect to a database.)

  use CGI;
  $cgi = new CGI;
  use DBI_error; # Custom DBI error handler.

If the application program is outputting html code, be sure to start the html output first.

  $cgi->header(),
  $cgi->start_html(-title=>$title, -bgcolor=>'cyan'),

  # Assemble the data-set name and connect to the database:
  $dsn="DBI:mysql:host=$host_name;database=$db_name";
  $dbh=DBI->connect($dsn, $userid, $passwd,{PrintError=>0, RaiseError=>0});
  DBI_error("Could not connect to DSN, \"$dsn\".","8"); # Terminate on error.

Call the subroutine immediately after any DBI operation that can be (. . .or is desirable to be) monitored. Note the assignments in the hash, {PrintError=>0, RaiseError=>0}. These FALSE values are necessary! This technique does not use the die function. My preference is to usually control error processing without die or warn.

The subroutine accepts an argument via the @_ array (. . .a Perl reserved name) and uses two elements. The first element can be any custom message to be printed or displayed in association with or without the actual DBI error information (in this example, issued by MySQL). The second element can be set to any of eight values (0 through 7) that are defined in the Truth Table.

The Truth Table is consists of a 3 x 8 matrix of elements that describe eight possible conbinations of parameters governing the action performed by the subroutine with regard to the printing (or not) of 1) a custom message, 2) printing (or not) of the DBI error number and message, and 3) termination-on-error (or not).

An exit-on-error request would be used for example, when the program could not connect to an essential database. On the other hand for example, if you wanted to edit a form, and not terminate on every DBI error (e.g., to allow the user to see several errors at once), then specify one of the values that correspond to no termination for the second element of the @_ array. For example, encountering a DBI eror with value 6 specified will print the custom message on-error only, the DBI error number and message will print, but the process will not terminate. The subroutine can be used to override the standard DBI message by specifying 4. In this case, the custom message will be displayed, the DBI error number and message will not print, and processing will continue.

Here's a typical message produced by specifying 8 in the Perl example above:

  DBI Error 1049: Unknown database 'foobar'
  Could not connect to DSN, "DBI:mysql:host=localhost;database=foobar".
  Process terminated.

For the latter scenario where exit is not preferred, the following message will be displayed within the appllication's html form:

  DBI Error 1054: Unknown column 'hoohaw' in 'order clause'
  Query execution to select from states failed.

There are certainly other techniques for monitoring messages. This is a similar concept to what I've used in IBM's OS/400 environment for years (by the way, OS/400 has a very robust interprocess messaging system.) Some may argue that subroutine calls are expensive, but I'm not concerned about a few milli-seconds. The advantage of this scenario is 1) standardization of the technique, and 2) externalization of the code (you can make changes to the subroutine's source file, and if your Makefile is up to muster, then your good to go.)

Often, programmers tend to procrastinate with regard to including error handling code. Using a standard module like DBI_error( ) should encourage more liberal use of meaningful messages.

OTTF and I hope this helps,
Ron Wingfield

FreeBSD 4.8 -- Apache http 2.0.28 -- MySQL client/server 4.1.7
Perl 5.8.5 -- p5-DBD-mysql-2.9004 driver -- p5-DBI-1.46

--------------------------------------------------------------------------------

  ----- Original Message -----
  From: Hardy Merrill
  To: rtwingfield@archaxis.net ; dbi-users@perl.org
  Sent: Tuesday, December 28, 2004 6:59 AM
  Subject: Re: Evaluated "require $database" not found.

  Ron, my comments below as HM>> Sorry for the ridiculous (lack of)
  quoting - old version of Groupwise at work :-(

  Hardy Merrill

>>> "Ron Wingfield" <rtwingfield@archaxis.net> 12/27/04 07:41PM >>>
  Hello everyone,

  I do appreciate all of your suggestions. Actually, now I do have a
  good handle on the problems. I was in a "couldn't see the forest for
  the trees" scenario. I've gone through my three proof-of-concept Perl
  programs and they are working very well. There is still a lot of
  functionality to add to the programs (such as the important function of
  returning the MySQL query results back to the "middle" program, i.e.,
  the appication user interface program) , but basically I have the
  following in place:

  DB_Select.pl calls Contacts_01.pl calls DBI_API_01.pl

  The DBI_API_01.pl program is totally generic and handles all database
  maintenance I/O (Inquire, Update, Insert, and Delete) for any
  database/table. As it turns out, the parameter passing problem was a
  non-issue once I realized that I was using a technique of Mr. Paul
  DuBois that incorporated a custom Perl library module to handle
  connections to (some of) his Cookbook Recipe examples.

  Also very frustrating is this little nuance:

    $dsn="DBI:mysql:host=$host_name;database=$db_name"; # CORRECT
  SYNTAX!
    $dbh=DBI->connect($dsn, $userid, $passwd,{PrintError=>0,
  RaiseError=>0});
    DBI_error("Could not connect to $dsn."); # Test/exit on error.

  HM>> I've never done error handling like this before. Looks like you
  are *always* calling the DBI_error subroutine - I'm curious to know what
  DBI_error contains for error handling code. As you may know, typical
  error handling involves an "or" (or "||"), something like this:

       $dbh=DBI->connect($dsn,
                                             $userid,
                                             $passwd,
                                            {PrintError=>0,
  RaiseError=>0}
        ) || die "Could not connect to $dsn: $DBI::errstr";

  ??? I'm familiar with error trapping as described in "perldoc DBI" -
  here's a snippet from the paragraph titled "Transactions":
  =========================================================
    Transactions
      Transactions are a fundamental part of any robust database system.
  They
      protect against errors and database corruption by ensuring that
  sets of
      related changes to the database take place in atomic (indivisible,
      all-or-nothing) units.

      This section applies to databases that support transactions and
  where
      "AutoCommit" is off. See "AutoCommit" for details of using
  "AutoCommit"
      with various types of databases.

      The recommended way to implement robust transactions in Perl
      applications is to use "RaiseError" and "eval { ... }" (which is
  very
      fast, unlike "eval "...""). For example:

        $dbh->{AutoCommit} = 0; # enable transactions, if possible
        $dbh->{RaiseError} = 1;
        eval {
            foo(...) # do lots of work here
            bar(...) # including inserts
            baz(...) # and updates
            $dbh->commit; # commit the changes if we get this far
        };
        if ($@) {
            warn "Transaction aborted because $@";
            # now rollback to undo the incomplete changes
            # but do it in an eval{} as it may also fail
            eval { $dbh->rollback };
            # add other application on-error-clean-up code here
        }
  =========================================================

  A lot of good info there, but the main point about error trapping is
  this:
        $dbh->{RaiseError} = 1;
        eval {
              ### dbi statements here ###
        };
        if ($@) {
              ### if any dbi statements in an eval fail with a "die"
  when
              ### RaiseError is on(1), then the die message gets put
              ### into $@, and control passes to the next statement
              ### after the eval.
        }

  Not that any of this will affect your errant connect statement, but I
  thought I would include it here just in case you weren't aware of it
  ;-)

  Hardy Merrill

  . . .if the construct of the $dsn variable includes a space as in
    $dsn="DBI:mysql:host=$host_name; database=$db_name"; # DO NOT USE
  SPACE!
  then the connect will fail, but the DBI_error( ) handler will not trap
  the error and report the failed attempt to connect. The program will
  continue processing and the DBI_error( ) handler will report errors if
  subsequent SELECTS, are attempted on a valid table in the database, for
  example:

    DBI Error 1046: No database selected
    Query execution to select from states failed.
    Process terminated.

  Notice that this was a failure of an operation to load a pop-up
  selection list of states from the "states" table.

  What I have found is that if the space(s) are removed as per the
  correct syntax, then the DBI_error( ) handler will trap and report a
  connection failure if a non-existent database is specified, as per this
  example:

    DBI Error 1044: Access denied for user 'cbuser'@'localhost' to
  database 'xookbook'
    Could not connect to DBI:mysql:host=localhost;database=xookbook.
    Process terminated.

  Notice that I intentionally mispelled the name of the "cookbook"
  database as "xookbook" to force the error. Finally, the DBI_error( )
  handler subroutine function accepts a custom string text for display in
  addition to the actual DBI error number and string issued by MySQL.

  Thanks again to everyone and OTTF :-)
  Ron Wingfield

  FreeBSD 4.8 -- Apache http 2.0.28 -- MySQL client/server 4.1.7
  Perl 5.8.5 -- p5-DBD-mysql-2.9004 driver -- p5-DBI-1.46

  --------------------------------------------------------------------------------