Introduction to Error Handling

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

6.1.1. 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 the HANDLER clause later in this chapter. For now, it is enough to understand that the DECLARE CONTINUE HANDLER statement tells MySQL that "if you encounter MySQL error 1062 (duplicate entry for key), then continue execution but set the variable p_status to '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 in sp_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;

6.1.2. Handling Last Row Conditions

One of the most common operations in a MySQL stored program involves fetching one or more rows of data. You can do this in a stored program through the use of a cursor (explained in Chapter 5). However, MySQL (and the ANSI standard) considers an attempt to fetch past the last row of the cursor an error. Therefore, you almost always need to catch that particular error when looping through the results from a cursor.

Consider the simple cursor loop shown in Example 6-4. At first glance, you might worry that we might inadvertently have created an infinite loop, since we have not coded any way to leave the dept_loop loop.

Example 6-4. Cursor loop without a NOT FOUND handler

CREATE PROCEDURE sp_fetch_forever( )
 READS SQL DATA
BEGIN
 DECLARE l_dept_id INT;
 DECLARE c_dept CURSOR FOR
 SELECT department_id
 FROM departments;

 OPEN c_dept;
 dept_cursor: LOOP
 FETCH c_dept INTO l_dept_id;
 END LOOP dept_cursor;
 CLOSE c_dept;
END

Bravely, we run this program and find that the seemingly infinite loop fails as soon as we attempt to fetch beyond the final row in the result set:

 mysql> CALL sp_fetch_forever( );
 ERROR 1329 (02000): No data to FETCH

Since we likely want to do something with the data after we've fetched it, we cannot let this exception propagate out of the procedure unhandled. So we will add a declaration for a CONTINUE HANDLER in the procedure, setting a flag to indicate that the last row has been fetched. This technique is shown in Example 6-5.

Example 6-5. Cursor loop with a NOT FOUND handler

1 CREATE PROCEDURE sp_not_found( )
2 READS SQL DATA
3 BEGIN
4 DECLARE l_last_row INT DEFAULT 0;
5 DECLARE l_dept_id INT;
6 DECLARE c_dept CURSOR FOR
7 SELECT department_id
8 FROM departments;
9 /* handler to set l_last_row=1 if a cursor returns no more rows */
10 DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row=1;
11
12 OPEN c_dept;
13 dept_cursor: LOOP
14 FETCH c_dept INTO l_dept_id;
15 IF (l_last_row=1) THEN
16 LEAVE dept_cursor;
17 END IF;
18 /* Do something with the data*/
19
20 END LOOP dept_cursor;
21 CLOSE c_dept;
22
23 END;

In plain English, the handler on line 10 says "When a fetch from a cursor returns no more rows, continue execution, but set the variable l_last_row to 1." After retrieving each row, we check the l_last_row variable and exit from the cursor loop if the last row is returned. Without this handler, our cursor loop will fetch too many times and raise an exception.

Now that you have seen two simple examples of declaring handlers for error situations that you can anticipate, let's explore this functionality in more detail.