Étiquette : AS400

Vincent Lecomte
[SQL] Concaténer en une seule chaine par ligne

[SQL] Concaténer en une seule chaine par ligne

Il existe, en MySQL, une fonction qui s’appelle GROUP_CONCAT et qui permet de concaténer – d’agréger – plusieurs éléments récupérés depuis une seule table en vue d’obtenir une seule chaine de caractères, par ligne de résultat. Cela permet, par exemple, de récupérer les informations d’un livre, avec les auteurs séparés par des “,”, et ce en une seule ligne de résultat. L’exemple repris dans la documentation de MySQL permet d’afficher le nom des étudiants avec leurs résultats aux différents tests.

SELECT student_name, GROUP_CONCAT(test_score) 
FROM student 
GROUP BY student_name;

Avec DB2 c’est plus délicat et selon la version de la base de données, il faudra utiliser une fonction différente. Par exemple, dans les versions 9.2 et supérieures (corrigez-moi si je fais erreur), il existe la fonction LISTAGG qui reçoit en paramètre le champ pour lequel on veut concaténer les valeurs, et le séparateur.La même fonction – LISTAGG – existe dans le SGBD Oracle, à partir de la version 11g. En ce qui concerne les versions inférieures on peut se dépanner avec une procédure stockée.

Sur AS/400, il a fallu trouver une alternative. Il faut donc utiliser des fonctions XML pour arriver à un résultat similaire, et si on le souhaite, utiliser la fonction REPLACE directement dans la requête pour modifier la chaîne résultante. C’est un peu plus lourd pour la lisibilité globale de la requête.

SELECT pc.ean, pc.libelle, 
	(SELECT 
		XMLSERIALIZE( 
			XMLAGG( 
				XMLELEMENT(NAME x, trim(auteur_nom)) 
			) AS VARCHAR(1024) CCSID 500 
		) 
	FROM auteurs 
	JOIN art_auteurs on pc.ean = art_auteurs.ean 
	AND art_auteurs.idaut = auteurs.idaut
	) AS liste_auteurs 
FROM articles AS pc

On utilise 3 fonctions au total :

  • XMLSERIALIZE : va retourner une expression XML sérialisée, dans le type donné. Le paramètre correspond à l’expression XML. Lors des différents essais l’erreur SQL0332 s’est produite. Il faut faire attention à l’encodage par rapport au client, d’où l’utilisation de la clause CCSID dans l’exemple. Plus d’informations dans la documentation IBM.
  • XMLAGG : retourne une séquence XML avec un objet pour chaque valeur non-nulle contenue dans un ensemble de valeurs XML Le paramètre, dans l’exemple, est donc l’ensemble d’éléments “nom d’auteur”. En savoir plus.
  • XMLELEMENT : renvoie un champ sous forme d’élément XML. Dans l’exemple, le nom de l’auteur sera transformé en NOM. Le premier paramètre correspond au nom de l’attribut, le second au champ à transformer. En savoir plus.

Il suffit ensuite, soit dans votre programme soit directement dans la requête, de modifier la chaine pour y mettre des “,” ou tout autre caractère de votre choix. Bon développement !

[.NET] Accès à DB2 UDB iSeries + DataGridView

[.NET] Accès à DB2 UDB iSeries + DataGridView

On retrouve dans .NET les mêmes possibilités qu’en Java afin de se connecter à une base de données DB2 UDB sur un AS/400. En effet, lorsqu’on installe IBM i Access sur le PC, un kit de développement est inclus. Dedans on retrouve les assemblages nécessaires pour développer en .NET. Dans le menu démarrer vous devriez retrouver, dans le dossier IBM i, l’aide “Boite à outils de programmation“.

Start Menu > Programs > IBM i Access for Windows > Boîte à outils de programmation.

Intégrer l’assemblage dans le projet

Pour effectuer cette opération il faut ouvrir l’explorateur de solutions, effectuer un clic droit sur la catégorie “Références” et choisir l’option “Ajouter une référence” dans le menu contextuel.

Ensuite, il faut cliquer sur “Extensions” et chercher après “IBM for DB2 i .NET Provider“. On peut s’aider du champ de recherche afin de filtrer les résultats. Une fois celui-ci repéré il faut le cocher puis cliquer sur OK.

Lorsqu’on a terminé, si on déroule l’arborescence, on pourra constater qu’un nouvel élément est venu rejoindre les références existantes, à savoir “IBM.Data.DB2.iSeries“. On va dès lors pouvoir utiliser les différents objets de l’API.

Connexion et requête

Tout d’abord, il faudra indiquer, à l’aide de la directive “using“, qu’on souhaite utiliser des objets et types de l’espace de noms “IBM.Data.DB2.iSeries” sans avoir à le qualifier à nouveau. Pour information nous programmons ici en C#.

using IBM.Data.DB2.iSeries;

Ensuite on va, dans le code d’un bouton par exemple, initialiser la connexion, récupérer des données dans une table, puis fermer cette connexion. On va aussi afficher le nom du job démarré sur la machine AS/400.

C’est en instanciant un objet de la classe “iDB2Connection” qu’on va pouvoir se connecter. Pour cela, on devra renseigner dans le paramètre du constructeur une chaine de connexion (ConnectionString). Pour savoir quelles sont les propriétés qu’on peut renseigner, consultez l’aide ou la documentation en ligne.

iDB2Connection cn2 = new iDB2Connection("DataSource=ADR_IP" +
  ";UserID=USER;Password=PASSWD;Naming=SQL"); 
cn2.Open();

Dans l’exemple ci-dessus, on a spécifié :

  • DataSource : le nom ou l’adresse du serveur à contacter.
  • UserID : le nom d’utilisateur avec lequel on veut se connecter.
  • Password : le mot de passe associé.
  • Naming : indique si on veut utiliser les conventions de nommage SQL.

Ensuite on a utilisé la fonction “Open()” de l’objet créé afin d’ouvrir explicitement la connexion. L’étape suivante consiste à créer une “commande” de type “select” qui sera liée à la connexion précédente.

iDB2Command myCmd = cn2.CreateCommand(); 
myCmd.CommandText = "SELECT * FROM myLib.myTable";

La propriété “CommandText” contiendra le code SQL de la requête à exécuter. On va remplir ce qu’on appelle un “DataGridView“. C’est un contrôle personnalisable qui permet d’afficher assez facilement un tableau, lié à une source de données, ou non.

iDB2DataAdapter da = new iDB2DataAdapter(myCmd);
DataTable ds = new DataTable(); da.Fill(ds);
dataGridView1.DataSource = ds;

Pour cela on va utiliser deux autres types de variables, un “iDB2DataAdapter” et un “DataTable“. Le second est une représentation des données de la table. Le premier quant à lui va servir de “pont” entre la base de données et notre objet “DataTable” en question.

  • On crée donc une instance de iDB2DataAdapter en passant en paramètre la commande.
  • On crée un objet DataTable, sans spécifier de paramètre au constructeur.
  • On utilise la fonctionne “Fill()” de notre objet iDB2DataAdapter avec en paramètre, l’objet DataTable.
  • Ensuite on lie cet objet DataTable à notre contrôle grâce à la propriété DataSource.
  • Il n’y a rien à faire ; le tableau apparaitra automatiquement.

Voilà ce que ça donne à l’exécution.

Et bien sûr il ne faudra pas oublier de se déconnecter de la base de données une fois que c’est terminé (tout dépend de ce que vous souhaitez faire ensuite).

Afficher le nom du job

Si on veut afficher le nom du job qui a été créé pour la connexion en cours, on peut par exemple écrire le code suivant.

MessageBox.Show(cn2.JobName);

Cela affichera, dans une boite de dialogue, son nom complet (travail, utilisateur et numéro).

Déconnexion

C’est très simple ! Il suffit d’appeler la fonction “Close()” de l’objet Connexion créé au début de ce billet. Cela aura pour effet de mettre fin au job créé sur l’AS/400.

[SQL] Insertion multiple

[SQL] Insertion multiple

Dans de nombreuses bases de données, il existe la possibilité d’effectuer une insertion multiple avec une seule requête SQL. La syntaxe suivante montre comment écrire la requête pour ajouter deux enregistrements.

INSERT INTO ma_table (col1, col2, ..., col n) 
VALUES 
  (val1, 'val2',..., val n),
  (val4, 'val5',..., val z)

La même requête en omettant le nom des colonnes.

INSERT ALL INTO ma_table (col1, col2,..., col n) 
VALUES (val1, 'val2',..., val n) 
INTO ma_table (col1, col2,..., col n) 
VALUES (val4, 'val5',..., val z)

Compatibilité

Disponible sur (liste non exhaustive) :

  • MySQL.
  • DB2 (idem sur OS/400).
  • HFSQL (à partir de la version 20).
  • PostgreSQL.
  • SQL Server 2008 et supérieur.
  • (…)

Si vous utilisez la base de données Oracle, la syntaxe est différente.

INSERT ALL INTO ma_table (col1, col2,..., col n) 
VALUES (val1, 'val2',..., val n)
INTO ma_table (col1, col2,..., col n) 
VALUES (val4, 'val5',..., val z)

Si vous utilisez une ancienne version de SQL Server (l’astuce suivante peut aussi être adaptée pour une base SQLite ou même Firebird).

INSERT INTO [schema.]MaTable (ID, Nom) 
SELECT 200, 'Vincent' 
UNION ALL 
SELECT 300, 'Cyril' 
UNION ALL 
SELECT 400, 'Rudi'

Avec Firebird 2.0 et supérieur on peut exécuter sous forme de bloc (voir la documentation officielle). N’hésitez pas à partager la syntaxe pour effectuer la même opération dans des bases de données différentes !

[SQL] Mémo – Modifier le type d’une colonne

[SQL] Mémo – Modifier le type d’une colonne

Pour modifier le type d’une colonne dans une table DB2/400, vous pouvez utiliser la syntaxe ALTER TABLE … ALTER COLUMN. Il faudra cependant spécifier une clause supplémentaire : SET DATA TYPE. Cela vous permettra de changer la taille par exemple, ou bien passer d’un entier sur 4 octets à un entier sur 8. Exemple :

ALTER TABLE 'biblio.table' ALTER COLUMN email 
SET DATA TYPE varchar(50);

Dans cet exemple nous modifions la colonne qui contient l’e-mail en indiquant qu’on souhaite une chaine de caractères à taille variable d’un maximum de 50 caractères. Dans SQL Server, cela donnerait quelque chose comme ceci :

ALTER TABLE 'schema.table' ALTER COLUMN email varchar(50);

Et dans Oracle ou même MySQL :

ALTER TABLE 'schema.table' MODIFIY COLUMN email varchar(50);

Bon développement !

[SQL] Mémo – Dates dans DB2 UDB for iSeries

[SQL] Mémo – Dates dans DB2 UDB for iSeries

Sur l’AS/400, lorsqu’on utilise des champs de type date, il faut parfois utiliser certaines fonctions de conversion ou du moins utiliser une chaine de caractères d’un format spécifique. Il existe aussi un tas de fonctions qui permettent par exemple de récupérer le jour de la semaine, le libellé du mois, ou bien une représentation sous forme d’entier.

Fonction “Date”

Il est possible de passer une chaine de caractères à la fonction “Date” pour retourner une variable du bon type. Dans l’exemple qui suit, on utilise la table système pour obtenir une seule ligne de résultat. La chaine de caractère passée en paramètre est au format “MM/DD/YYYY”.

SELECT DATE('04/10/2014') FROM sysibm/sysdummy1;

Ce qui permet d’obtenir à l’écran “10/04/2014”. On peut également convertir ce résultat au format “YYYY-MM-DD” grâce à la fonction CHAR. Celle-ci prend en paramètre une variable ou un champ de type date ainsi qu’un paramètre facultatif supplémentaire étant le type (ex : ISO, USA).

SELECT CHAR(DATE('04/10/2014'),iso) FROM sysibm/sysdummy1;

Cette requête nous retourne alors la date “2014-04-10”, soit le 10 avril : c’est ce que nous cherchions effectivement à faire. En utilisant le format “usa” au lieu d’”iso”, nous aurions obtenu exactement le même résultat que la chaine passée en paramètre de la fonction “DATE”.

Une chaine dans la condition

Il est également possible de récupérer des résultats d’une table en comparant la date à une chaine de caractères, pour autant qu’elle soit au bon format. En pratique, on pourrait directement utiliser les fonctions CHAR et DATE, mais il est également possible de réaliser ceci :

SELECT * FROM MA_TABLE WHERE CH_DAT = '2014-04-10';

Dans l’exemple ci-dessus, nous possédons une table nommée “MA_TABLE” contenant plusieurs champs dont un de type date, “CH_DAT”. Ce champ n’est donc pas un numérique, qui est pourtant couramment utilisé pour stocker les dates au format YYYYMMDD.

Récupérer le jour, le mois, l’année

Comme on le sait, les champs de type date imposent d’effectuer des conversions, mais permettent tout de même de récupérer des données aisément sans avoir à découper une quelconque chaine de caractères. Inutile de se battre avec la fonction SUBSTRING pour récupérer le jour ou le mois.

SELECT DAY(CH_DAT) FROM MA_TABLE; 
SELECT MONTH(CH_DAT) FROM MA_TABLE; 
SELECT YEAR(CH_DAT) FROM MA_TABLE;

En supposant que le champ “CH_DAT” soit bien de type date, nous récupérons alors les jours, les mois ou les années pour chaque ligne de la table. Depuis la V5R3, il est également possible de récupérer le libellé du mois (ex : Janvier, Février, etc), avec la fonction MONTHNAME.

SELECT MONTHNAME(CURRENT DATE) FROM SYSIBM/SYSDUMMY1;

Dans l’exemple ci-dessus, nous utilisons “CURRENT DATE” qui renvoie la date système. Du coup, avec la requête ci-dessus, nous récupérons la valeur “Avril”, ce qui est bien correct puisque nous sommes bien le 30 du mois lors de l’exécution de la requête.

D’autres fonctions utiles

Il existe bien entendu des fonctions qui permettent notamment de renvoyer le jour de la semaine avec DAYOFWEEK, qui considère que le dimanche est le premier jour de la semaine, et DAYOFWEEK_ISO, qui considère que c’est le lundi le 1er. La fonction DAYS renvoie une interprétation de la date en entier, ce qui permet également de calculer le nombre de jours qui se sont écoulés entre deux dates.

La fonction JULIAN_DAY permet de renvoyer le nombre de jours entre le 1er janvier 4712 avant J.C. jusqu’à la date renseignée en paramètre. Enfin on peut aussi utiliser WEEK ou WEEK_ISO pour obtenir le numéro de la semaine, sans avoir à effectuer de calcul particulier…

[AS400] Mémo – Commandes réseau

[AS400] Mémo – Commandes réseau

C’est en voyant intervenir un collègue il y a quelques jours que j’ai découvert qu’il existait des commandes sur l’AS/400 pour obtenir des informations réseau telles que la route empruntée pour contacter une IP distante ou simplement pour obtenir le délai de réponse d’un hôte en lui envoyant plusieurs paquets. Évidemment, on pouvait s’en douter, mais on ne le sait pas forcément surtout quand on s’habitue à le faire avec un PC. Voici quatre commandes à retenir !

Commande
Description
Paramètres
pingTester l’accessibilité d’une machine sur un réseau IPAstuce : utilisez le prompt = F4.
Obligatoire (un des 2) :
– RMTSYS = nom système (chaine)
Facultatifs :
– INTNETADR = adr. IPv4 / 6 (chaine)
* Si spécifiée,
=> RMTSYS = *INTNETADR
– ADRVERFMT : format d’adresse
*CALC = calculé
*IP4 = IPv4
*IP6 = IPv6
– MSGMODE : mode affichage message
– NBRPKT : nombre de paquets (déf : 5)
– PKTLEN : taille paquet en bytes (int)
telnetPermet de se connecter à une machine via le protocole Telnet, si elle l’autorise ou le prend en chargeObligatoire :
– RMTSYS = nom système (chaine)
Facultatifs :
– INTNETADR = adr. IPv4 / 6 (chaine)
* Si spécifiée,
=> RMTSYS = *INTNETADR
– PORT : spécifier un autre port si néc.
– SSL : *ENVAR, *YES, *NO.
** D’autres paramètres permettent de configurer le clavier pour la session.
tracerouteUtilitaire qui permet de suivre les chemins qu’un paquet IP va prend pour aller de la machine locale à une autre machine distante.Obligatoire :
– RMTSYS = nom système (chaine).
– RANGE : paramètre du TTL
Spécifier le départ et l’arrêt.
– PKTLEN : taille du paquet (bytes).
– WAITTIME : temps attente réponse.
– OUTPUT : *MSG, *VERBOSE, *DTAQ
** D’autres paramètres à définir
netstatL’exécution de cette commande permet d’avoir par exemple la liste des ports ouverts dans l’invite ; cependant ici cela se fait dans l’émulateur 5250.Le fait de saisir la commande “netstat” permet d’arriver sur le menu “Work with TCP/IP Network Status” ; de là, on retrouve plusieurs options :
1) Work with IPv4 interface status :
Afficher et gérer les interfaces.
2) Display TCP/IP Route Information
3) Work with IPv4 connection status
Liste des ports ouverts
** Les trois options suivantes proposent la même chose pour le protocole IPv6.

Pour lancer ces commandes, vous pouvez le faire depuis le menu principal ; les messages sont susceptibles de s’afficher dans la zone d’état.

Vous pouvez également utiliser un “CALL QCMD” pour obtenir l’interface vous permettant d’exécuter des commandes. Bien plus pratique lors d’un ping car on voit toutes les informations s’afficher à l’écran.

[SQL] Réaliser des “upserts” sur DB2/400

[SQL] Réaliser des “upserts” sur DB2/400

Beaucoup de SGBD proposent des requêtes plutôt simples pour réaliser ce qu’on appelle couramment des “upserts” (ou encore “insert-or-update). Cela permet donc d’effectuer une mise à jour si l’insertion échoue. Dans DB2/400, il faut utiliser une requête “MERGE INTO”.

La documentation d’IBM, en plus de détailler chaque clause, propose également des exemples concrets. Le principe est simple : la requête se base sur une condition de recherche. Il faut alors prévoir le cas où la recherche a abouti et le cas contraire.

Syntaxe

Exemple

MERGE INTO FPRXML p 
USING TABLE(
 VALUES(
  CAST (? AS CHAR(13)), 
  CAST (? AS INTEGER)
 )
) s(FEAN13, FCASKU)
ON (p.FEAN13 = s.FEAN13 
AND P.FCASKU = s.FCASKU)
WHEN NOT MATCHED THEN INSERT (FCACAR, FCASKU, FEAN13)
VALUES (?, ?, ?)
WHEN MATCHED THEN UPDATE SET FPXFNC = ?

Comme on le voit dans l’exemple ci-dessus, la condition de recherche peut être simplement un ensemble de valeurs, qui constituent alors une table. Une jointure est alors effectuée sur la table dans laquelle on veut faire les mises à jour (p.x = s.x). Notez que l’on peut aussi utiliser directement une table (ex : USING matable) ou bien une requête de sélection.

[SQL] Connaitre le RRN sur DB2/400

[SQL] Connaitre le RRN sur DB2/400

En SQL, il est possible de connaitre le numéro de ligne grâce à la fonction “Rownumber Over()”. Cependant, elle renvoie la position de l’enregistrement dans l’ensemble de données récupérées, alors que ce que nous souhaitons c’est retrouver le numéro relatif que la base de données lui a attribué lors de l’insertion. On peut même trier sur celui-ci pour obtenir les données dans leur ordre réel d’ajout. 

Syntaxe

SELECT RRN(alias1) AS rownum, ... 
FROM ma_table AS alias1 
WHERE ...

La table dont on veut récupérer le “numéro d’enregistrement relatif”, ou en anglais le “relative record number”, peut bien sûr faire partie d’une jointure. Attention également si vous utilisez des bases de données distribuées (plusieurs parties sur des sites différents et géographiquement distants).

La note ci-dessous est à prendre en compte:

The RRN function returns the relative record number of the row stored on a node in a distributed file. This means that RRN is not unique for a distributed file. A unique record in the file is specified if you combine RRN with either NODENAME or NODENUMBER.

D’autres exemples sur le site IT Jungle, par Howard F. Arner Jr.

[SQL] Identifiant automatique dans DB2/400

[SQL] Identifiant automatique dans DB2/400

Étant donné qu’en DB2/400, il n’y ait pas de gestion de la clause “Auto Increment”, nous avons cherché le moyen de générer un identifiant unique lors de chacune des insertions. Deux moyens se présentent à nous : l’utilisation de séquences, dans le cas où la clé est composée d’un numéro avec des préfixes ou suffixes supplémentaires, ou bien mieux encore, la clause “Generated as identity” à utiliser lors de la création d’une table en SQL.

Utilisation des séquences

Nous en parlions il y a déjà un petit peu plus d’un an, plus précisément le 11 octobre 2011, dans un article exclusivement dédié à ce sujet. Voici le lien pour vous y rendre: comment gérer les séquences dans DB2/400.

Utilisation de l’auto-incrément

Pour garantir la génération d’une clé unique, le fait d’indiquer qu’elle est primaire ne suffit pas. On souhaite alors aller plus loin et on voudrait que l’identifiant soit un nombre automatiquement incrémenté à chaque ajout. Le champ ne doit alors plus être spécifié dans une requête de type “Insert”.

Voici un exemple de syntaxe que vous pouvez utiliser:

CREATE TABLE vlecomte/test ( 
	idauto INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY 
		(START WITH 0, INCREMENT BY 1, NO CACHE ) PRIMARY KEY, 
	champ VARCHAR(20) 
);

On utilise donc la clause “Generated always AS Identity” en spécifiant la valeur de départ, le pas de l’incrément, et le cache. Exemple : démarrer à la valeur 0, utiliser un incrément de 1 et pas de mise en cache pour les sessions. Remarquez aussi qu’on utilise la norme système pour le SQL : cela explique pourquoi on ne retrouve pas un “.” à la place du slash quand on spécifie le schéma dans lequel on crée notre table.

Modifier la colonne

Imaginons que vous supprimiez le contenu de cette table après avoir effectué un archivage. Vous souhaitez alors recommencer avec une valeur bien définie plutôt que de recommencer avec la suivante. Nous allons donc vous montrer un exemple de syntaxe pour modifier la colonne.

ALTER TABLE vlecomte/test 
ALTER COLUMN idauto RESTART WITH 8;

On commence d’abord par le niveau le plus haut : la table. On spécifie ensuite qu’on veut modifier la colonne “idauto” (c’est son nom) en spécifiant la clause “Restart with” et la valeur 8. Lors du prochain ajout, la valeur de notre champ clé sera égale à 8.

Sur quelle version?

Ceci a été testé sur une version UDB V7R1MO du système iSeries. Consultez éventuellement la documentation officielle pour savoir si cela s’applique également à la vôtre. Cela doit aussi fonctionner pour une version 8+ sous l’OS Linux.

[SQL] Créer des triggers basiques sur DB2/400

[SQL] Créer des triggers basiques sur DB2/400

Il est toujours intéressant de savoir comment fonctionne une base de données s’exécutant sur un système que l’on connait peu, voire pas du tout. C’est la raison pour laquelle aujourd’hui, nous allons voir ensemble comment créer un trigger, ou en français, un “déclencheur”. Un trigger est une sorte de programme, qui permet d’effectuer une ou plusieurs actions spécifiques, en fonction des évènements qui se produisent. Les déclencheurs sont différents des procédures stockées par le fait qu’on ne les appelle pas explicitement. On les retrouve dans de nombreux SGBD (Système de Gestion de Base de Données) tels que Oracle ou MySQL, mais aussi dans DB2 (Linux, OS/400), malgré que la syntaxe puisse varier en fonction de celui utilisé.

Dans notre exemple, nous allons utiliser :

  • Une table de base, contenant deux champs, le code d’un article (10 caractères), ainsi qu’un libellé (50 caractères). Elle s’appellera “VLTEST”.
  • Une seconde table d’historique, contenant les deux champs de la table précédente, ainsi qu’un champ supplémentaire “date” (numérique de 8 dont 0) et un champ de 2 caractères pour indiquer le type de trigger (exemple : si c’est un déclencheur “After Insert”, on inscrira “AI” dans le champ). La table en question s’appellera “VLTESTHI”.

Des requêtes seront exécutées, aussi bien avec l’interpréteur SQL fourni dans le système i qu’avec un interpréteur basique écrit en WinDev, mais aussi par l’intermédiaire de fonctions natives WinDev (HAjoute, HModifie), et via des instructions en Java exploitant le pilote JDBC adéquat.Nous supposons que le schéma de la base de données utilisées sera la bibliothèque “VLECOMTE”, spécialement créée pour tous les tests de ce genre. La norme d’écriture des requêtes reste liée au système, donc la notation “schéma + table” utilisée ressemblera à “nom_schema/nom_table.

Les triggers sur AS/400

Sachez que sur l’AS/400, les méthodes principales pour créer les déclencheurs sont les suivantes (peut-être en existe-t-il d’autres plus simples ?) :

  • Création d’un programme en RPG : cette façon de procéder est plus compliquée, car il faut jouer sur les tailles des différents “membres” d’une table. Le programme doit ensuite être associé à la table via la commande ADDPFTRG. Cette commande doit être répétée plusieurs fois pour tous les types de trigger que l’on veut créer (avant/après insertion, avant/après mise à jour, etc).
  • Création d’un trigger via le langage procédural SQL : il s’agit là de la méthode la plus facile, mais pas forcément disponible sur toutes les versions du système ; cela dépend de la version interne de la base de données. On utilise alors la syntaxe CREATE TRIGGER pour créer, et DROP TRIGGER pour supprimer.

Triggers en PL/SQL

Le “Procedural Language / SQL” est un langage propriétaire, créé par Oracle et intégré à des SGBD comme TimesTen-In-Memory Database, DB2 depuis la version 9.7, et bien sûr, dans le système du même nom que son propriétaire. Il permet entre autres de coupler des requêtes SQL à des instructions simples comme des boucles ou des conditions, et de stocker cela sur le serveur sous forme de traitements particuliers. Les procédures stockées peuvent donc être écrites dans ce langage.

Créer notre premier trigger

Nous supposons ici que vous avez déjà créé vos tables et donc que vous êtes prêts à entamer le tutorial. N’oubliez pas que les requêtes SQL et les déclencheurs sont créés dans un schéma particulier, donc n’oubliez pas de changer le contenu de celles-ci si vous utilisez des bibliothèques de développement différentes.

Syntaxe

La documentation officielle d’IBM nous décrit, de manière très détaillée, les différentes clauses valides pour la création d’un trigger. Vous pouvez la trouver en suivant le lien suivant, “CREATE TRIGGER Statement” (en anglais), ou vous pouvez jeter un œil à l’image suivante, copiée depuis cette même source (cliquez pour agrandir).

Code et explications

Dans le premier exemple, nous allons créer un déclencheur qui réagit à l’ajout d’une ligne. Plus particulièrement, celui-ci devra déclencher “après insertion” pour chaque nouvelle ligne. Il exécutera une requête SQL qui insèrera des valeurs dans la table d’historique.

CREATE NEW TRIGGER vlecomte/vltest_ai 
AFTER INSERT ON vlecomte/vltest REFERENCING NEW AS post 
FOR EACH ROW 
BEGIN ATOMIC
	INSERT INTO vlecomte/vltesthi (cart, lib, vldat, typ) 
	VALUES (post.cart, post.lib, 20120101, 'AI'); 
END

Quelques précisions :

  • La clause “Create Trigger” est suivie du nom du déclencheur. Mieux encore : on peut directement spécifier dans quel schéma celui-ci doit être créé.
  • La clause “After Insert On” est suivie du nom de la table sur laquelle on va appliquer le trigger. On a bien précisé “après insertion” dans ce cas, donc cela déclenchera bien une fois que les données auront été intégrées.
  • La clause “Referencing” permet de définir les alias pour les tables ou les anciennes et nouvelles variables, qui pourront être réutilisées lors du traitement, dans les conditions, les boucles, et même les requêtes.
  • La clause “For each row” indique que le traitement doit déclencher pour chaque nouvelle ligne insérée.
  • Entre la clause “Begin Atomic” et “End”, on a placé une requête SQL. On peut en placer autant que l’on veut (nous le verrons après). Certains types de traitements ne peuvent pas être exécutés en fonction du type de déclencheur.
  • La date utilisée ici est écrite en dur.
  • Contraintes à prendre en compte : voir plus bas.

Lorsque le trigger est créé, on reçoit un message de confirmation (s’il est créé via l’interpréteur “Start SQL” du système). En explorant la bibliothèque dans laquelle on l’a créé, on peut apercevoir un objet de type *PGM appelé “VLTEST_AI”, qui a été créé automatiquement et qui a été automatiquement associé à la table. C’est ce qu’on aurait du faire si on avait utilisé la méthode compliquée : coder le programme pour le déclencheur puis l’associer avec la commande AS/400 adéquate.

Insertion de la 1re ligne

INSERT INTO vlecomte/vltest 
VALUES ('05030101','ASTERIX');

En vérifiant dans la table d’historique (VLTESTHI), on retrouve bien une ligne avec le code article “05030101”, le libellé “Astérix”, la date en dur “20120926” et le type du trigger qui a déclenché.

Amélioration

Afin d’insérer la bonne date à chaque fois que le trigger est exécuté, on doit modifier la requête d’insertion du trigger. On va donc le recréer mais d’abord, il faut que vous le supprimez au moyen de l’instruction “DROP TRIGGER”.

Code et explications

Dans l’exemple ci-dessous, nous allons nous servir des variables globales et des fonctions proposées par le système de base de données. Par exemple, CURRENT_DATE permet de renvoyer la date du jour dans un format spécifique. Il faut alors transformer dans une date qui nous convient, comme par exemple une date au format “AAAAMMJJ” (année-mois-jour).

CREATE NEW TRIGGER vlecomte/vltest_ai 
AFTER INSERT ON vlecomte/vltest REFERENCING NEW AS post 
FOR EACH ROW 
BEGIN ATOMIC 
	INSERT INTO vlecomte/vltesthi (cart, lib, vldat, typ) 
	VALUES (post.cart, post.lib, 
		YEAR(DATE(DAYS(CURRENT DATE))) * 1000 
		+ MONTH(DATE(DAYS(CURRENT DATE))) * 100 
		+ DAY(DATE(DAYS(CURRENT DATE)), 'AI'); 
END

La partie qui nous intéresse est une nouvelle fois en rouge : il s’agit de générer une date au bon format avec les différentes fonctions et variables fournies. On traite les années, les mois, et les jours, et on additionne le tout pour obtenir une date comme ceci : “20120926” (c’est la date à laquelle cet article a été rédigé).

Insertion de la 2ème ligne

C’est avec WinDev et les fonctions natives que nous créons une nouvelle ligne. Pour cela, il sera nécessaire d’utiliser le module Easycom (payant). Notez qu’il est aussi possible d’utiliser la fonction “HExecuteRequeteSQL” pour lancer directement une requête texte.

Une fois ce code exécuté, regardez à nouveau le contenu de votre table d’historique. Vous verrez apparaitre l’article “05050102” avec le libellé “TEST ART” et la bonne date, cette fois-ci.

Trigger après mise à jour

Voici un exemple de trigger qui déclenchera après une mise à jour. Etant donné que lorsqu’on écrit un déclencheur “avant mise à jour”, on ne peut exécuter de requête SQL dans le corps de celui-ci. C’est l’une des contraintes que nous évoquerons plus bas dans cet article.

Code et explications

Ce déclencheur s’exécutera lorsqu’une mise à jour sera effectuée (“après la mise à jour”), et ce pour chacune des lignes. On veut conserver, pour chaque ligne, le record avant et après sa modification.

Quelques petites précisions :

  • On a déclaré un alias pour pouvoir interagir avec les anciennes valeurs (clause REFERENCINGOLD AS…). “Old” et “New” ne peuvent pas être utilisés dans tous les types de triggers. Remarque : il s’agit de variables de transition.
  • On a modifié la valeur à insérer dans la dernière colonne pour qu’il indique qu’il s’agit d’un déclencheur de mise à jour (“BU” et “AU”).

Mise à jour d’une ligne

CREATE NEW TRIGGER vlecomte/vltest_bu 
AFTER UPDATE ON vlecomte/vltest 
  REFERENCING NEW AS post OLD AS pre 
FOR EACH ROW
BEGIN ATOMIC 
	INSERT INTO vlecomte/vltesthi (cart, lib, vldat, typ) 
	VALUES (pre.cart, pre.lib, 
		YEAR(DATE(DAYS(CURRENT DATE))) * 1000
		+ MONTH(DATE(DAYS(CURRENT DATE))) * 100 
		+ DAY(DATE(DAYS(CURRENT DATE)), 'BU');
	INSERT INTO vlecomte/vltesthi (cart, lib, vldat, typ)
	VALUES (post.cart, post.lib, 
		YEAR(DATE(DAYS(CURRENT DATE))) * 1000 
		+ MONTH(DATE(DAYS(CURRENT DATE))) * 100 
		+ DAY(DATE(DAYS(CURRENT DATE)), 'AU');
END

Effectuez une requête de mise à jour sur l’article “05030101” inséré lors du premier exemple, et modifiez le libellé en “Modification Libellé”. Normalement, deux lignes s’ajouteront dans la table d’historique. Essayez également d’effectuer cette requête en Java en utilisant le bon pilote JDBC (cela a été expliqué sur ce blog).

Contraintes à prendre en compte

  • On ne peut pas créer un trigger sur une table en cours d’utilisation.
  • Pour pouvoir créer un trigger, il faut suffisamment de privilèges.
  • Les instructions suivantes disponibles pour le corps du déclencheur :

  • Tables et variables de transition exploitables ou non avec la clause “Referencing”, en fonction du type de déclencheur :

Sources

Syntaxe du “Create Trigger”
Complément (variables, exemples, registres)