MySQL Stored Procedure Programming

Name

EXC-04: Avoid global SQLEXCEPTION handlers until MySQL implements SIGNAL and SQLCODE features

Synopsis

In the initial 5.0 release of MySQL, it is not possible to access the MySQL error code or SQLSTATE code that caused a handler to be invoked. You also can't raise your own exceptions (the SIGNAL/RESIGNAL statements are not yet supported). What this means is that unless your handler is very specific, you won't know exactly why it was raised. Furthermore, you won't have a reliable mechanism for propagating the exception to the calling program.

Under normal circumstances, it can be very helpful to implement a general-purpose exception handler. This handler would acquire all kinds of handy information about the current state. If, however, you are unable to determine the error that was raised, this kind of general-purpose handler is of little use, and it can even cause a loss of useful information. For instance, in the following example, a general-purpose hander is invoked but cannot report accurately the reason it fired:

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SET  v_status=-1;
        SET  v_message='Some sort of error detected somewhere in the application';
    END;

Given these restrictions, it is best not to create general SQLEXCEPTION handlers. Rather, you should handle only specific, foreseeable errors, and let the calling program handle any unexpected errors.