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

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



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-standardSQLSTATE codes or MySQL-specific error codes. Using theSQLSTATEcodes leads to more portable code, but because specificSQLSTATEcodes 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 trapsDUPLICATE_KEY_VALUEthan 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 theSQLSTATEorSQLSTATEvariables, as well as the ability to raise an error condition using theSIGNALstatement. In the absence of aSQLSTATEorSQLCODEvariable, it is good practice for you to define handlers against all error conditions that can reasonably be anticipated that populate aSQLCODE-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 missingSIGNALstatement if you find that it is absolutely necessary in your programs.

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