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)) 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 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
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)) 8BEGIN 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 73END
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 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
+------------+-----------------------------+ | @p_sqlcode | @p_status_message | +------------+-----------------------------+ | 1052 | Duplicate key error when inserting new department | +------------+-----------------------------+ 1 row in set (0.00 sec)
+------------+-----------------------------+ | @p_sqlcode | @p_status_message | +------------+-----------------------------+ | 1329 | No record found when fetching manager id | +------------+-------------- ---------------+ 1 row in set (0.00 sec)
MySQL 
/ 4 
