How to validate fields in MySQL stored procedures

Question

How to validate fields in MySQL stored procedures

Navigation

#1 by (2 votes)

1

I want you to recommend the most optimal way to validate the fields of a table when making an insert by stored procedures, when I say validate I mean that repeated data is not entered in fields that are defined as unique.

My question arises because if I try to make a repeated insert in a single field the system generates an alert but a bit ugly and in English, what I want is to give a clear message to the client that is what really happens when you try to make an insert of that type. Example (The category already exists.) And not an error Code: 1062. Duplicate entry 'GRAINS' for key 'description_UNIQUE'

mysql

asked by Shooter 05.02.2018 в 22:30

source

1 answer

Block control if else Reverse Queryset on Django models

score 2 · Answer 1

You can use the 'SIGNAL SQLSTATE' statement in the [procedures | functions | triggers] in MySQL.

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Minimo 3 caracteres';

Each type of error is a predefined number by convention, although you can put the number you want, I leave a table of the most frequent error codes:

DECLARE EXIT HANDLER FOR 1048 BEGIN /* El parámetro no puede ser nulo. */ END;
DECLARE EXIT HANDLER FOR 1050 BEGIN /* La tabla ya existe. */ END;
DECLARE EXIT HANDLER FOR 1064 BEGIN /* El parámetro es incorrecto. */ END;
DECLARE EXIT HANDLER FOR 1146 BEGIN /* La tabla no existe. */ END;
DECLARE EXIT HANDLER FOR 1318 BEGIN /* Número de parámetros incorrecto. */ END;

Example:

DELIMITER //
 CREATE PROCEDURE miProcedimiento( arg1 TINYINT(1))
 BEGIN
  IF (arg is null) THEN
   SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El valor es null';
  ELSE
   ..code      
  END IF;
 END //
 DELIMITER ;

This option only works for versions equal to or higher than MySQL 5.5

I pass the link to the MySQL documentation: Here