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

Missing SQL:2003 Features

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

CREATE PROCEDURE sp_update_employee_dob
    (p_employee_id INT, p_dob DATE, OUT p_status varchar(30))
BEGIN
    IF DATE_SUB(curdate(), INTERVAL 16 YEAR) <p_dob THEN
         SET p_status='Employee must be 16 years or older';
    ELSE
         UPDATE employees
            SET date_of_birth=p_dob
         WHERE employee_id=p_employee_id;
         SET p_status='Ok';
    END IF;
END;

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)

CREATE PROCEDURE sp_update_employee_dob
    (p_employee_id int, p_dob date)
BEGIN
    DECLARE employee_is_too_young CONDITION FOR SQLSTATE '99001';

    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.