Error Handling Examples - Handler Precedence (Page 2 of 4 )
As we’ve described, MySQL lets you define handler conditions in terms of a MySQL error code, a SQLSTATE error, or a named condition such as SQLEXCEPTION. It is possible, therefore, that you could define several handlers in a stored program that would all be eligible to fire when a specific error occurred. Yet only one handler can fire in response to an error, and MySQL has clearly defined rules that determine the precedence of handlers in such a situation.
To understand the problem, consider the code fragment in Example 6-8. We have declared three different handlers, each of which would be eligible to execute if a duplicate key value error occurs. Which handler will execute? The answer is that the most specific handler will execute.
Example 6-8. Overlapping condition handlers
DECLARE EXIT HANDLER FOR 1062 SELECT 'MySQL error 1062 encountered';
DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLException encountered';
DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000';
INSERT INTO departments VALUES (1, 'Department of Fred',22,'House of Fred');
Handlers based on MySQL error codes are the most specific type of handler, since an error condition will always correspond to a single MySQL error code.SQLSTATEcodes can sometimes map to many MySQL error codes, so they are less specific. General conditions such asSQLEXCEPTIONandSQLWARNINGare not at all specific. Therefore, a MySQL error code takes precedence over aSQLSTATEexception, which, in turn, takes precedence over aSQLEXCEPTIONcondition.
If multiple exception handlers are eligible to fire upon an error, the most specific handler will be invoked. This means that a MySQL error code handler fires before aSQLSTATE handler, which, in turn, fires before aSQLEXCEPTIONhandler.
This strictly defined precedence allows us to define a general-purpose handler for unexpected conditions, while creating a specific handler for those circumstances that we can easily anticipate. So, for instance, in Example 6-9, the first handler will be invoked if something catastrophic happens (perhaps a jealous colleague drops your database tables), while the second will fire in the more likely event that someone tries to create a duplicate row within your database.
Example 6-9. Example of overlapping condition handling
DECLARE EXIT HANDLER FOR 1062
SELECT 'Attempt to create a duplicate entry occurred';
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT 'Unexpected error occurred –
make sure Fred did not drop your tables again';
Note, however, that we generally don’t advise creatingSQLEXCEPTIONhandlers until MySQL implements theSIGNALstatement; see “Missing SQL:2003 Features” later in this chapter.
Next: Scope of Condition Handlers >>
More MySQL Articles
More By O'Reilly Media
|
 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.
|
|
/ 3 
