Articles

, Exemples SQL personnalisés sur ACS

Lorsque l’on travaille sur une belle requête SQL, nous avons tendance à la garder et la sauvegarder en local sur notre poste (parfois dans l’IFS). Pour la partager à un collègue quoi de mieux qu’un bon vieux mail ?

Ou alors, on peut exploiter les Exemples personnalisés d’ACS pour mutualiser nos découvertes !

Exemples SQL sur ACS

Via l’Exécuteur de scripts SQL d’ACS, une multitude d’exemples est fournie.
Pour y accéder trois possibilités :

  • Edition > Exemples > Insertion à partir d’exemples…
  • Ctrl + I
  • Via la petite icône SQL avec les deux flèches ci-dessous

On y retrouve tout un catalogue d’exemples relativement bien fourni :

Il suffit de rechercher les mots clés qui nous intéressent puis de cliquer sur Insertion, et enfin de remplacer les données variables de la requête.

Ajouter ses exemples personnalisés

Création d’un répertoire dans l’IFS

La première étape consiste à créer un répertoire commun dans l’IFS, le plus simple est de le créer dans /home/ qui est généralement déjà configuré comme partagé (donc visible pour Windows).
Par la suite nous utiliserons le chemin suivant : /home/exemples_sql/.
C’est ici que nous travaillerons pour créer nos exemples personnalisés.

Création d’un exemple

Il suffit de créer un nouveau source SQL, par exemple via ACS :

-- category: Exemples perso
-- description: Recherche d'un fichier dans l'ifs
SELECT *
  FROM TABLE (
      qsys2.ifs_object_statistics(start_path_name => '/')
  )
 WHERE path_name LIKE '%fichier.txt';

Le commentaire category permet de trier et regrouper vos exemples par usages.
Le commentaire description correspond au texte indiqué dans la liste des exemples.

Une fois l’exemple terminé il suffit de sauvegarder le script dans le répertoire de l’IFS choisi : Fichier > Sauvegarde sous… > Fichier STREAM IFS.

Il est bien entendu toujours possible de modifier ou supprimer des exemples à partir de ce répertoire.

Intégration du répertoire d’exemples à ACS

Dans un onglet d’ACS, ouvrir le menu des exemples : Edition > Exemples > Insertion à partir d’exemples…
Puis cliquer sur Préférences…

Cliquer ensuite sur Ajout

Indiquer ici le chemin vers le répertoire de l’IBM i qui contient les exemples SQL : \\<Nom de l’IBM i>\home\exemples_sql

Les exemples sont maintenant dans la liste avec les autres.
Ils sont triés par catégorie (que l’on retrouve juste en dessous de la mire de recherche).
Ils sont listés en dessus par description et un aperçu est disponible à droite.

Pour retrouver des exemples deux possibilités :

  • Choisir la catégorie à afficher en cliquant sur la catégorie actuelle (ici Exemples perso)
  • Utiliser la mire de recherche, qui affichera les exemples correspondant aux mots clés, toutes catégories confondues

Pour plus de détails

Exemples SQL de Birgitta Hauser : https://gist.github.com/BirgittaHauser
Exemples SQL de Scott Forstie : https://gist.github.com/forstie
/par
, Utilisation du MD5 sur votre IBM i

Bien que le MD5 ne soit plus utilisé pour l’encryption, il est toujours utilisé pour valider l’authenticité et la conformité des fichiers.

Qu’est-ce qu’un MD5

Un md5 est une chaine de 16 caractères composée de symboles hexadécimaux. Il s’agit en réalité du nom de l’algorithme utilisé pour générer la chaine.

Comme indiqué précédemment son usage est le contrôle d’intégrité des fichier, par exemple lors du partage d’un fichier, on peut mettre à disposition le MD5 afin de contrôler que le téléchargement du fichier s’est bien passé ou que le fichier n’a pas été modifié entre temps.

Pour la suite nous aurons besoin d’un fichier, par simplicité j’utiliserai un simple .txt qui contient la phrase « This is not a test! » présent dans mon répertoire de l’ifs.

Fichier dans l’ifs/home/jl/file.txt
Contenu du fichierThis is not a test!
md5EDA20FB86FE23401A5671734E4E55A12

QSH – md5sum

La première méthode pour générer le MD5 d’un fichier est d’utiliser la commande unix md5sum via QSH :

$ /QOpenSys/pkgs/bin/md5sum /home/jl/file.txt
eda20fb86fe23401a5671734e4e55a12  /home/jl/md5.txt

La fonction retourne le hash et le chemin du fichier.

RPGLE – _cipher

Il est également possible de générer le MD5 via RPG en exploitant la procédure externe cipher.
Je ne m’épancherai pas sur son implémentation complète ici, car bien plus complexe que les deux autres méthodes présentées. De plus, passer par cette méthode, n’est plus le sens de l’histoire.

// Déclaration de la procédure
dcl-pr cipher extproc('_cipher');
	*n pointer value;
	*n pointer value;
	*n pointer value;
end-pr;

// Appel de la procédure
cipher(%ADDR(receiver) : %ADDR(controls) : %ADDR(source));

Lien vers la documentation IBM pour plus d’informations :
https://www.ibm.com/docs/en/i/7.4?topic=ssw_ibm_i_74/rzatk/CIPHER.htm

SQL – hash_md5

En sql on retrouve la fonction hash_md5, qui retourne le hash d’une chaine de caractère passée en paramètre.

❗ Attention à l’encodage de votre chaine de caractères. ❗

Pour que le résultat soit cohérent entre différents systèmes il faut commencer par convertir la chaine de caractère en UTF-8 :

VALUES CAST('This is not a test!' AS VARCHAR(512) CCSID 1208); -- 1208 = UTF-8
-- Retour : This is not a test!

Le résultat est plutôt flagrant ! D’accord pas vraiment… Par contre si on regarde la valeur hexadécimale de la chaine avec et sans conversion :

VALUES HEX('This is not a test!');
-- Retour : E38889A24089A2409596A3408140A385A2A34F

VALUES HEX(CAST('This is not a test!' AS VARCHAR(512) CCSID 1208));
-- Retour : 54686973206973206E6F742061207465737421

Le hachage se fait en hexadécimal, donc le résultat ne serait pas le même sans conversion préalable.

Il suffit maintenant de hacher notre chaine de caractères :

VALUES HASH_MD5(CAST('This is not a test!' AS VARCHAR(512) CCSID 1208));
-- Retour : EDA20FB86FE23401A5671734E4E55A12

On obtient donc la même valeur que celle que l’on a obtenu précédemment (puisque que le contenu de notre fichier est strictement égale à cette chaine de caractère).

La dernière étape est de générer le MD5 directement à partir du fichier, pour cela il suffit d’utiliser la fonction GET_BLOB_FROM_FILE :

VALUES HASH_MD5(GET_BLOB_FROM_FILE('/home/jl/file.txt')) WITH CS;
-- Retour : EDA20FB86FE23401A5671734E4E55A12

Autres algorithmes de hash

Il existe d’autres algorithmes de hash qui permettent de hacher du texte et des fichiers.
Trois autres algorithmes sont généralement disponibles :

  • sha1 (qui génère une chaine de 20 de long)
  • sha256 (qui génère une chaine de 32 de long)
  • sha512 (qui génère une chaine de 64 de long)

QSH

CommandeRésultat
/qopensys/pkgs/bin/sha1sum /home/jl/file.txt10e2e89feb9287eea7a4b7b849b7a380d95c05b9 /home/jl/file.txt
/qopensys/pkgs/bin/sha256sum /home/jl/file.txtff8fb31c076b42fd63377e7ea4747f98c34291ac6e5f53cfd3940913bc9d7d37 /home/jl/file.txt
/qopensys/pkgs/bin/sha512sum /home/jl/file.txt658efb990d2765ca65adb570daa198ef6bee55e39d3a7b7fa31270c35fdf9ee523ce638dea4796ea8923a2ad428e23d23b62175b26494fa8fdca49d5e85ce502 /home/jl/file.txt

SQL

SyntaxeRésultat
VALUES HASH_SHA1(CAST(‘This is not a test!’ AS VARCHAR(512) CCSID 1208));10E2E89FEB9287EEA7A4B7B849B7A380D95C05B9
VALUES HASH_SHA256(CAST(‘This is not a test!’ AS VARCHAR(512) CCSID 1208));FF8FB31C076B42FD63377E7EA4747F98C34291AC6E5F53CFD3940913BC9D7D37
VALUES HASH_SHA512(CAST(‘This is not a test!’ AS VARCHAR(512) CCSID 1208));658EFB990D2765CA65ADB570DAA198EF6BEE55E39D3A7B7FA31270C35FDF9EE523CE638DEA4796EA8923A2AD428E23D23B62175B26494FA8FDCA49D5E85CE502

Pour plus de détails

MD5 : https://fr.wikipedia.org/wiki/MD5
md5sum : https://fr.wikipedia.org/wiki/Md5sum
Fonction sql HASH() : https://www.ibm.com/docs/en/i/7.4?topic=sf-hash-md5-hash-sha1-hash-sha256-hash-sha512
Fonction sql BLOB() : https://www.ibm.com/docs/en/i/7.4?topic=functions-get-blob-from-file
CCSID : https://www.ibm.com/docs/en/i/7.4?topic=information-ccsid-values-defined-i
cypher : https://www.ibm.com/docs/en/i/7.4?topic=ssw_ibm_i_74/rzatk/CIPHER.htm
/par
, , UTILISATION DES API EN SQL

Récupérer une API

Il existe un grand nombre d’API aux fonctionnalités diverses dont certaines nous permettent de récupérer des données structurées dans différents formats (XML, JSON, …).

Grace aux fonctions SQL de l’IBMi nous pouvons récupérer ces données pour les insérer dans les fichiers de la base de données.

Pour les exemples qui suivent, on se base sur trois API tirées du site https://openweathermap.org/ :

  • Une première qui récupère la météo dans une ville donnée

https://api.openweathermap.org/data/2.5/weather?q={city name}&appid={API key}&mode=xml’

  • Une qui récupère jusqu’à 50 communes autour de coordonnées choisies

https://api.openweathermap.org/data/2.5/find?lat=45.75&lon=4.5833&cnt=50&appid={API key}&mode=xml

  • Une qui récupère jusqu’à des communes dans un rectangle de coordonnées choisies

https:// api.openweathermap.org/data/2.5/box/city?bbox=4,45,8,46,50&appid={API key}

Extraire les données de l’API

Sortie API en XML

La commande SQL suivante permet d’afficher les données dans un champ DATA 

SELECT DATA FROM (values
char(SYSTOOLS.HTTPGETCLOB('https://api.openweathermap.org/data/2.5/weather?q={city name}&appid={API key}&mode=xml',''), 4096))
ws(data);

Sortie API en JSON

La commande SQL suivante permet d’afficher les données dans un champ DATA 

SELECT DATA FROM (values
char(SYSTOOLS.HTTPGETCLOB('api.openweathermap.org/data/2.5/weather?q={city name}&appid={API key}',''), 4096))
ws(data);

Sortie API en HTML

La commande SQL suivante permet d’afficher les données dans un champ DATA 

SELECT DATA FROM (values
char(SYSTOOLS.HTTPGETCLOB('https://api.openweathermap.org/data/2.5/weather?q={city name}&appid={API key}&mode=html',''), 4096))
ws(data);

Récupération des données

En XML

On crée un fichier qui contiendra les colonnes que l’on veut récupérer (Ville, Température en cours, date, …)

CREATE TABLE GG/METEODB
(VILLE_ID DECIMAL (9, 0) NOT NULL WITH DEFAULT,
VILLE_NOM CHAR (50) NOT NULL WITH DEFAULT,
TEMPERATURE DECIMAL (5, 2) NOT NULL WITH DEFAULT,
TEMP_MIN DECIMAL (5, 2) NOT NULL WITH DEFAULT,
TEMP_MAX DECIMAL (5, 2) NOT NULL WITH DEFAULT,
DATE_MAJ CHAR (20) NOT NULL WITH DEFAULT);

Récupérer les données de l’API dans le fichier créé :

INSERT INTO GG.METEODB
select xdata.* FROM xmltable('$doc/cities/list/item'
PASSING XMLPARSE(document SYSTOOLS.HTTPGETCLOB('https://api.openweathermap.org/data/2.5/find?lat=45.75&lon=4.5833&cnt=10&appid={API key}&mode=xml','')) AS "doc"
COLUMNS
ville_id decimal(9, 0) PATH 'city/@id',
ville_nom varchar(50) PATH 'city/@name',
temperature decimal(5, 2) PATH 'temperature/@value',
temp_min decimal(5, 2) PATH 'temperature/@min',
temp_max decimal(5, 2) PATH 'temperature/@max',
date_maj varchar(20) PATH 'lastupdate/@value' ) as xdata;

En JSON

Contrairement à XML, on peut créer tout de suite un fichier qui contiendra les colonnes que l’on veut récupérer.

CREATE TABLE GG.METEOBD
(VILLE_ID DECIMAL (9, 0) NOT NULL WITH DEFAULT,
VILLE_NOM CHAR (50) NOT NULL WITH DEFAULT,
TEMPERATURE DECIMAL (5, 2) NOT NULL WITH DEFAULT,
TEMP_MIN DECIMAL (5, 2) NOT NULL WITH DEFAULT,
TEMP_MAX DECIMAL (5, 2) NOT NULL WITH DEFAULT,
DATE_UX_MAJ DECIMAL (12, 0) NOT NULL WITH DEFAULT)

Récupérer les données de l’API dans le fichier créé :

INSERT INTO GG.METEOBD
select * from JSON_TABLE(SYSTOOLS.HTTPGETCLOB('https://api.openweathermap.org/data/2.5/box/city?bbox=4,45,8,46,50&appid={API key}','') ,
'$.list[*]'
COLUMNS
(ville_id decimal(9, 0) PATH '$.id',
ville_nom varchar(50) PATH '$.name',
temperature decimal(5, 2) PATH '$.main.temp',
temp_min decimal(5, 2) PATH '$.main.temp_min',
temp_max decimal(5, 2) PATH '$.main.temp_max',
date_ux_maj decimal(12, 0) PATH '$.dt'));

Pour aller plus loin

En utilisant une API de LA POSTE qui ne nécessite pas d’inscription au préalable, ni d’identification. Nous pouvons réaliser un programme qui nous aide à retrouver une commune à partir d’un code postal, dans l’optique d’aider au remplissage de certains formulaires.
On crée un fichier temporaire en interrogeant directement l’API.

/par