Home arrow MySQL arrow Error Handling Examples

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.

TABLE OF CONTENTS:
  1. Error Handling Examples
  2. Handler Precedence
  3. Scope of Condition Handlers
  4. Named Conditions
By: O'Reilly Media
Rating: starstarstarstarstar / 18
September 06, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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 aNOT FOUND), exit the current block or stored program after issuing aROLLBACK 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 theSELECTstatement (which generates a message for the calling program):

      DECLARE CONTINUE HANDER FOR 1062 
          SELECT 'Duplicate key in index';
  • IfSQLSTATE23000 (duplicate key value) is encountered, continue execution after executing theSELECT 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 settingl_done=1:

      DECLARE CONTINUE HANDLER FOR NOT
    FOUND
          SET l_done=1; 
  • Same as the previous example, except specified using aSQLSTATEvariable 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 orSQLSTATE variable:

      DECLARE CONTINUE HANDLER FOR 1329
          SET l_done=1;



 
 
>>> More MySQL Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: