Get the error return value in a variable
One missing feature in our stored procedures and functions is that, if there’s an exception (error), you don’t know the error number or sqlstate or error message. The plan is there, of course, WL#2111 Stored Procedures: Implement GET DIAGNOSTICS but I haven’t ticked the architecture review box, and the best prospect for a volunteer is busy with performance schema work.
However, there is a way to get this in the next MySQL 5.4 (which is called mysql-next on launchpad and which will almost certainly become the September milestone). Use RESIGNAL.
For space reasons I’m only going to illustrate how to do this to find out the SQLSTATE. I think bright souls will realize it’s simple, though tedious, to extend this so you can get the error number too.
I found out what the possible SQLSTATE values are by looking in the manual (of all places, eh?) in section Appendix B.3. Server Error Codes and Messages. No doubt they’ll change, but not often and wildly.
I added 38 DECLARE EXIT HANDLER statements at the start of my procedure, just after the variable declarations. These lines are always the same for any procedure. They’re not executed unless an error happens so I don’t worry about speed. (Update: Roland Bouman’s blog says you should worry, I suppose it depends whether a large fixed parse cost is significant for your workload. Thanks for correcting, Roland.)
USE test
DROP PROCEDURE IF EXISTS p//
CREATE PROCEDURE p ()
BEGIN
DECLARE v INT;
DECLARE EXIT HANDLER FOR SQLSTATE '01000' BEGIN SET @e='01000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '08004' BEGIN SET @e='08004'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '08S01' BEGIN SET @e='08S01'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '0A000' BEGIN SET @e='0A000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '20000' BEGIN SET @e='20000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '21000' BEGIN SET @e='21000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '21S01' BEGIN SET @e='21S01'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '22001' BEGIN SET @e='22001'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '22003' BEGIN SET @e='22003'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '22004' BEGIN SET @e='22004'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '22007' BEGIN SET @e='22007'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '22008' BEGIN SET @e='22008'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '22012' BEGIN SET @e='22012'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '23000' BEGIN SET @e='23000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '25000' BEGIN SET @e='25000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '28000' BEGIN SET @e='28000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '2F003' BEGIN SET @e='2F003'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '2F005' BEGIN SET @e='2F005'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '3D000' BEGIN SET @e='3D000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '40001' BEGIN SET @e='40001'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '42000' BEGIN SET @e='42000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '42S01' BEGIN SET @e='42S01'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '42S02' BEGIN SET @e='42S02'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '42S12' BEGIN SET @e='42S12'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '42S21' BEGIN SET @e='42S21'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '42S22' BEGIN SET @e='42S22'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '70100' BEGIN SET @e='70100'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'HY000' BEGIN SET @e='HY000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'HY001' BEGIN SET @e='HY001'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XA100' BEGIN SET @e='XA100'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XA102' BEGIN SET @e='XA102'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XA106' BEGIN SET @e='XA106'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XAE03' BEGIN SET @e='XAE03'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XAE04' BEGIN SET @e='XAE04'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XAE05' BEGIN SET @e='XAE05'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XAE07' BEGIN SET @e='XAE07'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XAE08' BEGIN SET @e='XAE08'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XAE09' BEGIN SET @e='XAE09'; RESIGNAL; END;
DROP TABLE no_such_table;
END//
The final line, DROP TABLE, is an error I threw in there for illustration. Here’s what happens when I invoke the procedure:
mysql> call p()//
ERROR 1051 (42S02): Unknown table 'no_such_table'
Fine, but where’s the SQLSTATE? Well, if you paid attention, you know it’s in @e.
mysql> select @e//
+-------+
| @e |
+-------+
| 42S02 |
+-------+
1 row in set (0.00 sec)
Remember, you need the next release of 5.4 (”Azalea”) for this. My compliments as always to the architect (Peter Gulutzan) (me), the implementor (Marc Alff), the first code reviewer (Davi Arnaut), the second code reviewer (Sergei Golubchik), and the documenter (Paul DuBois).