Vous êtes nouveau sur Developpez.com ? Créez votre compte ou connectez-vous afin de pouvoir participer !

Vous devez avoir un compte Developpez.com et être connecté pour pouvoir participer aux discussions.

Vous n'avez pas encore de compte Developpez.com ? Créez-en un en quelques instants, c'est entièrement gratuit !

Si vous disposez déjà d'un compte et qu'il est bien activé, connectez-vous à l'aide du formulaire ci-dessous.

Identifiez-vous

Identifiant
Mot de passe

Mot de passe oublié ?

Créer un compte

L'inscription est gratuite et ne vous prendra que quelques instants !

Je m'inscris !

Apprendre à programmer sa propre gestion d'erreurs dans les procédures SQL

Un billet de CinePhil

Le 2 novembre 2018, par CinePhil

0PARTAGES

Comme il y a des contraintes dans la base de données (clés étrangères, unicité...), l'exécution d'une procédure SQL peut entraîner la survenue d'erreurs (générées par le SGBD à cause de ces contraintes). Il est intéressant de capturer ces erreurs pour renvoyer au programme externe qui lance la procédure un code et un message d'erreur plus clairs.
On peut aussi programmer ses propres exceptions selon certaines conditions.

Nous allons étudier ça dans une procédure de création d'un utilisateur.

1. Le modèle de données
Règle de gestion :
R1 : un utilisateur est une personne et une personne peut être un utilisateur.

MCD (fait avec JMerise) :

Tables :
te_personne_prs (prs_id, prs_nom, prs_prenom, prs_date_naissance)
th_utilisateur_uti (uti_id_personne, uti_login, uti_mot_passe, uti_date_creation, uti_valide)

Remarque : j'utilise un système de nommage des tables, colonnes, contraintes et autres éléments de la BDD inspiré de celui de SQLPro.

Code de génération des tables :
- Pour MySQL / Maria DB

Code SQL :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE TABLE te_personne_prs  
( 
	prs_id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Identifiant de la personne', 
	prs_nom varchar(40) NOT NULL COMMENT 'Nom usuel de la personne', 
	prs_prenom varchar(40) NOT NULL COMMENT 'Prénom usuel de la personne', 
	prs_date_naissance date NOT NULL COMMENT 'Date de naissance de la personne', 
	PRIMARY KEY (prs_id), 
	UNIQUE KEY xu_prs_nom_prenom_naissance (prs_nom, prs_prenom, prs_date_naissance), 
	KEY `x_prs_nom` (`prs_nom`) 
)  
ENGINE=InnoDB  
DEFAULT CHARSET=utf8mb4  
COMMENT='Personnes physiques'; 
  
CREATE TABLE th_utilisateur_uti  
( 
	uti_id_personne int(11) NOT NULL COMMENT 'Identifiant de la personne', 
	uti_login varchar(40) NOT NULL COMMENT 'Nom d''utilisateur', 
	uti_mot_passe varchar(40) NOT NULL COMMENT 'Mot de passe de l’utilisateur', 
	uti_date_creation date NOT NULL COMMENT 'Date de création de l’utilisateur', 
	uti_valide tinyint(1) NOT NULL DEFAULT 0 COMMENT 'L''utilisateur est-il valide ?', 
	PRIMARY KEY (uti_id_personne), 
	UNIQUE KEY xu_uti_login (uti_login), 
	CONSTRAINT fk_uti_id_personne  
		FOREIGN KEY (uti_id_personne)  
		REFERENCES te_personne_prs (prs_id)  
		ON DELETE CASCADE  
		ON UPDATE CASCADE 
)  
ENGINE=InnoDB  
DEFAULT CHARSET=utf8mb4  
COMMENT='Utilisateurs de l’application';

Remarque : Avec MySQL / MariaDB, on ne peut pas spécifier CURRENT_DATE en valeur par défaut pour une colonne de type DATE. Avec PostgreSQL, c'est accepté (voir ci-dessous).

- Pour PostgreSQL :

Code SQL :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- Table: public.te_personne_prs 
CREATE TABLE public.te_personne_prs 
( 
	prs_id serial NOT NULL, -- Identifiant de la personne 
	prs_nom character varying(40) NOT NULL, -- Nom usuel de la personne 
	prs_prenom character varying(40) NOT NULL DEFAULT '', -- Prénom usuel de la personne 
	prs_date_naissance date NOT NULL, 
	CONSTRAINT pk_prs_id PRIMARY KEY (prs_id), 
	CONSTRAINT xu_prs_nom_prenom_naissance UNIQUE (prs_nom, prs_prenom, prs_date_naissance) 
) 
COMMENT ON TABLE public.te_personne_prs 
	IS 'Personnes physiques'; 
COMMENT ON COLUMN public.te_personne_prs.prs_id IS 'Identifiant de la personne'; 
COMMENT ON COLUMN public.te_personne_prs.prs_nom IS 'Nom usuel de la personne'; 
COMMENT ON COLUMN public.te_personne_prs.prs_prenom IS 'Prénom usuel de la personne'; 
  
CREATE INDEX x_prs_nom 
	ON public.te_personne_prs 
	USING btree 
	(prs_nom); 
  
-- Table: public.th_utilisateur_uti 
CREATE TABLE public.th_utilisateur_uti 
( 
	uti_id_personne integer NOT NULL, -- Identifiant de la personne 
	uti_login character varying(40) NOT NULL, -- Nom d'utilisateur 
	uti_mot_passe character varying(40) NOT NULL, -- Mot de passe de l'utilisateur 
	uti_date_creation date NOT NULL DEFAULT CURRENT_DATE, -- Date de création de l'utilisateur 
	uti_valide boolean NOT NULL DEFAULT false, -- L'utilisateur est-il valide ? 
	CONSTRAINT pk_uti_id_personne PRIMARY KEY (uti_id_personne), 
	CONSTRAINT fk_uti_id_personne FOREIGN KEY (uti_id_personne) 
		REFERENCES public.te_personne_prs (prs_id) MATCH SIMPLE 
		ON UPDATE CASCADE ON DELETE CASCADE, -- Clé étrangère référençant te_personne_prs 
	CONSTRAINT xu_uti_login UNIQUE (uti_login) 
); 
COMMENT ON TABLE public.th_utilisateur_uti 
	IS 'Utilisateurs de l’application'; 
COMMENT ON COLUMN public.th_utilisateur_uti.uti_id_personne IS 'Identifiant de la personne'; 
COMMENT ON COLUMN public.th_utilisateur_uti.uti_login IS 'Nom d''utilisateur'; 
COMMENT ON COLUMN public.th_utilisateur_uti.uti_mot_passe IS 'Mot de passe de l’utilisateur'; 
COMMENT ON COLUMN public.th_utilisateur_uti.uti_date_creation IS 'Date de création de l’utilisateur'; 
COMMENT ON COLUMN public.th_utilisateur_uti.uti_valide IS 'L''utilisateur est-il valide ?'; 
  
COMMENT ON CONSTRAINT fk_uti_id_personne ON public.th_utilisateur_uti IS 'Clé étrangère référençant te_personne_prs';

2. Procédure de création d'un utilisateur
Comme th_utilisateur_uti hérite de te_personne_prs, la clé primaire de th_utilisateur_uti est également une clé étrangère référençant te_personne_prs.
Lors de la création d'un utilisateur, on doit normalement fournir à la requête d'insertion l'identifiant de la personne. Il est alors intéressant de créer directement l'utilisateur via une procédure qui créera en même temps la personne, récupérera l'identifiant généré et donnera la bonne clé étrangère à l'insertion dans la table th_utilisateur_uti.

Voici la procédure simple, sans gestion des erreurs et sans programmation d'exceptions :
- Pour MySQL / MariaDB

Code SQL :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
DELIMITER // 
CREATE OR REPLACE PROCEDURE pi_ajout_utilisateur 
( 
	INOUT id_personne INTEGER, -- Identifiant de la personne 
	-- Données pour la création éventuelle de la personne 
	IN nom_usuel VARCHAR(40), -- Nom usuel de la personne 
	IN prenom_usuel VARCHAR(40), -- Prénom usuel de la personne 
	IN date_naissance DATE, -- Date de naissance de la personne 
	-- Données pour la création de l'utilisateur 
	IN login VARCHAR(40), -- Nom d'utilisateur 
	IN mot_passe VARCHAR(40) -- Mot de passe de l'utilisateur (on suppose qu'il est crypté par l'application) 
	-- Remarque : la date de création et la validité de l'utilisateur sont automatiques lors de sa création 
) 
BEGIN 
	IF id_personne IS NULL THEN 
		-- Identifiant de la personne non fourni : on crée d'abord la personne 
		INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_date_naissance) 
		VALUES (nom_usuel, prenom_usuel, date_naissance); 
  
		-- Récupération du prs_id généré par l'auto-incrémentation 
		SET id_personne = LAST_INSERT_ID(); 
	END IF; 
  
	-- Création de l'utilisateur 
	INSERT INTO th_utilisateur_uti (uti_id_personne, uti_login, uti_mot_passe, uti_date_creation, uti_valide) 
	VALUES (id_personne, login, mot_passe, CURRENT_DATE, FALSE); 
END;//

Pour PostgreSQL

Code SQL :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE OR REPLACE FUNCTION pi_ajout_utilisateur 
( 
	INOUT id_personne BIGINT, -- Identifiant de la personne 
	-- Données pour la création éventuelle de la personne 
	IN nom_usuel VARCHAR(40), -- Nom usuel de la personne 
	IN prenom_usuel VARCHAR(40), -- Prénom usuel de la personne 
	IN date_naissance DATE, -- Date de naissance de la personne 
	-- Données pour la création de l'utilisateur 
	IN login VARCHAR(40), -- Nom d'utilisateur 
	IN mot_passe VARCHAR(40) -- Mot de passe de l'utilisateur (on suppose qu'il est crypté par l'application) 
	-- Remarque : la date de création et la validité de l'utilisateur sont automatiques lors de sa création 
) 
AS 
$BODY$ 
BEGIN 
	IF id_personne IS NULL THEN 
		-- Identifiant de la personne non fourni : on crée d'abord la personne 
		INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_date_naissance) 
		VALUES (nom_usuel, prenom_usuel, date_naissance); 
  
		-- Récupération du prs_id généré par l'auto-incrémentation 
		SELECT CURRVAL('te_personne_prs_prs_id_seq') INTO id_personne; 
	END IF; 
  
	-- Création de l'utilisateur 
	INSERT INTO th_utilisateur_uti (uti_id_personne, uti_login, uti_mot_passe, uti_date_creation, uti_valide) 
	VALUES (id_personne, login, mot_passe, CURRENT_DATE, FALSE); 
END; 
$BODY$ 
LANGUAGE plpgsql VOLATILE;

Remarque : la table te_personne_prs a été créée dans PGAdmin III avec une colonne prs_id de type SERIAL. Ceci a entraîné la création automatique de la séquence et l'adoption du type INTEGER pour la colonne. Cependant, la fonction CURRVAL('te_personne_prs_prs_id_seq') renvoie un BIGINT. Il faut donc spécifier en entrée dans la procédure un BIGINT au lieu d'un INTEGER sinon le système ne reconnait pas la procédure si on utilise la fonction CURRVAL('te_personne_prs_prs_id_seq') pour lui passer la valeur de l'identifiant de la personne, comme dans le second test pour PostgreSQL ci-dessous.

Voyons maintenant si ces procédures fonctionnent...
Chez MySQL / MariaDB :

Code SQL :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Cas normal : Données de la personne complètes et correctes 
SET @id_personne = NULL; 
CALL pi_ajout_utilisateur (@id_personne, 'Leménager', 'Philippe', '1963-07-24', 'plemenager', 'Mon_Pass'); 
SELECT @id_personne; 
-- > @id_personne = 1 => OK  
  
-- Cas normal : Insertion d'une personne puis utilisation de son identifiant pour l'appel de la procédure 
INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_date_naissance) 
VALUES ('Bellucci', 'Monica', '1964-09-30'); 
SET @id_personne = LAST_INSERT_ID(); 
CALL pi_ajout_utilisateur (@id_personne, NULL, NULL, NULL, 'mbellucci', 'Son_Pass'); 
SELECT @id_personne; 
-- > @id_personne = 2 => OK

Chez PostgreSQL :

Code SQL :

Sélectionner tout

1
2
3
4
5
6
7
8
9
-- Cas normal : Données de la personne complètes et correctes 
SELECT pi_ajout_utilisateur (NULL, 'Leménager', 'Philippe', '1963-07-24', 'plemenager', 'Mon_Pass'); 
-- > pi_ajout_utilisateur = 1 => OK 
  
-- Cas normal : Insertion d'une personne puis utilisation de son identifiant pour l'appel de la procédure 
INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_date_naissance)  
VALUES ('Bellucci', 'Monica', '1964-09-30'); 
SELECT pi_ajout_utilisateur (CURRVAL('te_personne_prs_prs_id_seq'), NULL, NULL, NULL, 'mbellucci', 'Son_Pass'); 
-- > pi_ajout_utilisateur = 2 => OK

Regardez dans les tables ; les utilisateurs ont bien été créés.
Jusque là, tout va bien !

3. Exceptions personnalisées
En l'état, je peux très bien enregistrer comme date de naissance '1863-07-24' (record absolu de longévité ; appelons vite le Guiness Book !

) ou bien '2018-07-24' (précoce le gamin !

).
Il faut donc contrôler un minimum la cohérence de la date de naissance qui est passée à la procédure... et interrompre le processus en renvoyant un message d'erreur à l'application qui a lancé la procédure en lui passant de mauvaises données.

Remarque : on pourrait utiliser une contrainte CHECK et capturer le message d'erreur renvoyé par le SGBD, mais comme MySQL / MariaDB ne l'a toujours pas implémenté

, on va générer une exception dans la procédure pour nos deux SGBD.

Nous allons considérer ci-après que notre utilisateur ne peut pas être âgé de plus de 100 ans ni de moins de 10 ans.

Chez MySQL / MariaDB, on génère l'exception à l'aide de l'instruction SIGNAL.
Pour une exception personnalisée, il est de coutume d'utiliser le SQLSTATE '45000'.
On peut ensuite donner au SIGNAL un "numéro d'erreur" MYSQL_ERRNO qui doit être un nombre à 5 chiffres. Pour un numéro personnalisé , il vaut mieux éviter d'utiliser les numéros standards de MySQL et donc commencer la numérotation des exceptions personnalisées à partir de 10001.
Enfin, Le message personnalisé est donné par la variable MESSAGE_TEXT et ne doit pas dépasser 64 caractères sous peine d'être tronqué.

Voilà ce que ça donne dans le code de notre procédure MySQL /MariaDB (voir partie du code -- Contrôle de la date de naissance) :

Code SQL :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
DELIMITER // 
CREATE OR REPLACE PROCEDURE pi_ajout_utilisateur 
( 
	INOUT id_personne INTEGER, -- Identifiant de la personne 
	-- Données pour la création éventuelle de la personne 
	IN nom_usuel VARCHAR(40), -- Nom usuel de la personne 
	IN prenom_usuel VARCHAR(40), -- Prénom usuel de la personne 
	IN date_naissance DATE, -- Date de naissance de la personne 
	-- Données pour la création de l'utilisateur 
	IN login VARCHAR(40), -- Nom d'utilisateur 
	IN mot_passe VARCHAR(40) -- Mot de passe de l'utilisateur (on suppose qu'il est crypté par l'application) 
	-- Remarque : la date de création et la validité de l'utilisateur sont automatiques lors de sa création 
) 
BEGIN 
	-- Contrôle de la date de naissance 
	IF date_naissance NOT BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 100 YEAR) AND DATE_SUB(CURRENT_DATE, INTERVAL 10 YEAR) THEN 
		-- Si la date de naissance n'est pas comprise entre aujourd'hui moins 100 ans et aujourd'hui moins 10 ans => erreur ! 
		SIGNAL SQLSTATE '45000' 
		SET MYSQL_ERRNO = 10001, MESSAGE_TEXT = 'Date de naissance incorrecte'; 
	END IF; 
  
	-- Contrôle de l'id_personne et ajout éventuel de la personne 
	IF id_personne IS NULL THEN 
		-- Identifiant de la personne non fourni : on crée d'abord la personne 
		INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_date_naissance) 
		VALUES (nom_usuel, prenom_usuel, date_naissance); 
  
		-- Récupération du prs_id généré par l'auto-incrémentation 
		SET id_personne = LAST_INSERT_ID(); 
	END IF; 
  
	-- Création de l'utilisateur 
	INSERT INTO th_utilisateur_uti (uti_id_personne, uti_login, uti_mot_passe, uti_date_creation, uti_valide) 
	VALUES (id_personne, login, mot_passe, CURRENT_DATE, FALSE); 
END;//

Chez PostgreSQL, une erreur ou exception se déclenche avec la fonction RAISE.
Comme on veut déclencher une exception personnalisée, on écrit simplement RAISE EXCEPTION 'le message exception'.
PostgreSQL est plus permissif sur les codes d'erreurs personnalisés puisqu'ils sont alphanumériques, mais toujours limités à 5 caractères. On ajoute à ce qui précède USING ERRCODE = 'code1'. On peut ainsi se créer ses propres familles de codes, mais, là encore, je recommande de ne pas utiliser ceux de PostgreSQL pour éviter toute confusion entre une erreur renvoyée par PostgreSQL et qui n'aurait pas été prévue d'être capturée par le programme, et une exception personnalisée spécialement programmée pour les besoins de l'application.

Dans le code de notre procédure ci-dessous, j'ai décidé de commencer les codes d'erreur par DVP puis de les numéroter de 01 à potentiellement 99.

Code SQL :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
CREATE OR REPLACE FUNCTION pi_ajout_utilisateur 
( 
	INOUT id_personne BIGINT, -- Identifiant de la personne 
	-- Données pour la création éventuelle de la personne 
	IN nom_usuel VARCHAR(40), -- Nom usuel de la personne 
	IN prenom_usuel VARCHAR(40), -- Prénom usuel de la personne 
	IN date_naissance DATE, -- Date de naissance de la personne 
	-- Données pour la création de l'utilisateur 
	IN login VARCHAR(40), -- Nom d'utilisateur 
	IN mot_passe VARCHAR(40) -- Mot de passe de l'utilisateur (on suppose qu'il est crypté par l'application) 
	-- Remarque : la date de création et la validité de l'utilisateur sont automatiques lors de sa création 
) 
AS 
$BODY$ 
BEGIN 
	-- Vérification de la date de naissance	 
	IF date_naissance NOT BETWEEN CURRENT_DATE - INTERVAL '100' YEAR AND CURRENT_DATE - INTERVAL '10' YEAR THEN 
		-- Si la date de naissance n'est pas comprise entre il y a 100 ans et il y a 10 ans => Erreur 
		RAISE EXCEPTION 'Date de naissance incorrecte' 
		USING ERRCODE = 'DVP01'; 
	END IF;	 
  
	IF id_personne IS NULL THEN 
		-- Identifiant de la personne non fourni : on crée d'abord la personne 
		INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_date_naissance) 
		VALUES (nom_usuel, prenom_usuel, date_naissance); 
  
		-- Récupération du prs_id généré par l'auto-incrémentation 
		SELECT CURRVAL('te_personne_prs_prs_id_seq') INTO id_personne; 
	END IF; 
  
	-- Création de l'utilisateur 
	INSERT INTO th_utilisateur_uti (uti_id_personne, uti_login, uti_mot_passe, uti_date_creation, uti_valide) 
	VALUES (id_personne, login, mot_passe, CURRENT_DATE, FALSE); 
END; 
$BODY$ 
LANGUAGE plpgsql VOLATILE;

Essayons maintenant nos procédures modifiées en leur donnant de mauvaises dates de naissance...
Avec MySQL / MariaDB :

Code SQL :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
-- Cas erreur : date de naissance trop ancienne (> 100 ans) 
SET @id_personne = NULL; 
CALL pi_ajout_utilisateur (@id_personne, 'Chaplin', 'Charlie', '1899-04-16', 'cchaplin', 'Son_Pass'); 
SELECT @id_personne; 
-- > #10001 - Date de naissance incorrecte => OK 
  
-- Cas erreur : date de naissance trop récente (< 10 ans) 
SET @id_personne = NULL; 
CALL pi_ajout_utilisateur (@id_personne, 'Doll', 'Baby', '2018-05-15', 'bdoll', 'Son_Pass'); 
SELECT @id_personne; 
-- > #10001 - Date de naissance incorrecte => OK

Nous avons bien notre message et notre code d'erreur personnalisés.
Si vous regardez le contenu de la table te_personne_prs, vous verrez que l'envoi du SIGNAL a empêché la création de ces deux personnes. Idem, elles ne sont pas présentes non plus dans la table th_utilisateur_uti.

Passons sous PostgreSQL :

Code SQL :

Sélectionner tout

1
2
3
4
5
6
7
-- Cas erreur : date de naissance trop ancienne (> 100 ans) 
SELECT pi_ajout_utilisateur (NULL, 'Chaplin', 'Charlie', '1899-04-16', 'cchaplin', 'Son_Pass'); 
-- > ERROR: Date de naissance incorrecte / État SQL :DVP01 => OK 
  
-- Cas erreur : date de naissance trop récente (< 10 ans) 
SELECT pi_ajout_utilisateur (NULL, 'Doll', 'Baby', '2018-05-15', 'bdoll', 'Son_Pass'); 
-- > ERROR: Date de naissance incorrecte / État SQL :DVP01 => OK

Là aussi, nous avons notre message personnalisé et les utilisateurs n'ont pas été créés dans la base de données.

4. Captation des erreurs du SGBD
Dans la table te_personne_prs, j'ai créé une contrainte d'unicité sur le triplet {nom, prénom, date de naissance} de manière à ne pas enregistrer deux fois la même personne (le cas de deux personnes différentes ayant les mêmes nom, prénom et date de naissance est très improbable).
Dans la table th_utilisateur_uti, j'ai une contrainte d'unicité sur le login (2 utilisateurs différents ne doivent pas avoir le même login) et une clé étrangère référençant te_personne_prs.

Si je viole une de ces contraintes, le SGBD va renvoyer une erreur dans son jargon qui n'est pas toujours très compréhensible.
Exemple avec MySQL / MariaDB en essayant de créer un compte utilisateur via un identifiant ne figurant pas encore dans la table te_personne_prs...

Code SQL :

Sélectionner tout

1
2
3
4
SET @id_personne = 100; -- < identifiant inexistant dans te_personne_prs 
CALL pi_ajout_utilisateur (@id_personne, NULL, NULL, NULL, 'plemenager', 'Mon_Pass'); 
SELECT @id_personne; 
-- > #1452 - Cannot add or update a child row: a foreign key constraint fails (`test`.`th_utilisateur_uti`, CONSTRAINT `fk_uti_id_personne` FOREIGN KEY (`uti_id_personne`) REFERENCES `te_personne_prs` (`prs_id`) ON DELETE CASCADE ON UPDATE CASCADE)

Nous avons obtenu un magnifique message en anglais, pas très compréhensible pour l'utilisateur français de l'application, ou même pour un développeur débutant... Ce genre de message d'erreur indiquant la violation d'une clé étrangère figure en effet régulièrement dans les questions posées sur nos forums.

Nous allons donc capturer ces erreurs et en personnaliser le message.
Pour se faire, vous aurez peut-être remarqué que j'ai pris soin de nommer les contraintes (xu_prs_nom_prenom_naissance, fk_uti_id_personne...). Nos SGBD enregistrent les erreurs qu'ils produisent dans une sorte de journal d'événements. Nous allons chercher, dans le texte de la dernière erreur rencontrée (celle qui a fait planter l'exécution de la procédure), le nom de la contrainte qui a été violée.

Avec MySQL / MariaDB, nous créons un HANDLER qui va exécuter des instructions en cas d'erreur renvoyée par le SGBD. Celui-ci fait partie des déclarations de variables précédent le corps du code de la procédure, juste après le BEGIN :

Code SQL :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
DELIMITER // 
CREATE OR REPLACE PROCEDURE pi_ajout_utilisateur 
( 
	INOUT id_personne INTEGER, -- Identifiant de la personne 
	-- Données pour la création éventuelle de la personne 
	IN nom_usuel VARCHAR(40), -- Nom usuel de la personne 
	IN prenom_usuel VARCHAR(40), -- Prénom usuel de la personne 
	IN date_naissance DATE, -- Date de naissance de la personne 
	-- Données pour la création de l'utilisateur 
	IN login VARCHAR(40), -- Nom d'utilisateur 
	IN mot_passe VARCHAR(40) -- Mot de passe de l'utilisateur (on suppose qu'il est crypté par l'application) 
	-- Remarque : la date de création et la validité de l'utilisateur sont automatiques lors de sa création 
) 
BEGIN 
	-- Handler d'erreur  
	DECLARE err_condition CONDITION FOR SQLSTATE '23000'; -- Erreur de violation de contrainte 
	DECLARE EXIT HANDLER FOR err_condition 
		BEGIN 
			-- Les instructions ci-dessous seront exécutées seulement en cas d'erreur du SGBD 
			GET DIAGNOSTICS @nb_errors = NUMBER; -- On récupère dans les diagnostics le numéro du dernier événement (l'erreur à capturer) 
			GET DIAGNOSTICS CONDITION @nb_errors 
				@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT; -- Puis on récupère les informations qui nous intéressent de cette erreur 
  
			IF LOCATE('fk_uti_id_personne', @text) > 0 THEN  
				-- Si on touve le nom de la clé étrangère référençant la personne dans le texte de l'erreur, on prépare notre erreur personnalisée 
				SET @errno = 10001; -- Notre code d'erreur personnalisé 
				SET @text = 'Identifiant de la personne inconnu.'; -- Notre message personnalisé 
			ELSEIF LOCATE('xu_uti_login', @text) > 0 THEN 
				-- Si on trouve le nom de la contrainte d'unicité, on prépare notre erreur personnalisée 
				SET @errno = 10002; 
				SET @text = 'Ce login existe déjà.'; 
			ELSEIF LOCATE('xu_prs_nom_prenom_naissance', @text) > 0 THEN 
				-- Si le trplet {prs_nom, prs_prenom, prs_date_naissance} existe déjà, on prépare notre erreur personnalisée 
				SET @errno = 10003; 
				SET @text = 'Une personne portant ce nom, ce prénom et née ce jour existe déjà.'; 
			END IF; 
  
			-- Génération de l'erreur personnalisée 
			SIGNAL SQLSTATE VALUE '23000' 
			SET MYSQL_ERRNO = @errno, MESSAGE_TEXT = @text; 
		END; 
  
	-- Contrôle de la date de naissance 
	IF date_naissance NOT BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 100 YEAR) AND DATE_SUB(CURRENT_DATE, INTERVAL 10 YEAR) THEN 
		-- Si la date de naissance n'est pas comprise entre aujourd'hui moins 100 ans et aujourd'hui moins 10 ans => erreur ! 
		SIGNAL SQLSTATE '45000' 
		SET MYSQL_ERRNO = 10001, MESSAGE_TEXT = 'Date de naissance incorrecte'; 
	END IF; 
  
	-- Contrôle de l'id_personne et ajout éventuel de la personne 
	IF id_personne IS NULL THEN 
		-- Identifiant de la personne non fourni : on crée d'abord la personne 
		INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_date_naissance) 
		VALUES (nom_usuel, prenom_usuel, date_naissance); 
  
		-- Récupération du prs_id généré par l'auto-incrémentation 
		SET id_personne = LAST_INSERT_ID(); 
	END IF; 
  
	-- Création de l'utilisateur 
	INSERT INTO th_utilisateur_uti (uti_id_personne, uti_login, uti_mot_passe, uti_date_creation, uti_valide) 
	VALUES (id_personne, login, mot_passe, CURRENT_DATE, FALSE); 
END;//

Avec PostgreSQL, la même opération se fait dans la procédure en y ajoutant une structure de contrôle EXCEPTION (voir chapitre 38.6.5 de la documentation). Nous déclarons aussi au préalable (avec le BEGIN de la procédure) une variable msg_erreur destinée à récupérer le texte de l'erreur PostgreSQL :

Code SQL :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
CREATE OR REPLACE FUNCTION pi_ajout_utilisateur 
( 
	INOUT id_personne BIGINT, -- Identifiant de la personne 
	-- Données pour la création éventuelle de la personne 
	IN nom_usuel VARCHAR(40), -- Nom usuel de la personne 
	IN prenom_usuel VARCHAR(40), -- Prénom usuel de la personne 
	IN date_naissance DATE, -- Date de naissance de la personne 
	-- Données pour la création de l'utilisateur 
	IN login VARCHAR(40), -- Nom d'utilisateur 
	IN mot_passe VARCHAR(40) -- Mot de passe de l'utilisateur (on suppose qu'il est crypté par l'application) 
	-- Remarque : la date de création et la validité de l'utilisateur sont automatiques lors de sa création 
) 
AS 
$BODY$ 
DECLARE msg_erreur CHARACTER VARYING DEFAULT ''; -- Message d'erreur éventuel 
BEGIN 
	-- Vérification de la date de naissance	 
	IF date_naissance NOT BETWEEN CURRENT_DATE - INTERVAL '100' YEAR AND CURRENT_DATE - INTERVAL '10' YEAR THEN 
		-- Si la date de naissance n'est pas comprise entre il y a 100 ans et il y a 10 ans => Erreur 
		RAISE EXCEPTION 'Date de naissance incorrecte' 
		USING ERRCODE = 'DVP01'; 
	END IF;	 
  
	IF id_personne IS NULL THEN 
		-- Identifiant de la personne non fourni : on crée d'abord la personne 
		INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_date_naissance) 
		VALUES (nom_usuel, prenom_usuel, date_naissance); 
  
		-- Récupération du prs_id généré par l'auto-incrémentation 
		SELECT CURRVAL('te_personne_prs_prs_id_seq') INTO id_personne; 
	END IF; 
  
	-- Création de l'utilisateur 
	INSERT INTO th_utilisateur_uti (uti_id_personne, uti_login, uti_mot_passe, uti_date_creation, uti_valide) 
	VALUES (id_personne, login, mot_passe, CURRENT_DATE, FALSE); 
EXCEPTION  
    WHEN foreign_key_violation THEN 
		-- En cas d'erreur de clé étrangère, on récupère l'erreur et on la personnalise 
		GET STACKED DIAGNOSTICS msg_erreur = MESSAGE_TEXT; -- On récupère le message de l'erreur PostgreSQL 
  
		IF POSITION('fk_uti_id_personne' IN msg_erreur) > 0 THEN 
			-- Erreur de clé étrangère sur l'id_personne 
			RAISE EXCEPTION 'Identifiant de la personne non trouvé' -- Notre texte d'erreur personnalisé 
			USING ERRCODE = 'DEV01'; -- Notre code d'erreur personnalisé 
		END IF; 
    WHEN unique_violation THEN 
		-- En cas de violation de contrainte d'unicité 
		GET STACKED DIAGNOSTICS msg_erreur = MESSAGE_TEXT; 
  
		IF POSITION('xu_uti_login' IN msg_erreur) > 0 THEN 
			-- Violation de l'unicité du login 
			RAISE EXCEPTION 'Login existant'  -- Notre texte d'erreur personnalisé 
			USING ERRCODE = 'DEV02';  -- Notre code d'erreur personnalisé 
		ELSEIF POSITION('xu_prs_nom_prenom_naissance' IN msg_erreur) > 0 THEN 
            -- Violation de l'unicité du triplet {prs_nom, psr_prenom, psr_date_naissance} 
			RAISE EXCEPTION 'Une personne portant ce nom, ce prénom et née ce jour existe déjà.' -- Notre texte d'erreur personnalisé 
			USING ERRCODE = 'DEV03'; -- Notre code d'erreur personnalisé 
		END IF; 
END; 
$BODY$ 
LANGUAGE plpgsql VOLATILE;

Testons ces dernières procédures complètes...

Avec MySQL / MariaDB :

Code SQL :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Cas erreur : identifiant de la personne inconnu 
SET @id_personne = 100; 
CALL pi_ajout_utilisateur (@id_personne, NULL, NULL, NULL, 'bdoll', 'Son_Pass'); 
SELECT @id_personne; 
-- > #10001 - Identifiant de la personne inconnu. => OK 
  
-- Cas erreur : Login existant 
SET @id_personne = NULL; 
CALL pi_ajout_utilisateur (@id_personne, 'Colucci', 'Michel', '1944-10-28', 'mbellucci', 'Son_Pass'); 
SELECT @id_personne; 
-- > #10002 - Ce login existe déjà. => OK 
  
-- Cas erreur : Nom, prénom et date de naissance existants 
SET @id_personne = NULL; 
CALL pi_ajout_utilisateur (@id_personne, 'Leménager', 'Philippe', '1963-07-24', 'CinéPhil', 'Mon_Pass'); 
SELECT @id_personne; 
-- > #10003 - Une personne portant ce nom, ce prénom et née ce jour existe déjà. => OK

Avec PostgreSQL :

Code SQL :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
-- Cas erreur : identifiant de la personne inconnu 
SELECT pi_ajout_utilisateur (100, NULL, NULL, NULL, 'bdoll', 'Son_Pass'); 
-- > ERROR: Identifiant de la personne non trouvé / État SQL :DEV01 => OK 
  
-- Cas erreur : Login existant 
SELECT pi_ajout_utilisateur (NULL, 'Colucci', 'Michel', '1944-10-28', 'mbellucci', 'Son_Pass'); 
-- > ERROR: Login existant / État SQL :DEV02 => OK 
  
-- Cas erreur : Nom, prénom et date de naissance existants 
SELECT pi_ajout_utilisateur (NULL, 'Leménager', 'Philippe', '1963-07-24', 'CinéPhil', 'Mon_Pass'); 
-- > ERROR: Une personne portant ce nom, ce prénom et née ce jour existe déjà. / État SQL :DEV03 => OK

Tout fonctionne !

Nous avons maintenant une procédure qui personnalise les erreurs du SGBD et qui programme des erreurs spécifiques au besoin.

Vos commentaires sont les bienvenus pour améliorer ce billet.

Une erreur dans cette actualité ? Signalez-nous-la !

Votre nom : Votre e-mail :

Décrivez l'erreur que vous souhaitez porter à notre connaissance :

Commenter Signaler un problème