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:

1.

Create a new Perl script (Script 8.6).

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


2.

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.

3.

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

4.

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.


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  }

5.

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.

6.

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.


7.

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.

8.

Close the database connection and finish the main conditional.

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


9.

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

  • Another very useful debugging technique, besides printing the MySQL error, is to print out the actual query that was run. This is even more true with dynamically generated queries. It's important that you confirm exactly what query is being run.





MySQL Visual QuickStart Guide Serie  .Covers My SQL 4 and 5
MySQL, Second Edition
ISBN: 0321375734
EAN: 2147483647
Year: 2006
Pages: 162
Authors: Larry Ullman

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net