Home arrow MySQL arrow Page 2 - Error Handling Examples

Handler Precedence - 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

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.



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