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.
After working through Connecting, Selecting a Database, and Disconnecting, 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:
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.
The DBI module provides two attributes that control what happens when DBI method invocations fail:
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) ";
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 "; 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: $@ "; }
This technique is commonly used to implement transactions. For an example, see Using Transactions in Perl Programs.
Using RaiseError
in
combination with eval
differs
from using RaiseError
alone in
the following ways:
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
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
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:
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 "); print ("Connected ");
To obtain more specific information when a PEAR DB method fails, use the methods provided by the error object:
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. "); printf ("Error code: %d ", $conn->getCode ()); printf ("Error message: %s ", $conn->getMessage ()); printf ("Error debug info: %s ", $conn->getDebugInfo ()); printf ("Error user info: %s ", $conn->getUserInfo ()); exit (1); }
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 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 ()); } } }
3.147.126.211