So far in this chapter, we have talked about how you can handle errors raised by MySQL as it executes SQL statements within the stored program. In addition to these system-raised exceptions, however, you will surely have to deal with errors that are specific to an application’s domain of requirements and rules. If that rule is violated in your code, you may want to raise your own error and communicate this problem back to the user. The SQL:2003 specification provides the SIGNAL statement for this purpose.

The SIGNAL statement allows you to raise your own error conditions. Unfortunately, at the time of writing, the SIGNAL statement is not implemented within the MySQL stored program language (it is currently scheduled for MySQL 5.2).

You can’t use the SIGNAL statement in MySQL 5.0, but we are going to describe it here, in case you are using a later version of MySQL in which the statement has been implemented. Visit this book’s web site (see the Preface for details) to check on the status of this and other enhancements to the MySQL stored program language.

So let’s say that we are creating a stored procedure to process employee date-of-birth changes, as shown in Example 6-16. Our company never employs people under the age of 16, so we put a check in the stored procedure to ensure that the updated date of birth is more than 16 years ago (the curdate() function returns the current timestamp).

This implementation will work, but it has a few disadvantages. The most significant problem is that if the procedure is called from another program, the procedure will return success (at least, it will not raise an error) even if the update was actually rejected. Of course, the calling program could detect this by examining the p_status variable, but there is a good chance that the program will assume that the procedure succeeded since the procedure call itself does not raise an exception.

We have designed the procedure so that it depends on the diligence of the programmer calling the procedure to check the value of the returning status argument. It is all too tempting and easy to assume that everything went fine, since there was no error.

To illustrate, if we try to set an employee’s date of birth to the current date from the MySQL command line, everything seems OK:

This stored procedure would be more robust, and less likely to allow errors to slip by, if it actually raised an error condition when the date of birth was invalid. The ANSI SQL:2003 SIGNAL statement allows you to do this:

You can create your own SQLSTATE codes (there are some rules for the numbers you are allowed to use) or use an existing SQLSTATE code or named condition. When MySQL implements SIGNAL , you will probably be allowed to use a MySQL error code (within designated ranges) as well.

When the SIGNAL statement is executed, a database error condition is raised that acts in exactly the same way as an error that might be raised by an invalid SQL statement or a constraint violation. This error could be returned to the calling program or could be trapped by a handler in this or another stored program. If SIGNAL were available to us, we might write the employee date-of-birth birth procedure, as shown in Example 6-17.

Example 6-17. Using the SIGNAL statement (expected to be implemented in MySQL 5.2)

IF DATE_SUB(curdate(), INTERVAL 16 YEAR) <P_DOB THEN SIGNAL employee_is_too_young SET MESSAGE_TEST=’Employee must be 16 years or older’; ELSE UPDATE employees SET date_of_birth=p_dob WHERE employee_id=p_employee_id; END IF; END;

If we ran this new procedure from the MySQL command line (when MySQL implements SIGNAL ), we would expect the following output:

mysql> CALL sp_update_employee(1,now()); ERROR 90001 (99001): Employee must be 16 years or older

Using SIGNAL , we could make it completely obvious to the user or calling program that the stored program execution failed.

{mospagebreak title=Emulating the SIGNAL Statement}

The absence of the SIGNAL statement makes some stored program logic awkward, and in some cases demands that calling applications examine OUT variables, rather than SQL return codes, to check the results of some operations.

There is, however, a way to force an error to occur and pass some diagnostic information back to the calling application. You can, in other words, emulate SIGNAL in MySQL 5.0, but we warn you: this solution is not pretty!

Where we would otherwise want to use the SIGNAL statement to return an error to the calling application, we can instead issue a SQL statement that will fail—and fail in such a way that we can embed our error message within the standard error message.

Missing SQL:2003 Features

The best way to do this is to issue a SQL statement that attempts to reference a nonexistent table or column. The name of the nonexistent column or table can include the error message itself, which will be useful because the name of the column or table is included in the error message.

Example 6-18 shows how we can do this. We try to select a nonexistent column name from a table and we make the nonexistent column name comprise our error message. Note that in order for a string to be interpreted as a column name, it must be enclosed by backquotes (these are the quote characters normally found on your keyboard to the left of the 1 key).

Example 6-18. Using a nonexistent column name to force an error to the calling program

IF datediff(curdate(),p_dob)<(16*365) THEN UPDATE `Error: employee_is_too_young; Employee must be 16 years or older` SET x=1; ELSE UPDATE employees SET date_of_birth=p_dob WHERE employee_id=p_dob; END IF; END;

If we try to run the stored procedure from the MySQL command line, passing in an invalid date of birth, we get a somewhat informative error message:

The error code is somewhat garbled, and the error code is not in itself accurate, but at least we have managed to signal to the calling application that the procedure did not execute successfully and we have at least provided some helpful information.

We can somewhat improve the reliability of our error handling—and also prepare for a future in which the SIGNAL statement is implemented—by creating a generic procedure to implement our SIGNAL workaround. Example 6-19 shows a procedure that accepts an error message and then constructs dynamic SQL that includes that message within an invalid table name error.

IF datediff(curdate(),p_dob)<(16*365) THEN CALL my_signal(‘Error: employee_is_too_young; Employee must be 16 years or older’); ELSE UPDATE employees SET date_of_birth=p_dob WHERE employee_id=p_employee_id; END IF; END$$

Not only does this routine result in cleaner code that is easier to maintain, but when MySQL does implement SIGNAL , we will only need to update our code in a single procedure.

{mospagebreak title=Putting It All Together}

We have now covered in detail the error-handling features of MySQL. We’ll finish up this discussion by offering an example that puts all of these features together. We will take a simple stored procedure that contains no exception handling and apply the concepts from this chapter to ensure that it will not raise any unhandled exceptions for all problems that we can reasonably anticipate.

The example stored procedure creates a new departments row. It takes the names of the new department, the manager of the department, and the depar

tment’s location. It retrieves the appropriate employee_id from the employees table using the manager’s name. Example 6-21 shows the version of the stored procedure without exception handling.

This program reflects the typical development process for many of us: we concen trate on implementing the required functionality (the “positive”) and generally pay little attention to (or more likely, want to avoid thinking about) what could possibly go wrong. The end result is a stored program that contains no error handling.

So either before you write the program (ideally) or after the first iteration is done, you should sit down and list out all the errors that might be raised by MySQL when the program is run.

Here are several of the failure points of this stored procedure:

If the manager’s name is incorrect, we will fail to find a matching manager in the employees table. We will then attempt to insert a NULL value for the MANAGER_ID column, which will violate its NOT NULL constraint.

If the location argument does not match a location in the locations table, the foreign key constraint between the two tables will be violated.

If we specify a department_name that already exists, we will violate the unique constraint on the department_name .

The code in Example 6-22 demonstrates these failure scenarios.

Example 6-22. Some of the errors generated by a stored procedure without error handling

The good news is that MySQL detects these problems and will not allow bad data to be placed into the table. If this stored procedure will be called only by the host language, such as PHP or Java, we could declare ourselves done. If, on the other hand, this program might be called from another MySQL stored program, then we need to handle the errors and return status information so that the calling stored program can take appropriate action. Example 6-23 shows a version of the stored procedure that handles all the errors shown in Example 6-22.

Let’s go through Example 6-23 and review the error-handling code we have added.

Line(s)

Significance

12 and 13

Create condition declarations for duplicate key (1062) and foreign key (1216) errors. As we noted earlier, these declarations are not strictly necessary, but they improve the readability of the condition handlers we will declare later.

31-48

Define handlers for each of the exceptions we think might occur. The condition names match those we defined in lines 10 and 11. We didn’t have to create a NOTFOUNDcondition, since this is a predefined condition name. Each handler sets an appropriate value for the output status variables p_sqlcodeand p_status_message.

Line(s)

Significance

57

On this line we check the value of the p_sqlcode variable following our fetch from the cursor that retrieves the manager’s employee_id . If p_sqlcode is not 0, then we know that one of our exception handlers has fired. We add some context information to the message—identifying the statement we were executing—and avoid attempting to execute the insert into the departments table.

53

Check the value of the p_sqlcode variable following our insert operation. Again, if the value is nonzero, we know that an error has occurred, and we add some context information to the error message. At line 53, we don’t know what error has occurred—it could be either the foreign key or the unique index constraint. The handler itself controls the error message returned to the user, and so we could add handling for more error conditions by adding additional handlers without having to amend this section of code.

Running the stored procedure from the MySQL command line shows us that all the exceptions are now correctly handled. Example 6-24 shows the output generated by various invalid inputs.

{mospagebreak title=Handling Stored Program Errors in the Calling Application}

Throughout this chapter, we’ve often talked about “returning the error to the calling application.” In our examples we have used the MySQL command-line client to represent the calling application since this client is common to all environments and readers, and it allows you (and us) to quickly test out the stored program.

In production environments, however, the calling application will not be the MySQL command-line program, but possibly a web-based application using PHP, Perl, Java, Python, or .NET (C# or Visual Basic) to interface with the MySQL stored program. In Chapters 12 through 17, we look in detail at how to invoke stored programs from a variety of languages. We also cover various techniques for retrieving status and error messages from these languages. However, since we’re on the topic of error handling, let’s briefly look at how we can process errors generated by a stored program called from each of these languages.

PHP

PHP provides a variety of ways of interacting with MySQL. There are four major interfaces available:

PEAR (PHP Extension and Application Repository)

The PEAR repository includes a standard, database-independent module called PEAR DB that can be used to interface with almost any relational database.

mysql

PHP includes a MySQL-specific interface inventively called the mysql extension.

mysqli

Commencing with PHP 5, a new interface— mysqli —was introduced (according to the developer, the “ i ” stands for “any one of: improved, interface, ingenious, incompatible, or incomplete”). This interface provides better support for new features of MySQL.

The mysqli and PDO interfaces provide the best support for MySQL stored programs and other new features of MySQL 5.0.

In Chapter 13, we show in detail how to use stored programs with each of the major PHP interfaces and provide examples of both procedural and nonprocedural styles. For now, let’s look at a simple example showing how to process errors using the object-oriented variant of the mysqli interface.

In Example 6-25, a simple stored procedure—one without OUT parameters or result sets—is executed on line 8. If the method call returns failure, we can examine various properties of the database connection object ( $dbh in this example). $dbh->errno contains the MySQL error code, $ dbh->error contains the error message, and $dbh-> sqlstate contains the SQLSTATE code.

State Contains the SQLSTATE variable. However, the SQLSTATE variable usually includes only a generic success or failure code.

Each of these items can be referenced as a method or a property, so, for instance, you can reference the last MySQL error code for the connect handle $dbh as either $dbh::err or $dbh->err .

Example 6-26 shows a simple Perl code fragment that executes a stored procedure and checks the error status. On line 5 we execute a simple stored procedure (one without parameters or result sets). If the stored procedure call fails, we interrogate the error methods from the database handle.

Like most modern object-oriented languages, Java uses structured exception han dling to allow for flexible and efficient interception and handling of runtime errors. Rather than check the error status of every database call, we enclose our JDBC statements within a try block. If any of these statements causes a SQLException error, then the catch handler will be invoked to handle the error.

The catch handler has access to a SQLException object that provides various methods and properties for diagnosing and interpreting the error. Of most interest to us are these three methods:

getErrorCode() Returns the MySQL-specific error code

getSQLState() Returns the ANSI-standard SQLSTATE code

getMessage() Returns the full text of the error message

Example 6-27 shows an example of invoking a simple stored procedure that involves no OUT parameters or result sets. On line 8 we create a statement object, and on line 9 we use the execute method of that object to execute the stored procedure. If an error occurs, the catch block on line 11 is invoked, and the relevant methods of the SQLException object are used to display the details of the error.

Python can connect to MySQL using the MySQLdb extension. This extension generates Python exceptions if any MySQL errors are raised during execution. We enclose our calls to MySQL in a try block and catch any errors in an except block.

Example 6-28 shows how we can connect to MySQL and execute a stored procedure in Python. Line 1 commences the try block, which contains our calls to MySQL. On line 2 we connect to MySQL. On line 7 we create a cursor (SQL statement handle), and on line 8 we execute a stored procedure call.

If any of these calls generates a MySQL error condition, we jump to the except block on line 11. The MySQLdb.Error object (aliased here as e ) contains two elements: element 0 is the MySQL error code, and element 1 is the MySQL error message.

C# .NET

MySQL provides an ADO.NET connector—MySQL Connector/Net—that allows any .NET-compatible language to interact with a MySQL server.

In this chapter we provide a short example of handling stored procedure errors from a C# program. More details are provided in Chapter 17.

As in Java, C# provides an exception-handling model that relieves the developer of the necessity of checking for error conditions after every statement execution. Instead, commands to be executed are included within a try block. If an error occurs for any of these statements, execution switches to the catch block, in which appropriate error handling can be implemented.

Example 6-29 shows an example of error handling for a simple stored procedure (one without output parameters or result sets) in C#. A statement object for the stored procedure is created on line 15, and the statement is executed on line 17. If a MySqlException (essentially any MySQL error) occurs, the error handler defined on line 19 is invoked.

catch blocks have access to a MySQLException object; this object includes Message and Number properties, which contain the MySQL error message and error number, respectively.

Visual Basic .NET

The process for handling stored program errors in Visual Basic .NET (VB.NET) is practically identical to that of C#.

Example 6-30 shows an example of error handling for a simple stored procedure (one without output parameters or result sets) in VB.NET. A statement object for the stored procedure is created on lines 16 and 17, and the statement is executed on line 18. If a MySqlException (essentially any MySQL error) occurs, the error handler defined in lines 20-24 is invoked.

Catch blocks have access to a MySQLException object; this object includes Message and Number properties, which contain the MySQL error message and error number, respectively.

{mospagebreak title=Conclusion}

In this chapter we examined the MySQL error handlers that allow you to catch error conditions and take appropriate corrective actions. Without error handlers, your stored programs will abort whenever they encounter SQL errors, returning control to the calling program. While this might be acceptable for some simple stored programs, it is more likely that you will want to trap and handle errors within the stored program environment, especially if you plan to call one stored program from another. In addition, you need to declare handlers for cursor loops so that an error is not thrown when the last row is retrieved from the cursor.

Handlers can be constructed to catch all errors, although this is currently not best practice in MySQL, since you do not have access to an error code variable that would allow you to differentiate between possible error conditions or to report an appropriate diagnostic to the calling program. Instead, you should declare individual handlers for error conditions that can reasonably be anticipated. When an unexpected error occurs, it is best to let the stored program abort so that the calling program has access to the error codes and messages.

Handlers can be constructed that catch either ANSI-standard SQLSTATE codes or MySQL-specific error codes. Using the SQLSTATE codes leads to more portable code, but because specific SQLSTATE codes are not available for all MySQL error conditions, you should feel free to construct handlers against MySQL-specific error conditions.

To improve the readability of your code, you will normally want to declare named conditions against the error codes you are handling, so that the intention of your handlers is clear. It is far easier to understand a handler that traps DUPLICATE_KEY_VALUE than one that checks for MySQL error code 1062.

At the time of writing, some critical SQL:2003 error-handling functionality has yet to be implemented in MySQL, most notably the ability to directly access the SQLSTATE or SQLSTATE variables, as well as the ability to raise an error condition using the SIGNAL statement. In the absence of a SQLSTATE or SQLCODE variable, it is good practice for you to define handlers against all error conditions that can reasonably be anticipated that populate a SQLCODE -like variable that you can use within your program code to detect errors and take appropriate action. We expect MySQL to add these “missing” features in version 5.2—you should check to see if they have been implemented in the time since this book was written (see the book’s web site for details). Note also that it is currently possible to provide a workaround (though a somewhat awkward one) for the missing SIGNAL statement if you find that it is absolutely necessary in your programs.