Error Handling

To this point I've only used a minimum of error handling. Specifically, I've turned on RaiseError when connecting to MySQL. By doing so, if a connection error occurs, the die() function will be invoked, printing an error message and stopping the script. You may have already witnessed this. An alternative is to use PrintError. It prints any error messages but does not stop the script. To use PrintError, code

[View full width]my $dbh = DBI->connect('DBI:mysql: accounting:localhost', 'username', 'password',
 {PrintError => 1});

To take error management even further, you can use the err() function. It returns a true/false value indicating if an error occurred. To reference the specific error, use the errstr() function.

my $dbh = DBI->connect('DBI:mysql: accounting:localhost','username', 'password');
if (DBI->err()) {
   print "Could not connect. MySQL said:" . DBI->errstr();
}

If you encounter an error after preparing, executing, or doing a query, you would then refer to the statement handler:

if
($sth->err()) {
  print "Error running the query. MySQL said:"
. $sth ->errstr();
}

Practice is better than theory, so I'll create a new show_tables.pl script that implements error management.

To handle errors:

Create a new Perl script (Script 8.6).

#!/usr/bin/perl -w
use strict;
use DBI;

Determine which database to use and check that one was entered.

    my $database = $ARGV[0];
    if (defined($database)) {

This code is exactly as it is in the original version of the script.

Connect to MySQL.

[View full width]my $dbh = DBI->connect("DBI:mysql: $database:localhost", 'username', 'password',
 {PrintError => 0,  RaiseError => 0});

Since I'll be handling errors more specifically within the script, I'm turning off both PrintError and RaiseError

Check for, and report on, any errors.

if (DBI->err()) {
    print "Could not connect to the database! MySQL reported: " . DBI->errstr() . "\n";
    exit(1);
}

The conditional first checks for the presence of an error. If one exists, then a message is printed, including the actual error (Figure 8.26). Finally the script is terminated using exit().

Figure 8.26. How connection errors are now handled by the script.

[View full size image]

Script 8.6. This modified version of `show_tables.pl` takes error handling to a detailed level.

1 #!/usr/bin/perl -w 2 3 # Script 8.6, 'show_tables2.pl' 4 # This script takes a database name as 5 # a command line argument and shows 6 # the tables in that database. 7 8 # Use what needs to be used. 9 use strict; 10 use DBI; 11 12 # This script takes one argument when invoked. 13 # The argument is a database name. 14 my $database = $ARGV[0]; 15 16 if (defined($database)) { 17 18 # Connect to the database. 19 my $dbh = DBI->connect("DBI:mysql: $database:localhost", 'username', 'password', {PrintError => 0, RaiseError => 0}); 20 21 # Check for errors. 22 if (DBI->err()) { 23 print "Could not connect to the database! MySQL reported: " . DBI->errstr() . "\n"; 24 exit(1); 25 } 26 27 # Query the database. 28 my $sth = $dbh->prepare('SHOW TABLES'); 29 30 if (defined($sth)) { 31 $sth->execute(); 32 33 # Check for errors. 34 if ($sth->err()) { 35 print "Could not execute the query! MySQL reported: " . $sth->errstr() . "\n"; 36 } else { 37 my @row; 38 while (@row = $sth-> fetchrow_array()) { 39 print "$row[0]\n"; 40 } 41 $sth->finish(); 42 } 43 } else { 44 print "Could not prepare the query! MySQL reported: " . $dbh->errstr() . "\n"; 45 } 46 47 # Disconnect. 48 $dbh->disconnect; 49 50 } else { 51 print "Please enter a database name when calling this script! \n"; 52 }

Prepare and execute the query.

my $sth = $dbh->prepare('SHOW TABLES');
if (defined($sth)) {
   $sth->execute();

This code is also unchanged from the other version of the script.

Check for errors, and then retrieve and print every record.

if ($sth->err()) {
    print "Could not execute the query! MySQL reported: " . $sth->errstr() . "\n";
} else {
   my @row;
   while (@row = $sth->fetchrow_array()) {
     print "$row[0]\n";
   }
   $sth->finish();
}

If there was an execution problem, $sth->err() will be true and that error needs to be reported (Figure 8.27). Otherwise, the data is fetched and printed like before.

Figure 8.27. Query errors are also handled specifically by the script.

[View full size image]

Complete the defined($sth) conditional.

} else {
   print "Could not prepare the 'query! MySQL reported: " . $dbh-'>errstr() . "\n";
}

If the conditional is false, an error likely occurred and that should also be printed.

Close the database connection and finish the main conditional.

$dbh->disconnect;
} else {
   print "Please enter a database name when calling this script! \n";
}

Save the script as show_tables2.pl, change the permissions (if necessary), and run the script using the syntax ./show_tables.pl databasename or perl show_tables.pl databasename (Figure 8.28 and 8.29).

Figure 8.28. If there aren't any problems, you'll see no errors.

Figure 8.29. Don't forget to reference a specific database when running this script!

Tip