Mysql manage error messages

Question

I'll go straight to the point.

This is my table

Create Table Beneficios(
IdBeneficio int unsigned primary key auto_increment not null,
Nombre varchar(150)not null,
Estado boolean default true not null,
Descripcion varchar(300)not null,
Tipo enum('Promocio','Descuento','Oferta')
);

and to store values i decide to call a stored_procedure

create procedure registrarBeneficio(idSucursalLey int unsigned, nombreBeneficio varchar(150), estadoBeneficio boolean, descripcionBeneficio varchar(300), tipoBeneficio enum('Promocion', 'Descuento', 'Oferta'))
begin
DECLARE EXIT HANDLER FOR 1265
begin
    SELECT "Beneficio no encontrado" as 'ERROR_NO SQLSTATE';
end;
declare exit handler for sqlexception
begin
    rollback;
    resignal;
end;

start transaction;
if (tipoBeneficio not in('Promocion','Descuento','Oferta')) then
    signal sqlstate '45000' set message_text = 'Beneficio no encontrado', mysql_errno = 1265;
    rollback;
end if;
if exists(select nombre from Beneficios where IdSucursal = idSucursalLey) then
    insert into Beneficios(IdSucursal, Nombre, Estado, Descripcion, Tipo) values (idSucursalLey, nombreBeneficio, estadoBeneficio, descripcionBeneficio, tipoBeneficio);
    commit;
else
    signal sqlstate '45000' set message_text = 'Sucursal no encontrada', mysql_errno = 2000;
    rollback;
end if;
end$$

when i call the stored procedure i get the following message

Error Code: 1265. Data truncated for column 'tipoBeneficio' at row 1

however, what im actually trying to do it's to modify that error message to show something like Beneficio No Encontrado

I check this links with no success into solve my doubt: https://dev.mysql.com/doc/refman/5.5/en/signal.html

http://www.chriscalender.com/tag/declare-exit-handler-for-sqlexception/

MySQL Stored Procedure Error Handling

Please tell me what im doing wrong :(

Answer 1

If you call your procedure with an invalid enum value for your last parameter tipoBeneficio enum('Promocion', 'Descuento', 'Oferta'), that error happens before the procedure starts, and thus before you define any handler.

You could use e.g. varchar(30) for your variable. Then your check inside your procedure will work and can raise your custom error message if the variable has an invalid value.

If you call your procedure with a string that is too long, you will again get a (different) error that you cannot catch in your procedure (but e.g. in your application that called that procedure incorrectly).

Unless this is a simplified procedure, you do not need transactions here (as you have only one statement that changes data, the insert). In fact, you should be careful with transactions inside procedures, as they do not nest. If you start a transaction outside of your procedure, it will also either be committed or rolled back after you executed the procedure. It's usually a good idea to let the caller handle transactions.

Also be aware that signal interrupts code execution, so you do not reach the rollback in your if-blocks. As you have a rollback in your handler too, it has no consequences for most errors, except for the check on tipoBeneficio, since your handler for 1265 has no rollback.