Using the SIGNAL Statement for Error Handling - Conclusion (Page 5 of 5 )
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter six of the book MySQL Stored Procedure Programming, written by Guy Harrison and Steven Feuerstein (O'Reilly; ISBN: 0596100892). Check it out today at your favorite bookstore. Buy this book now.
|
|
