CODEWITHSUNDEEP
mysqllogging
davide
davide

Reputation: 15

Mysql - get the value of error

I'm working on mysql 5.7.23 and I'm trying to log the error inside my stored procedure I'm facing some difficulties to get the specific error in order to write it in a log table. I would like to do something like this:

BEGIN
DECLARE v_error varchar(50);
DECLARE v_err_no varchar(50) ;
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
set v_err_no= mysql_errno ;
#set v_error= mysql_sqlstate();

call pcd_log ('ERROR', v_err_no ); #my custom procedure to log
end;


insert into temp values (1);#some stuff that might give errors

end

but this is not working, how can I get the value of the error (number or description) and put it in a variable?

Thanks

Upvotes: 1

Views: 52

Answers (1)

P.Salmon
P.Salmon

Reputation: 17665

Read up on https://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html

drop procedure if exists p;

delimiter $$
create procedure p()
BEGIN
DECLARE v_error varchar(50);
DECLARE v_err_no varchar(50) ;
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
    get diagnostics condition 1
     v_err_no = returned_sqlstate;

    #call pcd_log ('ERROR', v_err_no ); #my custom procedure to log

    select v_err_no;
end;


insert into t values ('zzz');#some stuff that might give errors

end $$

delimiter ;

call p();

+----------+
| v_err_no |
+----------+
| 22007    |
+----------+
1 row in set (0.00 sec)

Upvotes: 2

Related Questions