Error Handlers & Get Diagnostics

from the Artful MySQL Tips List


Before MySQL support for Get Diagnostics came in with version 5.6, writing error handlers for any SQLEXCEPTION or SQLWARNING that might occur was tiresome at best. Now, it's as straightforward as this ...
drop table if exists t;
create table t(i int);

drop procedure if exists p;
delimiter go
create procedure p()
begin
  Declare Exit Handler for SqlException
    Begin
      Get Diagnostics Condition 1 
        @sqlstate = Returned_SqlState,
        @errno = Mysql_Errno, 
        @text = Message_Text;
      Select @sqlstate, @errno, @text;
    End;
  insert into t set i="astring";
end;
go
delimiter ;
call p();
+-----------+--------+------------------------------------------------------------+
| @sqlstate | @errno | @text                                                      |
+-----------+--------+------------------------------------------------------------+
| HY000     |   1366 | Incorrect integer value: 'astring' for column 'i' at row 1 |
+-----------+--------+------------------------------------------------------------+
drop table t;
You can handle SQLWARNINGs similarly.

Last updated 22 Apr 2020


Return to the Artful MySQL Tips page