MySQL Stored Procedure Programming

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.

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. ...