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.

Part I: Stored Programming Fundamentals

Introduction to MySQL Stored Programs

MySQL Stored Programming Tutorial

Language Fundamentals

Blocks, Conditional Statements, and Iterative Programming

Using SQL in Stored Programming

Error Handling

Part II: Stored Program Construction

Creating and Maintaining Stored Programs

Transaction Management

MySQL Built-in Functions

Stored Functions

Triggers

Part III: Using MySQL Stored Programs in Applications

Using MySQL Stored Programs in Applications

Using MySQL Stored Programs with PHP

Using MySQL Stored Programs with Java

Using MySQL Stored Programs with Perl

Using MySQL Stored Programs with Python

Using MySQL Stored Programs with .NET

Part IV: Optimizing Stored Programs

Stored Program Security

Tuning Stored Programs and Their SQL

Basic SQL Tuning

Advanced SQL Tuning

Optimizing Stored Program Code

Best Practices in MySQL Stored Program Development



MySQL Stored Procedure Programming
MySQL Stored Procedure Programming
ISBN: 0596100892
EAN: 2147483647
Year: 2004
Pages: 208

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net