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 new departments row. It takes the names of the new department, the manager of the department, and the depar

tment’s location. It retrieves the appropriate employee_id from the employees table 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)) 
  MODIFIES SQL DATA
BEGIN

    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)
    VALUES(UPPER(p_department_name),l_manager_id,UPPER
(p_location));

    CLOSE csr_mgr_id;
END$$

This program reflects the typical development process for many of us: we concen trate 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 the employees table. We will then attempt to insert a NULL value for the MANAGER_ID column, which will violate its NOT NULL constraint.
  
• If the location argument does not match a location in the locations table, the foreign key constraint between the two tables will be violated.
  
• If we specify a department_name that already exists, we will violate the unique constraint on the department_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',
@p_sqlcode,@p_status_message)

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

mysql> CALL sp_add_department
    ('Optimizer Research','Yan','Binca','Berkshire',
@p_sqlcode,@p_status_message);

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

mysql> CALL sp_add_department('Advanced
Research','Yan','Bianca','Bercshire',@p_
sqlcode,@p_status_message)

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

1 CREATE PROCEDURE sp_add_department 2
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))
8 BEGIN
9
10 /* START Declare Conditions * /
1 1
12  DECLARE duplicate_key CONDITION FOR 1062 ;
13  DECLARE foreign_key_violated CONDITION FOR 1216;
1 4
15 /* END Declare Conditions * /
1 6
17 /* START Declare variables and cursors * / 1 8
19    DECLARE l_manager_id     INT ;
2 0
21      DECLARE csr_mgr_id CURSOR FO R
22      SELECT employee_id
23        FROM employees
24      WHERE surname=UPPER(p_manager_surname)
25         AND firstname=UPPER(p_manager_firstname);
2 6
27  /* END Declare variables and cursors * / 2 8
29   /* START Declare Exception Handlers * /
3 0
31   DECLARE CONTINUE HANDLER FOR duplicate_ke y
32       BEGIN
33          SET p_sqlcode=1052;
34          SET p_status_message='Duplicate key error';
35       END;
3 6
37     DECLARE CONTINUE HANDLER FOR foreign_key_violate d
38     BEGIN
39       SET p_sqlcode=1216;
40       SET p_status_message='Foreign key violated';
41     END;
4 2
43      DECLARE CONTINUE HANDLER FOR not FOUN D
44       BEGIN
45        SET p_sqlcode=1329;
46         SET p_status_message='No record found';
47       END;
4 8
49   /* END Declare Exception Handlers * /
5 0
51   /* START Execution * /
5 2
53    SET p_sqlcode=0 ;
54    OPEN csr_mgr_id;
55    FETCH csr_mgr_id INTO l_manager_id;
5 6
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
(department_name,manager_id,location)
62      VALUES(UPPER(p_department_name),l_manager_id,UPPER
(p_location));
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;
6 8
69    CLOSE csr_mgr_id ;
7 0
71 / * END Execution * /
7 2
73 EN D

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

Line(s)	Significance
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.
31-48	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 the p_sqlcode variable following our fetch from the cursor that retrieves the manager’s employee_id . If p_sqlcode is 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 the departments table.
53	Check the value of the p_sqlcode variable 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
Research','Yan','Bianca','Berkshire',
@p_sqlcode,@p_status_message)

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
sec)

mysql> CALL sp_add_department2('Optimizer
Research','Yan','Binca','Berkshire',
@p_sqlcode,@p_status_message)

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
Research','Yan','Bianca','Bercshire',
@p_sqlcode,@p_status_message)

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)