Client 2—Adding Error Checking

client1.pl didn't do any error checking at all. The error messages that you saw were produced by DBI or DBD::Pg, not by the script. For simple applications, it might be sufficient to let DBI handle errors, but in more complex cases, you probably want some other options.

Let's start by modifying the previous client so that it prints its own error message if something goes wrong. Listing 14.4 shows the resulting code.

Listing 14.4. client2a.pl

 1 #!/usr/bin/perl -W
 2 #
 3 #  Filename: client2a.pl
 4 #
 5
 6 use strict;
 7 use DBI;
 8
 9 my $dbh = DBI->connect( "dbi:Pg:" )
10   or die "Can't connect to PostgreSQL: $DBI::errstr ($DBI::err)
";
11
12 $dbh->disconnect();

This script detects connect() failures by examining the return value. DBI::connect() returns undef (instead of a database handle) when it fails. The error message that prints (see line 10) includes an error message ($DBI::errstr) and an error number ($DBI::err).

At line 12, client2a disconnects the database handle if the connection attempt was successful. This should prevent the error message that you saw with client1 (Database handle destroyed without explicit disconnect). Notice that you will never reach line 12 if the connection attempt fails because the script dies (at line 10) if connect() encounters an error.

Now, let's run this client:

$ chmod a+x client2a.pl
$ ./client2a.pl
DBI->connect() failed: FATAL 1:  Database "korry" does not exist 
    in the system catalog. at ./client2a.pl line 9
Can't connect to PostgreSQL: FATAL 1:  Database "korry" does not 
    exist in the system catalog. (1)

There's the error message, but you are still getting the automatic error message delivered by DBI and/or DBD::Pg, too. Listing 14.5 shows how to turn off DBI's automatic error messages.

Listing 14.5. client2b.pl

 1 #!/usr/bin/perl -W
 2 #
 3 #  Filename: client2b.pl
 4 #
 5
 6 use strict;
 7 use DBI;
 8
 9 my $dbh = DBI->connect( "dbi:Pg:", undef, undef, {PrintError => 0} )
10   or die "Can't connect to PostgreSQL: $DBI::errstr ($DBI::err)
";
11
12 $dbh->disconnect();

client2b uses another form of the DBI->connect() method (actually, it's the same method, just a different number of arguments). The full prototype for the DBI->connect() method is

DBI->connect( $url, $username, $password, \%attributes )

The $url parameter specifies to which data source you want to connect. The $username and $password parameters specify the username and password, respectively (I'll come back to those in a moment). The final parameter is a list of attributes. Every DBI-related handle has a set of attributes that control how the handle behaves.

There are two attributes that control the way a handle responds when an error occurs. client2b.pl sets the PrintError attribute to 0. PrintError controls whether error messages should be printed by the driver (or the DBI class). When PrintError is enabled (which is the default), the driver (or DBI) prints an error message any time an error is encountered—that's where the extra message came from when you ran client2a.pl. If PrintError is disabled (by setting it to 0), the driver will not print any error messages. In either case, the DBI will set $DBI::err and $DBI::errstr. The next error-control attribute is RaiseError. When RaiseError is enabled, the DBI or driver throws an exception (by calling the die() method) whenever an error is encountered. Unless you catch the exception (using eval{}), your application will terminate when an error is raised. RaiseError is disabled by default. If you want a really quick way to handle DBI-related errors, enable RaiseError (that is, set it to 1 using {RaiseError => 1}), and your application will die if any errors occur. We'll leave RaiseError disabled in the examples shown in this chapter.

When you run this client, you'll see that you have disabled the automatic error messages and intercepted any error conditions with your own code:

$ chmod a+x client2b.pl
$ ./client2b.pl
Can't connect to PostgreSQL: FATAL 1:  Database "korry" does not 
    exist in the system catalog. (1)

This time, you only see the error message that you explicitly printed.

If you're using a recent version of DBD::Pg and you're connected to a PostgreSQL server newer than version 7.4, you can examine $DBI::state to find the SQLSTATE value returned by the most recent command. You can find a list of SQLSTATE values in Appendix A, “PostgreSQL Error Codes,” of the PostgreSQL reference manual. SQLSTATE values are short strings (such as 40P01, the uncooperative 42846, or the dreaded 42601) that are a little easier to handle if you need to interpret an error result within a program. If you're just displaying an error message to the user, use $DBI::errstr; if you're making some sort of programmatic decision based on an error condition, use $DBI::state.