Named Conditions - MySQL

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.