Re: How to catch SQL Exception Error number in MySQL 5.6

[Peter Brawley]

Until you upgrade to 8.0, assuming a target table t( somecol t int ), an errorlog table ( ts timestamp, msg text ), and a desire for handling bad key errors, here's one way ...

delimiter go
CREATE PROCEDURE errdemo( pvalue INT, OUT pResult INT )
BEGIN
  DECLARE bad_key CONDITION FOR SQLSTATE '23000';
  DECLARE EXIT HANDLER FOR bad_key
    BEGIN
      INSERT INTO errlog( dt, msg )
        VALUES( Now(), Concat('Errdemo:Insert on t failed; invalid key.') );
      SET presult=-1;
      ROLLBACK;
    END;
  START TRANSACTION;
    INSERT INTO t VALUES( pvalue, NULL ) ;
    SET pResult=Row_Count();
  COMMIT;
END;
go
delimiter ;

Edited 1 time(s). Last edit at 12/01/2019 02:32PM by Peter Brawley.