I believe there is nothing currently available in MySQL that allows access to the SQLSTATE of the last executed statement within a MySQL stored procedure. This means that when a generic SQLException is raised within a stored procedure it is hard/impossible to derive the exact nature of the error.
Does anybody have a workaround for deriving the SQLSTATE of an error in a MySQL stored procedure that does not involve declaring a handler for every possible SQLSTATE?
For example - imagine that I am trying to return an error_status that goes beyond the generic "SQLException happened somewhere in this BEGIN....END block" in the following:
DELIMITER $$
CREATE PROCEDURE `myProcedure`(OUT o_error_status varchar(50))
MY_BLOCK: BEGIN
DECLARE EXIT handler for 1062 set o_error_status := "Duplicate entry in table";
DECLARE EXIT handler for 1048 set o_error_status := "Trying to populate a non-null column with null value";
-- declare handlers ad nauseum here....
DECLARE EXIT handler for sqlexception set o_error_status:= "Generic SQLException. You'll just have to figure out the SQLSTATE yourself...." ;
-- Procedure logic that might error to follow here...
END MY_BLOCK$$
Any tips?
PS I am running MySQL 5.1.49
GET DIAGNOSTICS is available in 5.6.4
See
http://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html
I believe there is nothing currently available in MySQL that allows access to the SQLSTATE of the last executed statement within a MySQL stored procedure. This means that ... it is hard/impossible to derive the exact nature of the error.
Luckily that is not true.
SHOW ERRORS LIMIT 1 -- for SQL-state > 2
SHOW WARNINGS LIMIT 1 -- for SQL-state 1,2
Will show the last error or warning.
In order to prevent listing each and every error, you can handle a class of SQL-errors like so:
SQLWARNING is shorthand for the class of SQLSTATE values that begin with '01'.
NOT FOUND is shorthand for the class of SQLSTATE values that begin with '02'. This is relevant only within the context of cursors and is used to control what happens when a cursor reaches the end of a data set. If no more rows are available, a No Data condition occurs with SQLSTATE value 02000. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition). An example is shown in Section 12.7.5, “Cursors”. This condition also occurs for SELECT ... INTO var_list statements that retrieve no rows.
SQLEXCEPTION is shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'.
So to handle an exception, you need to only do:
DECLARE EXIT HANDLER FOR SQLSTATE SQLEXCEPTION .....;
Links:
http://dev.mysql.com/doc/refman/5.5/en/signal.html
http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html
I am doing the following workaround: using a SELECT to provocate an error. For example:
SELECT RAISE_ERROR_unable_to_update_basket;
This will result in the following error message (example):
ERROR 1054 (42S22): Unknown column 'RAISE_ERROR_unable_to_update_basket' in 'field list'
I am wrapping my call to a stored procedure in a try { ... } catch { ... } and now can handle this error. This will of course only work for provocating custom error messages from inside your stored procedure and will not handle any SQL or database errors, that might occur (because of duplicate-key entry). In the latter case, you might be able to workaround this using the solution of Johan.
