Error Handling Examples

In this second article in a three-part series, you will learn more about errors and 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.

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 SQLEXCEPTIO N
      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 106 2 
          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
    FOUN D
          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 132 9
          SET l_done=1;

{mospagebreak title=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 possi ble, 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.

{mospagebreak title=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 handler—even 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 invoked—the scope of the handler is limited to the nested block, and the INSERT statement occurs outside that block.

Condition Handlers

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.

{mospagebreak title=Named Conditions}

So far, our examples have used conditions based on MySQL error codes, SQLSTATE codes, or predefined named conditions (SQLEXCEPTION, SQLWARNING, NOT FOUND). These handlers do the job required, but they do not result in particularly readable code, since they rely on the hardcoding of literal error numbers. Unless you memorize all or most of the MySQL error codes and SQLSTATE codes (and expect everyone maintaining your code to do the same), you are going to have to refer to a manual to understand exactly what error a handler is trying to catch.

You can improve the readability of your handlers by defining a condition declaration, which associates a MySQL error code or SQLSTATE code with a meaningful name that you can then use in your handler declarations. The syntax for a condition declaration is:

  DECLARE condition_name CONDITION FOR {SQLSTATE sqlstate_code | MySQL_error_code};

Once we have declared our condition name, we can use it in our code instead of a MySQL error code or SQLSTATE code. So instead of the following declaration:

  DECLARE CONTINUE HANDLER FOR 1216 MySQL_statements;

we could use the following more readable declaration:

  DECLARE foreign_key_error CONDITION FOR 1216;

  DECLARE CONTINUE HANDLER FOR foreign_key_error MySQL_statements;

Create named conditions using condition declarations, and use these named conditions in your handlers to improve the readability and maintainability of your stored program code.

Missing SQL:2003 Features

The SQL:2003 specification includes a few useful features that—at the time of writing—are not currently implemented in the MySQL stored program language. The absence of these features certainly limits your ability to handle unexpected conditions, but we expect that they will be implemented in MySQL server 5.2. Specifically:

  • There is no way to examine the current MySQL error code or SQLSTATE code. This means that in an exception handler based on a generic condition such as SQLEXCEPTION , you have no way of knowing what error just occurred.
  • You cannot raise an exception of your own to indicate an application-specific error or to re-signal an exception after first catching the exception and examining its context.

We’ll describe these situations in the following sections and suggest ways to deal with them.

Missing SQL:2003 Features

Directly Accessing SQLCODE or SQLSTATE

Implementing a general-purpose exception handler would be a good practice, except that if you cannot reveal the reason why the exception occurred, you make debugging your stored programs difficult or impossible. For instance, consider Example 6-13.

Example 6-13. General-purpose—but mostly useless—condition handler

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
   
SET l_status=-1;
   
Set l_message=‘Some sort of error detected somewhere in the application’; END;

Receiving an error message like this is not much help—in fact, there is almost nothing more frustrating than receiving such an error message when trying to debug an application. Obscuring the actual cause of the error makes the condition handler worse than useless in most circumstances.

The SQL:2003 specification allows for direct access to the values of SQLCODE (the “vendor”—in this case MySQL—error code) and the SQLSTATE code. If we had access to these codes, we could produce a far more helpful message such as shown in Example 6-14.

Example 6-14. A more useful—but not supported—form of condition handler

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
   
SET l_status=-1;
   
SET l_message=’Error ‘||sqlcode||’ encountered';
END;

We can partially emulate the existence of a SQLCODE or SQLSTATE variable by defining a more comprehensive set of condition handlers that create appropriate SQLCODE variables when they are fired. The general approach would look like Example 6-15.

Example 6-15. Using multiple condition handlers to expose the actual error code

DECLARE sqlcode INT DEFAULT 0;
DECLARE status_message VARCHAR(50);

DECLARE CONTINUE HANDLER FOR duplicate_key
BEGIN
     
SET sqlcode=1052;
     
SET status_message=’Duplicate key error';
END;

DECLARE CONTINUE HANDLER FOR foreign_key_violated
BEGIN
    
SET sqlcode=1216;
     SET status_message=’Foreign key violated';
END;

DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
    
SET sqlcode=1329;
    
SET status_message=’No record found'; END;

In most circumstances, it is best not to define a SQLEXCEPTION handler, because without the ability to display the SQLSTATE or SQLSTATE , it is better to let the exception occur and allow the calling application to have full access to the error codes and messages concerned.

Until MySQL implements a SQLSTATE or SQLSTATE variable, avoid creating a general-purpose SQLEXCEPTION handler. Instead, create handlers for individual error conditions that generate appropriate messages and status codes.

Please check back next week for the conclusion to this article.

 

[gp-comments width="770" linklove="off" ]

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort