Home arrow MySQL arrow Page 2 - Using the SIGNAL Statement for Error Handling

Emulating the SIGNAL Statement - MySQL

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



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, emulateSIGNALin MySQL 5.0, but we warn you: this solution is not pretty!

Where we would otherwise want to use theSIGNAL 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

CREATE PROCEDURE sp_update_employee_dob2
    (p_employee_id INT, p_dob DATE)

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

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 handling—and also prepare for a future in which theSIGNALstatement is implemented—by creating a generic procedure to implement ourSIGNAL 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

CREATE PROCEDURE `my_signal`(in_errortext VARCHAR(255))
'` SET x=1');
PREPARE my_signal_stmt FROM @sql;
EXECUTE my_signal_stmt;
DEALLOCATE PREPARE my_signal_stmt;

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

CREATE PROCEDURE sp_update_employee_dob2(p_employee_id INT, p_dob DATE)


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

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

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