Home arrow MySQL arrow Page 3 - Error Handling

Condition Handlers - MySQL

In this first article in a three-part series, you will learn how to create various types of 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
  2. Handling Last Row Conditions
  3. Condition Handlers
  4. Handler Conditions
By: O'Reilly Media
Rating: starstarstarstarstar / 14
August 30, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

A condition handler defines the actions that the stored program is to take when a specified event—such as a warning or an error—occurs.

Here is the syntax of the DECLARE HANDLER command:

  DECLARE {CONTINUE | EXIT} HANDLER FOR
      {SQLSTATE sqlstate_code| MySQL error code| condition_name}
     
handler_actions

Note that handlers must be defined after any variable or cursor declarations, which makes sense, since the handlers frequently access local variables or perform actions on cursors (such as closing them). They must also be declared before any executable statements. Chapter 4 includes more details on the rules governing the positioning of statements within a block.

The hander declaration has three main clauses;

  1. Handler type (CONTINUE,EXIT)
  2. Handler condition (SQLSTATE, MySQL error code, named condition) 
  3. Hander actions

 Let’s look at each of these clauses in turn.

Types of Handlers

Condition handlers can be one of two types:

EXIT

When anEXIThandler fires, the currently executing block is terminated. If this block is the main block for the stored program, the procedure terminates, and control is returned to the procedure or external program that invoked the procedure. If the block is enclosed within an outer block inside of the same stored program, control is returned to that outer block.

CONTINUE

With aCONTINUEhandler, execution continues with the statement following the one that caused the error to occur.

In either case, any statements defined within the hander (the handler actions) are run before either theEXITorCONTINUEtakes place.

Let’s look at examples of both types of handlers. Example 6-6 shows a stored procedure that creates a department record and attempts to gracefully handle the situation in which the specified department already exists.

Example 6-6. Example of an EXIT handler

1CREATE PROCEDURE add_department
2      (in_dept_name VARCHAR(30),
3       in_location VARCHAR(30),
4       in_manager_id INT)
5    MODIFIES SQL DATA
6BEGIN
7    DECLARE duplicate_key INT DEFAULT 0;
8    BEGIN
9         DECLARE EXIT HANDLER FOR 1062 /* Duplicate key*/ SET duplicate_key=1;
10
11        INSERT INTO departments (department_name,location,manager_id)
12        VALUES(in_dept_name,in_location,in_manager_id);
13
14        SELECT CONCAT('Department ',in_dept_name,' created') as "Result";
15    END;
16
17   IF duplicate_key=1 THEN
18        SELECT CONCAT('Failed to insert ',in_dept_name,
19          ': duplicate key') as "Result";
20   END IF;
21END$$

Let’s examine the logic for Example 6-6:

Lines(s)

Explanation

7

Declare a status variable that will record the status of our insert attempt.

8-15

This BEGIN-ENDblock encloses the INSERTstatement that will attempt to create the department row. The block includes the EXIThandler that will terminate the block if a 1062 error occurs.

11

Attempt to insert our row—if we get a duplicate key error, the handler will set the variable and terminate the block.

14

This line executes only if the EXIThandler did not fire, and reports success to the user. If the handler fired, then the block was terminated and this line would never be executed.

17

Execution will then continue on this line, where we check the value of the variable and—if the hander has fired—advise the user that the insert was unsuccessful.

Following is the output from this stored procedure for both unsuccessful and successful execution:

  MySQL> CALL add_department('OPTIMIZER RESEARCH','SEATTLE',4) //

  +----------------------------------------+
  | Result                                 |
  +----------------------------------------+
  | Failed to insert OPTIMIZER RESEARCH: duplicate key                              |
  +----------------------------------------+
 
1 row in set (0.02 sec)

  MySQL> CALL add_department('CUSTOMER SATISFACTION','DAVIS',4);

  +----------------------------------------+
  | Result                                 |
  +----------------------------------------+
  | Department CUSTOMER SATISFACTION
created                                    |
  +----------------------------------------+
  1 row in set (0.00 sec)

Example 6-7 provides an example of the same functionality implemented with aCONTINUEhandler. In this example, when the handler fires, execution continues with the statement immediately following theINSERTstatement. ThisIFstatement checks to see if the handler has fired, and if it has, it displays the failure message. Otherwise, the success message is displayed.

Example 6-7. Example of a CONTINUE handler

CREATE PROCEDURE add_department
       (in_dept_name VARCHAR(30),
        in_location VARCHAR(30),

        in_manager_id INT)
  MODIFIES SQL DATA
BEGIN
    DECLARE duplicate_key INT DEFAULT 0;

    DECLARE CONTINUE HANDLER FOR 1062 /* Duplicate key*/
            SET duplicate_key=1;

    INSERT INTO departments (department_name,location,manager_id)
    VALUES(in_dept_name,in_location,in_manager_id);

    IF duplicate_key=1 THEN
         SELECT CONCAT('Failed to insert ',in_dept_name,
             ': duplicate key') as "Result";
    ELSE
              SELECT CONCAT('Department ',in_dept_name,' created') as "Result";
    END IF;
END$$


EXIT or CONTINUE?

The choice between creating an EXIT handler and creating aCONTINUEhandler is based primarily on program flow-of-control considerations.

AnEXIThandler will exit from the block in which it is declared, which precludes the possibility that any other statements in the block (or the entire procedure) might be executed. This type of handler is most suitable for catastrophic errors that do not allow for any form of continued processing.

ACONTINUEhandler allows subsequent statements to be executed. Generally, you will detect that the handler has fired (through some form of status variable set in the handler) and determine the most appropriate course of action. This type of handler is most suitable when you have some alternative processing that you will execute if the exception occurs.




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