Emulating the SIGNAL Statement - MySQL

The absence of the SIGNAL statement makes some stored program logic awkward, and in some cases demands that calling applications examine OUT variables, rather than SQL return codes, to check the results of some operations.

There is, however, a way to force an error to occur and pass some diagnostic information back to the calling application. You can, in other words, emulateSIGNALin MySQL 5.0, but we warn you: this solution is not pretty!

Where we would otherwise want to use theSIGNAL statement to return an error to the calling application, we can instead issue a SQL statement that will fail—and fail in such a way that we can embed our error message within the standard error message.

Missing SQL:2003 Features

The best way to do this is to issue a SQL statement that attempts to reference a nonexistent table or column. The name of the nonexistent column or table can include the error message itself, which will be useful because the name of the column or table is included in the error message.

Example 6-18 shows how we can do this. We try to select a nonexistent column name from a table and we make the nonexistent column name comprise our error message. Note that in order for a string to be interpreted as a column name, it must be enclosed by backquotes (these are the quote characters normally found on your keyboard to the left of the 1 key).

Example 6-18. Using a nonexistent column name to force an error to the calling program

CREATE PROCEDURE sp_update_employee_dob2
    (p_employee_id INT, p_dob DATE)
BEGIN

    IF datediff(curdate(),p_dob)<(16*365) THEN
         UPDATE `Error: employee_is_too_young; Employee must be 16 years or older`
            SET x=1;
    ELSE
        UPDATE employees
           SET date_of_birth=p_dob
        WHERE employee_id=p_dob;
    END IF;
END;

If we try to run the stored procedure from the MySQL command line, passing in an invalid date of birth, we get a somewhat informative error message:

  MySQL> CALL sp_update_employee_dob2(2,now());

  ERROR 1054 (42S22): Unknown column 'Error: employee_is_too_young; Employee must be 16
  years or older' in 'field list'

The error code is somewhat garbled, and the error code is not in itself accurate, but at least we have managed to signal to the calling application that the procedure did not execute successfully and we have at least provided some helpful information.

We can somewhat improve the reliability of our error handling—and also prepare for a future in which theSIGNALstatement is implemented—by creating a generic procedure to implement ourSIGNAL workaround. Example 6-19 shows a procedure that accepts an error message and then constructs dynamic SQL that includes that message within an invalid table name error.

Example 6-19. Standard procedure to emulate SIGNAL

CREATE PROCEDURE `my_signal`(in_errortext VARCHAR(255))
BEGIN
   SET @sql=CONCAT('UPDATE `',
            in_errortext,
            '` SET x=1');
   PREPARE my_signal_stmt FROM @sql;
   EXECUTE my_signal_stmt;
   DEALLOCATE PREPARE my_signal_stmt;
END$$

We could now implement our employee date-of-birth update routine to call this routine, as shown in Example 6-20.

Example 6-20. Using our SIGNAL emulation procedure to raise an error

CREATE PROCEDURE sp_update_employee_dob2(p_employee_id INT, p_dob DATE)

BEGIN

    IF datediff(curdate(),p_dob)<(16*365) THEN
         CALL my_signal('Error: employee_is_too_young; Employee must be 16
                         years or older');
    ELSE
        UPDATE employees
           SET date_of_birth=p_dob
        WHERE employee_id=p_employee_id;
    END IF;
END$$

Not only does this routine result in cleaner code that is easier to maintain, but when MySQL does implementSIGNAL, we will only need to update our code in a single procedure.