Putting It All Together

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 department'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 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 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_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))
8 BEGIN
9
10 /* START Declare Conditions */
11
12 DECLARE duplicate_key CONDITION FOR 1062;
13 DECLARE foreign_key_violated CONDITION FOR 1216;
14
15 /* END Declare Conditions */
16
17 /* START Declare variables and cursors */
18
19 DECLARE l_manager_id INT;
20
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);
26
27 /* END Declare variables and cursors */
28
29 /* START Declare Exception Handlers */
30
31 DECLARE CONTINUE HANDLER FOR duplicate_key
32 BEGIN
33 SET p_sqlcode=1052;
34 SET p_status_message='Duplicate key error';
35 END;
36
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;
42
43 DECLARE CONTINUE HANDLER FOR not FOUND
44 BEGIN
45 SET p_sqlcode=1329;
46 SET p_status_message='No record found';
47 END;
48
49 /* END Declare Exception Handlers */
50
51 /* START Execution */
52
53 SET p_sqlcode=0;
54 OPEN csr_mgr_id;
55 FETCH csr_mgr_id INTO l_manager_id;
56
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;
68
69 CLOSE csr_mgr_id;
70
71 / * END Execution */
72
73 END

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 NOT FOUND condition, since this is a predefined condition name. Each handler sets an appropriate value for the output status variables p_sqlcode and p_status_message.
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 messageidentifying the statement we were executingand 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 occurredit 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)