Missing SQL:2003 Features

Missing SQL 2003 Features

The SQL:2003 specification includes a few useful features thatat the time of writingare not currently implemented in the MySQL stored program language. The absence of these features certainly limits your ability to handle unexpected conditions, but we expect that they will be implemented in MySQL server 5.2. Specifically:

• There is no way to examine the current MySQL error code or SQLSTATE code. This means that in an exception handler based on a generic condition such as SQLEXCEPTION, you have no way of knowing what error just occurred.
• You cannot raise an exception of your own to indicate an application-specific error or to re-signal an exception after first catching the exception and examining its context.

We'll describe these situations in the following sections and suggest ways to deal with them.

6.4.1. Directly Accessing SQLCODE or SQLSTATE

Implementing a general-purpose exception handler would be a good practice, except that if you cannot reveal the reason why the exception occurred, you make debugging your stored programs difficult or impossible. For instance, consider Example 6-13.

Example 6-13. General-purposebut mostly uselesscondition handler

Receiving an error message like this is not much helpin fact, there is almost nothing more frustrating than receiving such an error message when trying to debug an application. Obscuring the actual cause of the error makes the condition handler worse than useless in most circumstances.

The SQL:2003 specification allows for direct access to the values of SQLCODE (the "vendor"in this case MySQLerror code) and the SQLSTATE code. If we had access to these codes, we could produce a far more helpful message such as shown in Example 6-14.

Example 6-14. A more usefulbut not supportedform of condition handler

We can partially emulate the existence of a SQLCODE or SQLSTATE variable by defining a more comprehensive set of condition handlers that create appropriate SQLCODE variables when they are fired. The general approach would look like Example 6-15.

Example 6-15. Using multiple condition handlers to expose the actual error code

In most circumstances, it is best not to define a SQLEXCEPTION handler, because without the ability to display the SQLSTATE or SQLSTATE, it is better to let the exception occur and allow the calling application to have full access to the error codes and messages concerned.

6.4.2. Creating Your Own Exceptions with the SIGNAL Statement

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).

Example 6-16. Example stored procedure with date-of-birth validation

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:

mysql> CALL sp_update_employee_dob(1,now( ),@status); Query OK, 0 rows affected (0.01 sec)

It is only if we examine the status variable that we realize that the update did not complete:

mysql> SELECT @status; +------------------------------------+ | @status | +------------------------------------+ | Employee must be 16 years or older | +------------------------------------+ 1 row in set (0.00 sec)

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:

SIGNAL takes the following form:

SIGNAL SQLSTATE sqlstate_code|condition_name [SET MESSAGE_TEXT=string_or_variable];

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 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.

6.4.3. 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 failand fail in such a way that we can embed our error message within the standard error message.

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 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:

MySQL> CALL sp_update_employee_dob2(2,now( )) ; ERROR 1054 (42S22): Unknown column 'Error: employee_is_too_young; Employee must be 16 years or older' in 'field list'

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 handlingand also prepare for a future in which the SIGNAL statement is implementedby 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.

Example 6-19. Standard procedure to emulate SIGNAL

We could now implement our employee date-of-birth update routine to call this routine, as shown in Example 6-20.

Example 6-20. Using our SIGNAL emulation procedure to raise an error

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.