Handling Query Errors

It can be useful to control DBI's error-handling behavior for a particular database connection. The connection method looks like this:

$dbh = DBI->connect ($data_source, $username, $auth, %attr);

The last attribute, a hash, can be left unspecified (as we've done until now), or can be specified, like this, for example:

$dbh = DBI->connect ($data_source, $username, $auth,
  { RaiseError => 0, PrintError => 1} );

RaiseError and PrintError are error-handling attributes that determine how DBI should handle errors in queries within that connection:

• RaiseError being set to 1 causes the Perl script to exit with an error message if a DBI error occurs. Default = 0 (off).

• PrintError being set to 1 generates an error message if a DBI error occurs, but the Perl script continues executing. Default = 1 (on).

Thus in the default states (as shown in the connect() line in the preceding example), a Perl script will log an error but continue executing if a DBI error occurs. In our scripts earlier today, you may recall that we added the die statement after every DBI method, something like this:

$var = $sth->some_method ()
  or die "Error message";

In general it's wise to stop further database processing after an error because if it keeps going, a script could have unpredictable effects on your database or produce spurious output. By using RaiseError and PrintError, you could have connected to the database a little differently, standardized error handling, and saved program lines. Consider Listing 12.5.

Listing 12.5. testerror1.pl, Program to Demonstrate the Effect of Inserting a Duplicate Primary Key

 1: #!/usr/bin/perl -w
 2:
 3: # Simple program to try to insert a duplicate primary key
 4:
 5: use DBI;
 6:
 7: my $dsn = "DBI:mysql:cms";
 8: my $db_user = "cms";
 9: my $db_pass = "mypass";
10:
11: print "Content/type: text/plain

";
12:
13: # Connect to the database
14: my $dbh = DBI->connect ($dsn, $db_user, $db_pass,
15:   { RaiseError => 1, PrintError => 1 });
16:
17: my $sql = "INSERT INTO subscribers
18:            SET name='New name',
19:                email='New@email',
20:                subscriber_id=3";   ## duplicate primary key
21: $aff = $dbh->do ($sql);
22: print "$aff rows were affected.
";
23:
24: exit;
					

In Listing 12.5, the connection attributes in lines 14–15 mean that error conditions are no longer handled longhand, as we did in previous programs with some lines of Perl after, for example, a do(). In Listing 12.5, if the do() in line 21 fails, we would instead get rather unfriendly messages like this:

DBD::mysql::db do failed: Duplicate entry '3' for key 1 at
/home/sites/cms/web/cgi-bin/testerror1.pl line 29.
DBD::mysql::db do failed: Duplicate entry '3' for key 1 at
/home/sites/cms/web/cgi-bin/testerror1.pl line 29.

If the script is run standalone, both messages would be printed to the console; but if called as a CGI script through the Web server, the messages would be printed to the Web server's error log. The script would terminate with no output to the user's browser telling him of the error.

Although the error occurs only once because both RaiseError and PrintError are set, an error is logged once, and the script exits with that error logged again. We could turn PrintError off, in which case, only a single message from RaiseError would be logged when the script dies.

To handle the error in a more user-friendly way, we can insert the following lines before any DBI calls. They define a subroutine that handles the error in a more elegant way by telling Perl to process any DIE signals through the handle_error subroutine. First, we'll define the subroutine; then we'll tell Perl to invoke it for all DIE signals:

sub handle_error {
  print "Database error... we'll tell sysadmin...
";
  ## and mail the system administrator for example
  die "Database error... $DBI::err: $DBI::errstr.
";
}
$SIG{__DIE__} = &handle_error;

With RaiseError and PrintError both on, if the script is called through a Web browser and if DBI causes Perl to terminate, a friendly message will be printed to the screen (you may also want to email the system administrator or do other things), and some useful information will be put in the error log, such as this:

DBD::mysql::db do failed: Duplicate entry '3' for key 1 at /home/sites/cms/
web/cgi-bin/testerror1.pl line 29.
Database error... 1062: Duplicate entry '3' for key 1.

Deciding precisely how to handle errors is in the end a matter of what the application requires and even down to the programmer's own taste. However, by learning the various options offered by Perl and DBI, you should be able to find the most elegant solution for your application.