Condition Handlers

A condition handler defines the actions that the stored program is to take when a specified eventsuch as a warning or an erroroccurs.

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;

• Handler type (CONTINUE, EXIT)
• Handler condition (SQLSTATE, MySQL error code, named condition)
• Hander actions

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

6.2.1. Types of Handlers

Condition handlers can be one of two types:

 

EXIT

When an EXIT handler 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 a CONTINUE handler, 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 the EXIT or CONTINUE takes 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

1 CREATE PROCEDURE add_department
2 (in_dept_name VARCHAR(30),
3 in_location VARCHAR(30),
4 in_manager_id INT)
5 MODIFIES SQL DATA
6 BEGIN
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;
21 END$$

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

Line(s)	Explanation
7	Declare a status variable that will record the status of our insert attempt.
8-15	This BEGIN-END block encloses the INSERT statement that will attempt to create the department row. The block includes the EXIT handler that will terminate the block if a 1062 error occurs.
11	Attempt to insert our rowif we get a duplicate key error, the handler will set the variable and terminate the block.
14	This line executes only if the EXIT handler 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 andif the hander has firedadvise 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 a CONTINUE handler. In this example, when the handler fires, execution continues with the statement immediately following the INSERT statement. This IF statement 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 a CONTINUE handler is based primarily on program flow-of-control considerations.

An EXIT handler 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.

A CONTINUE handler 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.

 

6.2.2. Handler Conditions

The handler condition defines the circumstances under which the handler will be invoked. The circumstance is always associated with an error condition, but you have three choices as to how you define that error:

• As a MySQL error code.
• As an ANSI-standard SQLSTATE code.
• As a named condition. You may define your own named conditions (described in the later section "Named Conditions") or use one of the built-in conditions SQLEXCEPTION, SQLWARNING, and NOT FOUND.

MySQL has its own set of error codes that are unique to the MySQL server. A handler condition that refers to a numeric code without qualification is referring to a MySQL error code. For instance, the following handler will fire when MySQL error code 1062 (duplicate key value) is encountered:

 DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key=1;

SQLSTATE error codes are defined by the ANSI standard and are database-independent, meaning that they will have the same value regardless of the underlying database. So, for instance, Oracle, SQL Server, DB2, and MySQL will always report the same SQLSTATE value (23000) when a duplicate key value error is encountered. Every MySQL error code has an associated SQLSTATE code, but the relationship is not one-to-one; some SQLSTATE codes are associated with many MySQL codes; HY000 is a general-purpose SQLSTATE code that is raised for MySQL codes that have no specific associated SQLSTATE code.

The following handler will fire when SQLSTATE 23000 (duplicate key value) is encountered:

 DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET duplicate_key=1;

SQLSTATE or MySQL Error Code?

In theory, using the SQLSTATE codes will make your code more portable to other database platforms and might therefore seem to be the best choice. However, there are a number of reasons to use MySQL error codes rather than SQLSTATE codes when writing MySQL stored programs:

• In reality, it is unlikely that you will move your stored programs to another RDBMS. The Oracle and SQL Server stored program languages are totally incompatible with MySQL. The DB2 stored program language is somewhat compatible (both are based on the SQL:2003 standard). It is very likely, however, that you will use MySQL-specific syntax as you write your application, which will prevent your stored code from being portable.
• Not all MySQL error codes have SQLSTATE equivalents. Although every MySQL error code is associated with some SQLSTATE error code, often it will be a general-purpose SQLSTATE that is not specific (such as HY000). Therefore, you will almost certainly have to code some handlers that refer directly to MySQL error codes. You'll probably find that the advantages of using a consistent handler format will outweigh the theoretical portability advantage of SQLSTATE error codes.

We will, for the most part, use MySQL error codes in this book.

When the MySQL client encounters an error, it will report both the MySQL error code and the associated SQLSTATE code, as in the following output:

 mysql> CALL nosuch_sp( );

 ERROR 1305 (42000): PROCEDURE sqltune.nosuch_sp does not exist

In this case, the MySQL error code is 1305 and the SQLSTATE code is 42000.

Table 6-1 lists some of the error codes you might expect to encounter in a MySQL stored program together with their SQLSTATE equivalents. Note, again, that many MySQL error codes map to the same SQLSTATE code (many map to HY000, for instance), which is why you may wish to sacrifice portability and use MySQL error codes rather than SQLSTATE codesin your error handlers.

Table 6-1. Some common MySQL error codes and SQLSTATE codes

MySQL error code	SQLSTATE code	Error message
1011	HY000	Error on delete of '%s' (errno: %d)
1021	HY000	Disk full (%s); waiting for someone to free some space . . .
1022	23000	Can't write; duplicate key in table '%s'
1027	HY000	'%s' is locked against change
1036	HY000	Table '%s' is read only
1048	23000	Column '%s' cannot be null
1062	23000	Duplicate entry '%s' for key %d
1099	HY000	Table '%s' was locked with a READ lock and can't be updated
1100	HY000	Table '%s' was not locked with LOCK TABLES
1104	42000	The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
1106	42000	Incorrect parameters to procedure '%s'
1114	HY000	The table '%s' is full
1150	HY000	Delayed insert thread couldn't get requested lock for table %s
1165	HY000	INSERT DELAYED can't be used with table '%s' because it is locked with LOCK TABLES
1242	21000	Subquery returns more than 1 row
1263	22004	Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld
1264	22003	Out of range value adjusted for column '%s' at row %ld
1265	1000	Data truncated for column '%s' at row %ld
1312	0A000	SELECT in a stored program must have INTO
1317	70100	Query execution was interrupted
1319	42000	Undefined CONDITION: %s
1325	24000	Cursor is already open
1326	24000	Cursor is not open
1328	HY000	Incorrect number of FETCH variables
1329	2000	No data to FETCH
1336	42000	USE is not allowed in a stored program
1337	42000	Variable or condition declaration after cursor or handler declaration
1338	42000	Cursor declaration after handler declaration
1339	20000	Case not found for CASE statement
1348	HY000	Column '%s' is not updatable
1357	HY000	Can't drop a %s from within another stored routine
1358	HY000	GOTO is not allowed in a stored program handler
1362	HY000	Updating of %s row is not allowed in %s trigger
1363	HY000	There is no %s row in %s trigger

You can find a complete and up-to-date list of error codes in Appendix B of the MySQL reference manual, available online at http://dev.mysql.com/doc/.

6.2.3. Handler Examples

Here are some examples of handler declarations:

• If any error condition arises (other than a NOT FOUND), continue execution after setting l_error=1:

   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
   SET l_error=1;
  

• If any error condition arises (other than a NOT FOUND), exit the current block or stored program after issuing a ROLLBACK statement and issuing an error message:

   DECLARE EXIT HANDLER FOR SQLEXCEPTION
   BEGIN
   ROLLBACK;
   SELECT 'Error occurred - terminating';
   END;
  

• If MySQL error 1062 (duplicate key value) is encountered, continue execution after executing the SELECT statement (which generates a message for the calling program):

   DECLARE CONTINUE HANDER FOR 1062
   SELECT 'Duplicate key in index';
  

• If SQLSTATE 23000 (duplicate key value) is encountered, continue execution after executing the SELECT statement (which generates a message for the calling program):

   DECLARE CONTINUE HANDER FOR SQLSTATE '23000'
   SELECT 'Duplicate key in index';
  

• When a cursor fetch or SQL retrieves no values, continue execution after setting l_done=1:

   DECLARE CONTINUE HANDLER FOR NOT FOUND
   SET l_done=1;
  

• Same as the previous example, except specified using a SQLSTATE variable rather than a named condition:

   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
   SET l_done=1;
  

• Same as the previous two examples, except specified using a MySQL error code variable rather than a named condition or SQLSTATE variable:

   DECLARE CONTINUE HANDLER FOR 1329
   SET l_done=1;
  

6.2.4. Handler Precedence

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. SQLSTATE codes can sometimes map to many MySQL error codes, so they are less specific. General conditions such as SQLEXCEPTION and SQLWARNING are not at all specific. Therefore, a MySQL error code takes precedence over a SQLSTATE exception, which, in turn, takes precedence over a SQLEXCEPTION condition.

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 a SQLSTATE handler, which, in turn, fires before a SQLEXCEPTION handler.

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 creating SQLEXCEPTION handlers until MySQL implements the SIGNAL statement; see "Missing SQL:2003 Features" later in this chapter.

6.2.5. Scope of Condition Handlers

The scope of a handler determines which statements within the stored program are covered by the handler. In essence, the scope of a handler is the same as for a stored program variable: the handler applies to all statements in the block in which it is defined, including any statements in nested blocks. Furthermore, handlers in a stored program also cover statements that execute in any stored program that might be called by the first program, unless that program declares its own handler.

For instance, in Example 6-10 the handler will be invoked when the INSERT statement executes (because it violates a NOT NULL constraint). The handler fires because the INSERT statement is contained within the same block as the handlereven though the INSERT statement is in a nested block.

Example 6-10. Handler scope includes statements within BEGIN-END blocks

DECLARE CONTINUE HANDLER FOR 1048 SELECT 'Attempt to insert a null value';
BEGIN
 INSERT INTO departments (department_name,manager_id,location)
 VALUES (NULL,1,'Wouldn''t you like to know?');
END;

However, in Example 6-11 the handler will not be invokedthe scope of the handler is limited to the nested block, and the INSERT statement occurs outside that block.

Example 6-11. Handlers within a nested block do not cover statements in enclosing blocks

BEGIN
 BEGIN
 DECLARE CONTINUE HANDLER FOR 1216 select
 'Foreign key constraint violated';
 END;
 INSERT INTO departments (department_name,manager_id,location)
 VALUES ('Elbonian HR','Catbert','Catbertia');
END;

Handler scope extends to any stored procedures or functions that are invoked within the handler scope. This means that if one stored program calls another, a handler in the calling program can trap errors that occur in the program that has been called. So, for instance, in Example 6-12, the handler in calling_procedure( ) TRaps the null value exception that occurs in sub_procedure ( ).

Example 6-12. A handler can catch conditions raised in called procedures

CREATE PROCEDURE calling_procedure( )
BEGIN
 DECLARE EXIT HANDLER FOR 1048 SELECT 'Attempt to insert a null value';
 CALL sub_procedure( );
END;

Query OK, 0 rows affected (0.00 sec)

--------------
CREATE PROCEDURE sub_procedure( )
BEGIN
 INSERT INTO departments (department_name,manager_id,location)
 VALUES (NULL,1,'Wouldn''t you like to know');
 SELECT 'Row inserted';

END;

Query OK, 0 rows affected (0.00 sec)

CALL calling_procedure( );

+--------------------------------+
| Attempt to insert a null value |
+--------------------------------+
| Attempt to insert a null value |
+--------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Of course, a handler in a procedure will override the scope of a hander that exists in a calling procedure. Only one handler can ever be activated in response to a specific error condition.