Home arrow MySQL arrow Page 4 - Error Handling Examples

Named Conditions - MySQL

In this second article in a three-part series, you will learn more about errors and exception handlers. 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.

TABLE OF CONTENTS:
  1. Error Handling Examples
  2. Handler Precedence
  3. Scope of Condition Handlers
  4. Named Conditions
By: O'Reilly Media
Rating: starstarstarstarstar / 18
September 06, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

So far, our examples have used conditions based on MySQL error codes, SQLSTATE codes, or predefined named conditions (SQLEXCEPTION, SQLWARNING, NOT FOUND). These handlers do the job required, but they do not result in particularly readable code, since they rely on the hardcoding of literal error numbers. Unless you memorize all or most of the MySQL error codes and SQLSTATE codes (and expect everyone maintaining your code to do the same), you are going to have to refer to a manual to understand exactly what error a handler is trying to catch.

You can improve the readability of your handlers by defining a condition declaration, which associates a MySQL error code orSQLSTATEcode with a meaningful name that you can then use in your handler declarations. The syntax for a condition declaration is:

  DECLARE condition_name CONDITION FOR {SQLSTATE sqlstate_code | MySQL_error_code};

Once we have declared our condition name, we can use it in our code instead of a MySQL error code orSQLSTATEcode. So instead of the following declaration:

  DECLARE CONTINUE HANDLER FOR 1216 MySQL_statements;

we could use the following more readable declaration:

  DECLARE foreign_key_error CONDITION FOR 1216;

  DECLARE CONTINUE HANDLER FOR foreign_key_error MySQL_statements;

Create named conditions using condition declarations, and use these named conditions in your handlers to improve the readability and maintainability of your stored program code.

Missing SQL:2003 Features

The SQL:2003 specification includes a few useful features that—at the time of writing—are not currently implemented in the MySQL stored program language. The absence of these features certainly limits your ability to handle unexpected conditions, but we expect that they will be implemented in MySQL server 5.2. Specifically:

  • There is no way to examine the current MySQL error code orSQLSTATEcode. This means that in an exception handler based on a generic condition such asSQLEXCEPTION, you have no way of knowing what error just occurred.
  • You cannot raise an exception of your own to indicate an application-specific error or to re-signal an exception after first catching the exception and examining its context.

We’ll describe these situations in the following sections and suggest ways to deal with them.

Missing SQL:2003 Features

Directly Accessing SQLCODE or SQLSTATE

Implementing a general-purpose exception handler would be a good practice, except that if you cannot reveal the reason why the exception occurred, you make debugging your stored programs difficult or impossible. For instance, consider Example 6-13.

Example 6-13. General-purpose—but mostly useless—condition handler

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
   
SET l_status=-1;
   
Set l_message='Some sort of error detected somewhere in the application'; END;

Receiving an error message like this is not much help—in fact, there is almost nothing more frustrating than receiving such an error message when trying to debug an application. Obscuring the actual cause of the error makes the condition handler worse than useless in most circumstances.

The SQL:2003 specification allows for direct access to the values ofSQLCODE(the “vendor”—in this case MySQL—error code) and theSQLSTATEcode. If we had access to these codes, we could produce a far more helpful message such as shown in Example 6-14.

Example 6-14. A more useful—but not supported—form of condition handler

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
   
SET l_status=-1;
   
SET l_message='Error '||sqlcode||' encountered';
END;

We can partially emulate the existence of aSQLCODE orSQLSTATEvariable by defining a more comprehensive set of condition handlers that create appropriateSQLCODE variables when they are fired. The general approach would look like Example 6-15.

Example 6-15. Using multiple condition handlers to expose the actual error code

DECLARE sqlcode INT DEFAULT 0;
DECLARE status_message VARCHAR(50);

DECLARE CONTINUE HANDLER FOR duplicate_key
BEGIN
     
SET sqlcode=1052;
     
SET status_message='Duplicate key error';
END;

DECLARE CONTINUE HANDLER FOR foreign_key_violated
BEGIN
    
SET sqlcode=1216;
     SET status_message='Foreign key violated';
END;

DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
    
SET sqlcode=1329;
    
SET status_message='No record found'; END;

In most circumstances, it is best not to define aSQLEXCEPTIONhandler, because without the ability to display theSQLSTATE orSQLSTATE, it is better to let the exception occur and allow the calling application to have full access to the error codes and messages concerned.

Until MySQL implements aSQLSTATEorSQLSTATEvariable, avoid creating a general-purposeSQLEXCEPTIONhandler. Instead, create handlers for individual error conditions that generate appropriate messages and status codes.

Please check back next week for the conclusion to this article.

 



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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

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