Home arrow MySQL arrow Page 3 - Using the SIGNAL Statement for Error Handling

Putting It All Together - MySQL

In this conclusion to a three-part series, you will learn about the SIGNAL statement and its uses for creating your own error statements. It is excerpted from chapter six of the book MySQL Stored Procedure Programming, written by Guy Harrison and Steven Feuerstein (O'Reilly; ISBN: 0596100892). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

  1. Using the SIGNAL Statement for Error Handling
  2. Emulating the SIGNAL Statement
  3. Putting It All Together
  4. Handling Stored Program Errors in the Calling Application
  5. Conclusion
By: O'Reilly Media
Rating: starstarstarstarstar / 4
September 13, 2007

print this article



We have now covered in detail the error-handling features of MySQL. We’ll finish up this discussion by offering an example that puts all of these features together. We will take a simple stored procedure that contains no exception handling and apply the concepts from this chapter to ensure that it will not raise any unhandled exceptions for all problems that we can reasonably anticipate.

The example stored procedure creates a newdepartmentsrow. It takes the names of the new department, the manager of the department, and the depar

tment’s location. It retrieves the appropriateemployee_idfrom theemployeestable using the manager’s name. Example 6-21 shows the version of the stored procedure without exception handling.

Example 6-21. Stored procedure without error handling

CREATE PROCEDURE sp_add_department
      (p_department_name        VARCHAR(30),
       p_manager_surname        VARCHAR(30),
       p_manager_firstname      VARCHAR(30),
p_location               VARCHAR(30),
       out p_sqlcode            INT,
       out p_status_message     VARCHAR(100)) 

    DECLARE l_manager_id        INT;
    DECLARE csr_mgr_id cursor for
         SELECT employee_id
           FROM employees
         WHERE surname=UPPER(p_manager_surname)
           AND firstname=UPPER(p_manager_firstname);

    OPEN csr_mgr_id;
    FETCH csr_mgr_id INTO l_manager_id;

    INSERT INTO departments (department_name,manager_id,location)

    CLOSE csr_mgr_id;

This program reflects the typical development process for many of us: we concentrate on implementing the required functionality (the “positive”) and generally pay little attention to (or more likely, want to avoid thinking about) what could possibly go wrong. The end result is a stored program that contains no error handling.

So either before you write the program (ideally) or after the first iteration is done, you should sit down and list out all the errors that might be raised by MySQL when the program is run.

Here are several of the failure points of this stored procedure:

  • If the manager’s name is incorrect, we will fail to find a matching manager in theemployeestable. We will then attempt to insert a NULL value for theMANAGER_IDcolumn, which will violate its NOT NULL constraint.
  • If thelocationargument does not match a location in thelocationstable, the foreign key constraint between the two tables will be violated.
  • If we specify adepartment_namethat already exists, we will violate the unique constraint on thedepartment_name.

The code in Example 6-22 demonstrates these failure scenarios.

Example 6-22. Some of the errors generated by a stored procedure without error handling

mysql> CALL sp_add_department
    ('Optimizer Research','Yan','Bianca','Berkshire',

ERROR 1062 (23000): Duplicate entry 'OPTIMIZER RESEARCH'
for key 2

mysql> CALL sp_add_department
    ('Optimizer Research','Yan','Binca','Berkshire',

ERROR 1048 (23000): Column 'MANAGER_ID' cannot be null

mysql> CALL sp_add_department('Advanced

ERROR 1216 (23000): Cannot add or update a child row: a
foreign key constraint fails

The good news is that MySQL detects these problems and will not allow bad data to be placed into the table. If this stored procedure will be called only by the host language, such as PHP or Java, we could declare ourselves done. If, on the other hand, this program might be called from another MySQL stored program, then we need to handle the errors and return status information so that the calling stored program can take appropriate action. Example 6-23 shows a version of the stored procedure that handles all the errors shown in Example 6-22.

Example 6-23. Stored procedure with error handling

1CREATE PROCEDURE sp_add_department2
2    (p_department_name       VARCHAR(30),
3     p_manager_surname       VARCHAR(30),
4     p_manager_firstname     VARCHAR(30),
5     p_location              VARCHAR(30),
6     OUT p_sqlcode           INT,
7     OUT p_status_message    VARCHAR(100))
10/* START Declare Conditions */
12 DECLARE duplicate_key CONDITION FOR 1062;
13 DECLARE foreign_key_violated CONDITION FOR 1216;
15/* END Declare Conditions */
17/* START Declare variables and cursors */18
19   DECLARE l_manager_id     INT;
21     DECLARE csr_mgr_id CURSOR FOR
22     SELECT employee_id
23       FROM employees
24     WHERE surname=UPPER(p_manager_surname)
25        AND firstname=UPPER(p_manager_firstname);
27 /* END Declare variables and cursors */28
29  /* START Declare Exception Handlers */
32      BEGIN
33         SET p_sqlcode=1052;
34         SET p_status_message='Duplicate key error';
35      END;
37    DECLARE CONTINUE HANDLER FOR foreign_key_violated
38    BEGIN
39      SET p_sqlcode=1216;
40      SET p_status_message='Foreign key violated';
41    END;
44      BEGIN
45       SET p_sqlcode=1329;
46        SET p_status_message='No record found';
47      END;
49  /* END Declare Exception Handlers */
51  /* START Execution */
53   SET p_sqlcode=0;
54   OPEN csr_mgr_id;
55   FETCH csr_mgr_id INTO l_manager_id;
57   IF p_sqlcode<>0 THEN      /* Failed to get manager id*/
58     SET p_status_message=CONCAT(p_status_message,' when
fetching manager id');
59   ELSE
60                         /* Got manager id, we can try and
insert */
61     INSERT INTO departments
62     VALUES(UPPER(p_department_name),l_manager_id,UPPER
63     IF p_sqlcode<>0 THEN/* Failed to insert new department */
64       SET p_status_message=CONCAT(p_status_message,
65                            ' when inserting new department');
66     END IF;
67    END IF;
69   CLOSE csr_mgr_id;
71/ * END Execution */

Let’s go through Example 6-23 and review the error-handling code we have added.



12 and 13

Create condition declarations for duplicate key (1062) and foreign key (1216) errors. As we noted earlier, these declarations are not strictly necessary, but they improve the readability of the condition handlers we will declare later.


Define handlers for each of the exceptions we think might occur. The condition names match those we defined in lines 10 and 11. We didn’t have to create a NOTFOUNDcondition, since this is a predefined condition name. Each handler sets an appropriate value for the output status variables p_sqlcodeand p_status_message.

Line(s) Significance
57 On this line we check the value of thep_sqlcodevariable following our fetch from the cursor that retrieves the manager’semployee_id. Ifp_sqlcodeis not 0, then we know that one of our exception handlers has fired. We add some context information to the message—identifying the statement we were executing—and avoid attempting to execute the insert into thedepartmentstable.
53 Check the value of thep_sqlcodevariable following our insert operation. Again, if the value is nonzero, we know that an error has occurred, and we add some context information to the error message. At line 53, we don’t know what error has occurred—it could be either the foreign key or the unique index constraint. The handler itself controls the error message returned to the user, and so we could add handling for more error conditions by adding additional handlers without having to amend this section of code.

Running the stored procedure from the MySQL command line shows us that all the exceptions are now correctly handled. Example 6-24 shows the output generated by various invalid inputs.

Example 6-24. Output from stored procedure with exception handling

mysql> CALL sp_add_department2('Optimizer

Query OK, 0 rows affected (0.17 sec)

mysql> SELECT @p_sqlcode,@p_status_message

+------------+-----------------------------+ | @p_sqlcode | @p_status_message           |
+------------+-----------------------------+ | 1052       | Duplicate key error when inserting new
department                   |
+------------+-----------------------------+ 1 row in set (0.00

mysql> CALL sp_add_department2('Optimizer

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @p_sqlcode,@p_status_message

+------------+-----------------------------+ | @p_sqlcode | @p_status_message           |
+------------+-----------------------------+ | 1329       | No
record found when fetching manager id                                 | +------------+--------------
---------------+ 1 row in set (0.00 sec)

mysql> call sp_add_department2('Advanced

Query OK, 0 rows affected (0.12 sec)

mysql> SELECT @p_sqlcode,@p_status_message

+------------+-----------------------------+ | @p_sqlcode |
@p_status_message           |
+------------+-----------------------------+ | 1216       |
Foreign key violated when inserting new
department                   | +------------+--------------------
---------+ 1 row in set (0.00 sec)

>>> More MySQL Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: