How to get exception message on stored procedure in MySQL 5.5

Question

I'm using MySQL 5.5.
To get the exception message on MySQL 5.6 is using GET DIAGNOSTIC function. Is there any similar function in MySQL 5.5 ,.?
The project I'm working is already use MySQL version 5.5.

Answer 1

You could try using SHOW ERROR and SHOW WARNING. To see the last error or warning you could use it as:

SHOW ERRORS LIMIT 1   -- for SQL-state > 2
SHOW WARNINGS LIMIT 1 -- for SQL-state 1,2

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