Error Handling (Page 1 of 4 )
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.
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))
MODIFIES SQL DATA
BEGIN
INSERT INTO locations
(location,address1,address2,zipcode)
VALUES
(in_location,in_address1, in_address2,zipcode);
END$$
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))
MODIFIES SQL DATA
BEGIN
DECLARE CONTINUE HANDLER FOR 1062
SET out_status='Duplicate Entry';
SET out_status='OK';
INSERT INTO locations
(location,address1,address2,zipcode)
VALUES
(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)
)
MODIFIES SQL DATA
BEGIN
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)
VALUES(in_manager_id,in_department_name,in_location);
END;
Next: Handling Last Row Conditions >>
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.
|
|
