Stored Program Error Handling

Section 0: Module Objectives or Competencies

Section 1: Errors and Error Handling

An Exception is an abnormal condition that occurs during the execution of a program.

• An Exception Handler is a mechanism to handle runtime errors, so that the normal flow of the application can be maintained.

Syntax:

DECLARE handler_type(Continue | Exit) HANDLER FOR
condition_value[,...] statement

Types of condition value:

• mysql_error_code
• sqlstate_value
• SQLWarning
• SQLException

Here is an excellent introduction to error handling: SQL Errors and Error Handling

When an error is encountered in a stored program, its execution is abruptly stopped and the error is passed to the calling routine.

• The DECLARE ... HANDLER syntax instead allows specific error handlers to be defined to gracefully address problems.
• Handlers, like stored procedures and stored functions, may consist of either a single statement or a BEGIN ... END compound statement block.
• A routine may have zero or more handlers, and a handler may deal with one or more conditions.
• The conditions indicate numeric error code(s) (see also link) and/or the keywords NOT FOUND, SQLWARNING, SQLEXCEPTION, matching multiple types of errors by category.
• Handlers must be defined at the beginning of a routine.

There are two types of handlers:

• DECLARE EXIT HANDLER executes the handler and stops execution of the BEGIN ... END block in which the handler is declared. This is true even if the error occurs in an inner block.
• DECLARE CONTINUE HANDLER executes the handler and resumes execution of the current routine.

Generating an Error

To generate an error within a stored program, use SIGNAL.

• A generic error message can be created with SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Your error message here'; as seen in the upcoming example.

Section 2: Rollback Handler Example

Here is the AddPubs procedure again.

• This stored procedure performs a ROLLBACK operation in the event of an error because the "statement" portion of the error handler is "ROLLBACK".
• We introduce an error by attempting to insert pub_id '0736' again.

delimiter //
DROP PROCEDURE IF EXISTS AddPubs //
CREATE PROCEDURE AddPubs ()
BEGIN
DECLARE EXIT HANDLER -- handler type
FOR SQLEXCEPTION -- condition value(s)
BEGIN -- begin body of multi-statement handler type
ROLLBACK; -- statement
IF (ROW_COUNT() = 0) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unable to insert publishers.';
END IF;
END; -- end body of multi-statement handler type
START TRANSACTION;
INSERT INTO publishers (pub_id, pub_name) VALUES ('0234', 'Prospect Press');
INSERT INTO publishers (pub_id, pub_name) VALUES ('0736', 'Angelfire Publishing');
COMMIT;
END //
delimiter ;

To test the procedure, invoke it with the statement CALL AddPubs();

• The procedure attempts and fails to add a duplicate publisher (pub_id = '0736').
• The system generates an error since a duplicate primary key is an integrity constraint violation.
• The error handler automatically rolls back the transaction, and neither '0234' nor '0736' are inserted.

SELECT * FROM publishers; displays the table contents, confirming that the new publisher (pub_id = '0234') does not exist because the entire transaction was rolled back:

            +--------+-----------------------+------------+-------+---------+
            | pub_id | pub_name              | city       | state | country |
            +--------+-----------------------+------------+-------+---------+
            |    736 | New Moon Books        | Boston     | MA    | USA     |
            |    877 | Binnet & Hardley      | Washington | DC    | USA     |
            |   1389 | Algodata Infosystems  | Berkeley   | CA    | USA     |
            |   1622 | Five Lakes Publishing | Chicago    | IL    | USA     |
            |   1756 | Ramona Publishers     | Dallas     | TX    | USA     |
            |   9901 | GGG&G                 | Mnchen     | NULL  | Germany |
            |   9952 | Scootney Books        | New York   | NY    | USA     |
            |   9999 | Lucerne Publishing    | Paris      | NULL  | France  |
            +--------+-----------------------+------------+-------+---------+
            

An OUT parameter indicating the success or failure of the transaction could be added to the stored procedure if it were necessary for the caller to have additional information.

Section 3: Additional Sources for you to Review

• MySQL 8.0 Reference Manual: 13.6.7.2 DECLARE ... HANDLER Statement
• MySQL Error Handling in Stored Procedures
• How to try and catch errors in MySQL stored procedures.
• Exception Handling
• Stored Procedures and Stored Functions in MySQL
• How to handle exception in stored procedure in mysql?