Home arrow MySQL arrow Error Handling

Error Handling

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.

  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



The perfect programmer, living in a perfect world, would always write programs that anticipate every possible circumstance. Those programs would either always work correctly, or fail “gracefully” by providing comprehensive diagnostic information to the support team and very readable messages to the user.

For a certain class of applications—software supporting life support systems or the space shuttle, for instance—this level of perfection is actually a part of the requirements, because any unexpected failure of the software would be catastrophic. However, in the world of business applications, we usually make certain assumptions about our execution environment—we assume the MySQL server will be running, that our tables have not been dropped, that the host machine is not on fire, and so on. If any of these conditions occurs, then we accept that our application will fail. In many other circumstances, we can and should anticipate potential failures and write code to manage those situations. This is where exception handling comes into play.

When a stored program encounters an error condition, execution ceases and an error is returned to the calling application. That’s the default behavior. What if we need a different kind of behavior? What if, for example, we want to trap that error, log it, or report on it, and then continue execution of our application? For that kind of control, we need to define exception handlers in our programs.

When developing MySQL stored programs, a very common scenario—fetching to the end of a result set—also requires that we define an exception handler.

In this chapter we explain how to create various types of exception handlers and how to improve the readability of error handling by using “named” conditions. We also identify several gaps in exception-handling functionality in MySQL 5, and explore ways of compensating for these omissions.

Introduction to Error Handling

Let’s begin by looking at several examples of stored program error handling.

A Simple First Example

Consider a simple stored procedure that creates a location record, as shown in Example 6-1.

Example 6-1. Simple stored procedure without error handling

CREATE PROCEDURE sp_add_location
(in_location VARCHAR(30), 
         in_address1 VARCHAR(30),
         in_address2 VARCHAR(30),
         zipcode     VARCHAR(10))
INSERT INTO locations
     (in_location,in_address1, in_address2,zipcode);

This procedure works fine when the location does not already exist, as shown in the following output:

  mysql> CALL sp_add_location('Guys place','30 Blakely Drv',
                    'Irvine CA','92618-20');

  Query OK, 1 row affected, 1 warning (0.44 sec)

However, if we try to insert a department that already exists, MySQL raises an error:

  mysql> CALL sp_add_location('Guys place','30 Blakely Drv',
                    'Irvine CA','92618-20');

  ERROR 1062 (23000): Duplicate entry 'Guys place' for key 1

If the stored procedure is called by an external program such as PHP, we could probably get away with leaving this program “as is.” PHP, and other external programs, can detect such error conditions and then take appropriate action. If the stored procedure is called from another stored procedure, however, we risk causing the entire procedure call stack to abort. That may not be what we want.

Since we can anticipate that MySQL error 1062 could be raised by this procedure, we can write code to handle that specific error code. Example 6-2 demonstrates this technique. Rather than allow the exception to propagate out of the procedure unhandled (causing failures in the calling program), the stored procedure traps the exception, sets a status flag, and returns that status information to the calling program.

The calling program can then decide if this failure warrants termination or if it should continue execution.

Example 6-2. Simple stored procedure with error handling

CREATE PROCEDURE sp_add_location
(in_location    VARCHAR(30),
          in_address1    VARCHAR(30),
          in_address2    VARCHAR(30),
          zipcode        VARCHAR(10),
          OUT out_status VARCHAR(30))
     SET out_status='Duplicate Entry';

  SET out_status='OK';
  INSERT INTO locations
 (in_location,in_address1,in_address2,zipcode); END;

We’ll review in detail the syntax of theHANDLER clause later in this chapter. For now, it is enough to understand that theDECLARE CONTINUE HANDLERstatement tells MySQL that “if you encounter MySQL error 1062 (duplicate entry for key), then continue execution but set the variablep_statusto
'Duplicate Entry'.”

As expected, this implementation does not return an error to the calling program, and we can examine the status variable to see if the stored procedure execution was successful. In Example 6-3 we show a stored procedure that creates new department records. This procedure calls our previous procedure to add a new location. If the location already exists, the stored procedure generates a warning and continues. Without the exception handling insp_add_location, this procedure would terminate when the unhandled exception is raised.

Example 6-3. Calling a stored procedure that has an error handler

CREATE PROCEDURE sp_add_department
       (in_department_name VARCHAR(30),
        in_manager_id   INT,
        in_location     VARCHAR(30),
        in_address1     VARCHAR(30),
        in_address2     VARCHAR(30),
        in_zipcode      VARCHAR(10)
    DECLARE l_status VARCHAR(20);

    CALL sp_add_location(in_location,in_address1,in_address2, 
                      in_zipcode, l_status);
      IF l_status='Duplicate Entry' THEN
            SELECT CONCAT('Warning: using existing definition for location ', 
                    in_location) AS warning;
    END IF;

    INSERT INTO departments (manager_id,department_name,location)


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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


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