Home arrow MySQL arrow Using the SIGNAL Statement for Error Handling

Using the SIGNAL Statement for Error Handling

In this conclusion to a three-part series, you will learn about the SIGNAL statement and its uses for creating your own error statements. It is excerpted from chapter six of the book MySQL Stored Procedure Programming, written by Guy Harrison and Steven Feuerstein (O'Reilly; ISBN: 0596100892). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

  1. Using the SIGNAL Statement for Error Handling
  2. Emulating the SIGNAL Statement
  3. Putting It All Together
  4. Handling Stored Program Errors in the Calling Application
  5. Conclusion
By: O'Reilly Media
Rating: starstarstarstarstar / 4
September 13, 2007

print this article



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.

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

You can’t use theSIGNALstatement 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 (thecurdate()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))
    IF DATE_SUB(curdate(), INTERVAL 16 YEAR) <p_dob THEN
         SET p_status='Employee must be 16 years or older';
         UPDATE employees
SET date_of_birth=p_dob
WHERE employee_id=p_employee_id;
SET p_status='Ok';

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 thep_statusvariable, 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:2003SIGNALstatement allows you to do this:

SIGNALtakes the following form:

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

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

When theSIGNALstatement 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. IfSIGNALwere 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)
DECLARE employee_is_too_young CONDITION FOR SQLSTATE '99001';

         SIGNAL employee_is_too_young
            SET MESSAGE_TEST='Employee must be 16 years or older';
        UPDATE employees
           SET date_of_birth=p_dob
         WHERE employee_id=p_employee_id;
    END IF;

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

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

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

>>> More MySQL Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: