Recipe 2.2. Checking for Errors


Problem

Something went wrong with your program, and you don't know what.

Solution

Everyone has problems getting programs to work correctly. But if you don't anticipate difficulties by checking for errors, you make the job a lot harder. Add some error-checking code so that your programs can help you figure out what went wrong.

Discussion

After working through Section 2.1, you now know how to connect to the MySQL server. It's also a good idea to know how to check for errors and how to retrieve specific error information from the API, so that's what we'll cover next. You're probably anxious to see how to do more interesting things (such as issuing statements and getting back the results), but error checking is fundamentally important. Programs sometimes fail, especially during development, and if you don't know how to determine why failures occur, you'll be flying blind.

When errors occur, MySQL provides three values:

  • A MySQL-specific error number

  • A MySQL-specific descriptive text error message

  • An SQLSTATE error code that is a five-character value defined according to the ANSI and ODBC standards

Various sections in this recipe in this section show how to access this information. Most of the later recipes in this book that display error information print only the MySQL-specific values, but the recipes here show how to access the SQLSTATE value as well, for those APIs that expose it.

The example programs demonstrate how to check for errors but will in fact execute without any problems if your MySQL account is set up properly. Thus, you may have to modify the examples slightly to force errors to occur so that the error-handling statements are triggered. That is easy to do. For example, you can change a connection-establishment call to supply a bad password.

A general debugging aid that is not specific to any API is to check the MySQL server's query log to see what statements the server actually is receiving. (This requires that you have query logging enabled and that you have access to the log.) The query log often will show you that a statement is malformed in a particular way and give you a clue that your program is not constructing the proper statement string. If you're running a script under a web server and it fails, check the web server's error log.

Don't Shoot Yourself in the Foot: Check for Errors

The principle that you should check for errors is not so obvious or widely appreciated as one might hope. Many messages posted on MySQL-related mailing lists are requests for help with programs that fail for reasons unknown to the people that wrote them. In a surprising number of cases, the reason these people are mystified by their programs is that they put in no error checking, and thus gave themselves no way to know that there was a problem or to find out what it was! You cannot help yourself this way. Plan for failure by checking for errors so that you can take appropriate action when they occur.


Perl

The DBI module provides two attributes that control what happens when DBI method invocations fail:

  • PrintError, if enabled, causes DBI to print an error message using warn⁠(⁠ ⁠ ⁠).

  • RaiseError, if enabled, causes DBI to print an error message using die⁠(⁠ ⁠ ⁠). This terminates your script.

By default, PrintError is enabled, and RaiseError is disabled, so a script continues executing after printing a message if an error occurs. Either or both attributes can be specified in the connect⁠(⁠ ⁠ ⁠) call. Setting an attribute to 1 or 0 enables or disables it, respectively. To specify either or both attributes, pass them in a hash reference as the fourth argument to the connect⁠(⁠ ⁠ ⁠) call.

The following code sets only the AutoCommit attribute and uses the default settings for the error-handling attributes. If the connect⁠(⁠ ⁠ ⁠) call fails, this results in a warning message, but the script continues to execute:

my %conn_attrs = (AutoCommit => 1); my $dbh = DBI->connect ($dsn, "cbuser", "cbpass", \%conn_attrs); 

However, because you really can't do much if the connection attempt fails, it's often prudent to exit instead after DBI prints a message:

my %conn_attrs = (AutoCommit => 1); my $dbh = DBI->connect ($dsn, "cbuser", "cbpass", \%conn_attrs)             or exit; 

To print your own error messages, leave RaiseError disabled, and disable PrintError as well. Then test the results of DBI method calls yourself. When a method fails, the $DBI::err, $DBI::errstr, and $DBI::state variables contain the MySQL error number, a descriptive error string, and the SQLSTATE value, respectively:

my %conn_attrs = (PrintError => 0, AutoCommit => 1); my $dbh = DBI->connect ($dsn, "cbuser", "cbpass", \%conn_attrs)             or die "Connection error: "                    . "$DBI::errstr ($DBI::err/$DBI::state)\n"; 

If no error occurs, $DBI::err will be 0 or undef, $DBI::errstr will be the empty string or undef, and $DBI::state will be empty or 00000.

When you check for errors, access these variables immediately after invoking the DBI method that sets them. If you invoke another method before using them, their values will be reset.

The default settings (PrintError enabled, RaiseError disabled) are not so useful if you're printing your own messages. In this case, DBI prints a message automatically, and then your script prints its own message. This is at best redundant, and at worst confusing to the person using the script.

If you enable RaiseError, you can call DBI methods without checking for return values that indicate errors. If a method fails, DBI prints an error and terminates your script. If the method returns, you can assume it succeeded. This is the easiest approach for script writers: let DBI do all the error checking! However, if PrintError and RaiseError both are enabled, DBI may call warn⁠(⁠ ⁠ ⁠) and die⁠(⁠ ⁠ ⁠) in succession, resulting in error messages being printed twice. To avoid this problem, it's best to disable PrintError whenever you enable RaiseError. That's the approach generally used in this book, as illustrated here:

my %conn_attrs = (PrintError => 0, RaiseError => 1, AutoCommit => 1); my $dbh = DBI->connect ($dsn, "cbuser", "cbpass", \%conn_attrs); 

If you don't want the all-or-nothing behavior of enabling RaiseError for automatic error checking versus having to do all your own checking, you can adopt a mixed approach. Individual handles have PrintError and RaiseError attributes that can be enabled or disabled selectively. For example, you can enable RaiseError globally by turning it on when you call connect⁠(⁠ ⁠ ⁠), and then disable it selectively on a per-handle basis. Suppose that you have a script that reads the username and password from the command-line arguments, and then loops while the user enters statements to be executed. In this case, you'd probably want DBI to die and print the error message automatically if the connection fails (there's not much you can do if the user doesn't provide a valid name and password). After connecting, on the other hand, you wouldn't want the script to exit just because the user enters a syntactically invalid statement. It would be better for the script to trap the error, print a message, and then loop to get the next statement. The following code shows how this can be done. The do⁠(⁠ ⁠ ⁠) method used in the example executes a statement and returns undef to indicate an error:

my $user_name = shift (@ARGV); my $password = shift (@ARGV); my %conn_attrs = (PrintError => 0, RaiseError => 1, AutoCommit => 1); my $dbh = DBI->connect ($dsn, $user_name, $password, \%conn_attrs); $dbh->{RaiseError} = 0; # disable automatic termination on error print "Enter queries to be executed, one per line; terminate with Control-D\n"; while (<>)              # read and execute queries {     $dbh->do ($_) or warn "Query failed: $DBI::errstr ($DBI::err)\en"; } $dbh->{RaiseError} = 1; # re-enable automatic termination on error 

If RaiseError is enabled, you can trap errors without terminating your program by executing code within an eval block. If an error occurs within the block, eval fails and returns a message in the $@ variable. Typically, you use eval something like this:

eval {   # statements that might fail go here... }; if ($@) {   print "An error occurred: $@\n"; } 

This technique is commonly used to implement transactions. For an example, see Section 15.4.

Using RaiseError in combination with eval differs from using RaiseError alone in the following ways:

  • Errors terminate only the eval block, not the entire script.

  • Any error terminates the eval block, whereas RaiseError applies only to DBI-related errors.

When you use eval with RaiseError enabled, be sure to disable PrintError. Otherwise, in some versions of DBI, an error may simply cause warn⁠(⁠ ⁠ ⁠) to be called without terminating the eval block as you expect.

In addition to using the error-handling attributes PrintError and RaiseError, you can get lots of useful information about your script's execution by turning on DBI's tracing mechanism. Invoke the trace⁠(⁠ ⁠ ⁠) method with an argument indicating the trace level. Levels 1 to 9 enable tracing with increasingly more verbose output, and level 0 disables tracing:

DBI->trace (1);      # enable tracing, minimal output DBI->trace (3);      # elevate trace level DBI->trace (0);      # disable tracing 

Individual database and statement handles have trace⁠(⁠ ⁠ ⁠) methods, too. That means you can localize tracing to a single handle if you want.

Trace output normally goes to your terminal (or, in the case of a web script, to the web server's error log). You can write trace output to a specific file by providing a second argument indicating a filename:

DBI->trace (1, "/tmp/trace.out"); 

If the trace file already exists, trace output is appended to the end; the file's contents are not cleared first. Beware of turning on a file trace while developing a script, and then forgetting to disable the trace when you put the script into production. You'll eventually find to your chagrin that the trace file has become quite large. Or worse, a filesystem will fill up, and you'll have no idea why!

Ruby

Ruby signals errors by raising exceptions and Ruby programs handle errors by catching exceptions in a rescue clause of a begin block. Ruby DBI methods raise exceptions when they fail and provide error information by means of a DBI::DatabaseError object. To get the MySQL error number, error message, and SQLSTATE value, access the err, errstr, and state methods of this object. The following example shows how to trap exceptions and access error information in a DBI script:

begin   dsn = "DBI:Mysql:host=localhost;database=cookbook"   dbh = DBI.connect(dsn, "cbuser", "cbpass")   puts "Connected" rescue DBI::DatabaseError => e   puts "Cannot connect to server"   puts "Error code: #{e.err}"   puts "Error message: #{e.errstr}"   puts "Error SQLSTATE: #{e.state}"   exit(1) end 

PHP

A PEAR DB method indicates success or failure by means of its return value. If the method fails, the return value is an error object. If the method succeeds, the return value is something else:

  • The connect⁠(⁠ ⁠ ⁠) method returns a connection object for interacting with the database server.

  • The query⁠(⁠ ⁠ ⁠) method for executing SQL statements returns a result set object for statements such as SELECT that return rows, or the DB_OK value for statements such as INSERT, UPDATE, or DELETE that modify rows.

To determine whether a method return value is an error object, pass it to the PEAR::isError⁠(⁠ ⁠ ⁠) method, check the PEAR::isError⁠(⁠ ⁠ ⁠) result, and take action accordingly. For example, the following code prints "Connected" if connect⁠(⁠ ⁠ ⁠) succeeds and exits with a generic error message if not:

$dsn = "mysqli://cbuser:cbpass@localhost/cookbook"; $conn =& DB::connect ($dsn); if (PEAR::isError ($conn))   die ("Cannot connect to server\n"); print ("Connected\n"); 

To obtain more specific information when a PEAR DB method fails, use the methods provided by the error object:

  • getCode⁠(⁠ ⁠ ⁠) and getMessage⁠(⁠ ⁠ ⁠) return an error number and message, respectively. These are standard values provided by PEAR that are not MySQL specific.

  • getUserInfo⁠(⁠ ⁠ ⁠) and geTDebugInfo⁠(⁠ ⁠ ⁠) return MySQL-specific information.

The following listing shows how each method displays the error information returned by PEAR DB when a connect error occurs:

$dsn = "mysqli://cbuser:cbpass@localhost/cookbook"; $conn =& DB::connect ($dsn); if (PEAR::isError ($conn)) {   print ("Cannot connect to server.\n");   printf ("Error code: %d\n", $conn->getCode ());   printf ("Error message: %s\n", $conn->getMessage ());   printf ("Error debug info: %s\n", $conn->getDebugInfo ());   printf ("Error user info: %s\n", $conn->getUserInfo ());   exit (1); } 

Python

Python signals errors by raising exceptions, and Python programs handle errors by catching exceptions in the except clause of a try statement. To obtain MySQL-specific error information, name an exception class, and provide a variable to receive the information. Here's an example:

try:   conn = MySQLdb.connect (db = "cookbook",                           host = "localhost",                           user = "cbuser",                           passwd = "cbpass")   print "Connected" except MySQLdb.Error, e:   print "Cannot connect to server"   print "Error code:", e.args[0]   print "Error message:", e.args[1]   sys.exit (1) 

If an exception occurs, the first and second elements of e.args are set to the error number and error message, respectively. (Note that the Error class is accessed through the MySQLdb driver module name.)

Java

Java programs handle errors by catching exceptions. If you simply want to do the minimum amount of work, print a stack trace to inform the user where the problem lies:

try {   /* ... some database operation ... */ } catch (Exception e) {   e.printStackTrace (); } 

The stack trace shows the location of the problem but not necessarily what the problem is. It may not be all that meaningful except to you, the program's developer. To be more specific, you can print the error message and code associated with an exception:

  • All Exception objects support the getMessage⁠(⁠ ⁠ ⁠) method. JDBC methods may throw exceptions using SQLException objects; these are like Exception objects but also support getErrorCode⁠(⁠ ⁠ ⁠) and getSQLState⁠(⁠ ⁠ ⁠) methods. getErrorCode⁠(⁠ ⁠ ⁠) and getMessage⁠(⁠ ⁠ ⁠) return the MySQL-specific error number and message string. getSQLState⁠(⁠ ⁠ ⁠) returns a string containing the SQLSTATE value.

  • You can also get information about nonfatal warnings, which some methods generate using SQLWarning objects. SQLWarning is a subclass of SQLException, but warnings are accumulated in a list rather than thrown immediately, so they don't interrupt your program, and you can print them at your leisure.

The following example program, Error.java, demonstrates how to access error messages by printing all the error information it can get its hands on. It attempts to connect to the MySQL server and prints exception information if the attempt fails. Then it issues a statement and prints exception and warning information if the statement fails:

// Error.java - demonstrate MySQL error-handling import java.sql.*; public class Error {   public static void main (String[] args)   {     Connection conn = null;     String url = "jdbc:mysql://localhost/cookbook";     String userName = "cbuser";     String password = "cbpass";     try     {       Class.forName ("com.mysql.jdbc.Driver").newInstance ();       conn = DriverManager.getConnection (url, userName, password);       System.out.println ("Connected");       tryQuery (conn);    // issue a query     }     catch (Exception e)     {       System.err.println ("Cannot connect to server");       System.err.println (e);       if (e instanceof SQLException)  // JDBC-specific exception?       {         // print general message, plus any database-specific message         // (e must be cast from Exception to SQLException to         // access the SQLException-specific methods)         System.err.println ("SQLException: " + e.getMessage ());         System.err.println ("SQLState: "                             + ((SQLException) e).getSQLState ());         System.err.println ("VendorCode: "                             + ((SQLException) e).getErrorCode ());       }     }     finally     {       if (conn != null)       {         try         {           conn.close ();           System.out.println ("Disconnected");         }         catch (SQLException e)         {           // print general message, plus any database-specific message           System.err.println ("SQLException: " + e.getMessage ());           System.err.println ("SQLState: " + e.getSQLState ());           System.err.println ("VendorCode: " + e.getErrorCode ());         }       }     }   }   public static void tryQuery (Connection conn)   {     try     {       // issue a simple query       Statement s = conn.createStatement ();       s.execute ("USE cookbook");       s.close ();       // print any accumulated warnings       SQLWarning w = conn.getWarnings ();       while (w != null)       {         System.err.println ("SQLWarning: " + w.getMessage ());         System.err.println ("SQLState: " + w.getSQLState ());         System.err.println ("VendorCode: " + w.getErrorCode ());         w = w.getNextWarning ();       }     }     catch (SQLException e)     {       // print general message, plus any database-specific message       System.err.println ("SQLException: " + e.getMessage ());       System.err.println ("SQLState: " + e.getSQLState ());       System.err.println ("VendorCode: " + e.getErrorCode ());     }   } } 




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

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