MySQL
  Home arrow MySQL arrow Page 4 - Error Handling Examples
The Best Selling PC Migration Utility.
Dev Shed Forums 
Administration  
Apache  
BrainDump  
DHTML  
Flash  
Java  
JavaScript  
Multimedia  
MySQL  
Oracle  
Perl  
PHP  
Practices  
Python  
Reviews  
Security  
Style-Sheets  
Web Services  
XML  
Zend  
Zope  
Forums Sitemap 
IBM® developerWorks 
Dedicated Servers 
E-Commerce Hosting 
Linux Web Hosting 
Managed Hosting 
Small Business Hosting 
Download TestComplete 
VPS Hosting 
Weekly Newsletter

 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
MYSQL

Error Handling Examples
By: O'Reilly Media
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 3
    2007-09-06

    Table of Contents:
  • Error Handling Examples
  • Handler Precedence
  • Scope of Condition Handlers
  • Named Conditions

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
     
    ADVERTISEMENT

    Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here

    Error Handling Examples - Named Conditions
    (Page 4 of 4 )

    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 orSQLSTATEcode 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 orSQLSTATEcode. 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 orSQLSTATEcode. This means that in an exception handler based on a generic condition such asSQLEXCEPTION, 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 ofSQLCODE(the “vendor”—in this case MySQL—error code) and theSQLSTATEcode. 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 aSQLCODE orSQLSTATEvariable by defining a more comprehensive set of condition handlers that create appropriateSQLCODE 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 aSQLEXCEPTIONhandler, because without the ability to display theSQLSTATE orSQLSTATE, 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 aSQLSTATEorSQLSTATEvariable, avoid creating a general-purposeSQLEXCEPTIONhandler. 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.

     


    DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

       · This article is an excerpt from the book "MySQL Stored Procedure Programming,"...
     

    Buy this book now. This article is excerpted from chapter six of the book MySQL Stored Procedure Programming, written by Guy Harrison and Steven Feuerstein (O'Reilly; ISBN: 0596100892). Check it out today at your favorite bookstore. Buy this book now.

       

    MYSQL ARTICLES

    - MySQL Table Prefix Changer Tool in PHP
    - Using the SIGNAL Statement for Error Handling
    - Error Handling Examples
    - Error Handling
    - Completing a Search Engine with MySQL and PH...
    - Paginating Result Sets for a Search Engine B...
    - Building a Search Engine with MySQL and PHP 5
    - Using Boolean Operators for Full Text and Bo...
    - PHP, MySQL and the PEAR Database
    - Working with PHP and MySQL
    - Getting PHP to Talk to MySQL
    - Creating an RSS Reader: the Reader
    - MySQL Security Overview
    - Creating the Admin Script for a PHP/MySQL Bl...
    - Creating the Blog Script for a PHP/MySQL Blo...

     
    Accelerating Trading Partner Performance
     
    Competing on Analytics
     
    Cost Effective Scaling with Virtualization and Coyote Point Systems
     
    Five Checkpoints to Implementing IP Telephony
     
    Hosted Email Security: Staying Ahead of New Threats
     




    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway