SQL et programmation RPG/400
Introduction
La nouveauté
Il est possible d'inclure des commandes SQL dans des programmes en utilisant des variables. Ceci permet notamment d'empêcher les utilisateurs vulgaires de faire n'importe-quoi, contrairement aux authentiques informaticiens, toujours maîtres de leurs actes – comme chacun sait –
Intérêt
- Action sur des groupes de données : SQL permet de mettre-à-jour ou supprimer une masse d’enregistrements en une seule instruction.
- Actions variées sur une colonne : avec SQL, on peut totaliser, calculer, et agir de différentes manières sur un ou plusieurs champs. On peut aussi par exemple séparer une unique colonne en plusieurs.
- Convertir des colonnes de données en liste. Avec SQL, on peut exécuter en une seule passe des opérations qui exigeraient des opérations complexes en programmation classique (boucle de lecture enregistrement par enregistrement, alimentation d’un tableau avec gestion de compteurs etc.)
Questions de performances
Ne pas être intégriste : il reste des cas où un classique CHAIN peut être plus efficace. Si on veut lire un seul enregistrement dont on connaît la clef d’appel, c’est le cas-type où ou les fonctions natives d’entrée/sortie de l’OS/400 font merveille.
Quand on utilise SQL, il est fortement conseillé d’accéder aux données par le fichier physique et non pas via un logique.
Conditions
La commande STRQSL (SQL interactif) doit fonctionner, sinon on ne pourra pas compiler. Il faut en effet disposer de DB2 Query manager et SQL Developpent Kit. Cela dit, un exécutable fonctionnera sur toute machine, fut-elle dépourvue de ces outils. Pour des utilisations occasionnelles de SQL, se rappeler qu’Operation Navigator permet d’utiliser SQL.
A savoir
Le programme sera de type SQLRPGLE. Il est compilé avec la commande CRTSQLRPGLI (ou avec PDM option 14 pour créer un programme lié *PGM, option 15 pour créer un *MODULE). Si le fichier n’est pas journalisé (ce qui se rencontre souvent...) l'option COMMIT doit être à *NONE ; ou alors on ajoute WITH NC dans la commande SQL ; on peut encore indiquer set option commit=*none dans le exec sql.
Exemples
Update Customer Set Remise = :NewDisc Where ShpCity = :SlcCusCity
Création d'un objet SQL ILE RPG (précompilation)
CRTSQLRPGI OBJ( appexc/updcust )
SRCFILE( appsrc/qrpglesrc )
SRCMBR( updcust )
Inclusion directe dans le RPG
....1....+....2....+....3....+....4....+....5. C/Exec SQL C+ Update Customer C+ Set Remise = :NewDisc C+ Where ShpCity = :SlcCusCity C/End-Exec
Idem en COBOL/400
Exec SQL
Update Customer
Set Remise = :NewDisc
Where ShpCity = :SlcCusCity
End-Exec
Exemple avec des commentaires
C Eval NewDisc = InpDisc C Eval SlcCusCity = InpCusCityC/Exec SQL * Change the discount for all customers in a city C+ Update Customer C+ Set Remise = :NewDisc -- New Remise C+ Where ShpCity = :SlcCusCity -- Selected city C/End-Exec
Exemple avec un indicateur
Si NewDisc est "null", indicateur NewDisNul indicateur = 1
....1....+....2....+....3....+....4....+....5. D NewDiscNul S 4B 0 C If InpDiscNul = 'Y' C Eval NewDisc = 0 C Eval NewDiscNul = -1 C Else C Eval NewDisc = InpDisc C Eval NewDiscNul = 0 C EndIf C Eval SlcCusCity = InpCusCity C/Exec SQL C+ Update Customer C+ Set Remise = :NewDisc :NewDiscNul C+ Where ShpCity = :SlcCusCity C/End-Exec
Gestion des erreurs
Utiliser la SQLCA (SQL communication area)
....1....+....2....+....3....+....4....+....5....+....6....+ C Select C When SQLCod < 0 C ExSr SQLError C When SQLCod = 100 C ExSr SQLNoRow C When ( SQLCod > 0 ) Or ( SQLWn0 <> *Blank ) C ExSr SQLWarning C EndSl
Il est plus simple d'avoir un source tout prêt
C/Exec SQL C+ Update Customer C+ Set Remise = :NewDisc C+ Where ShpCity = :SlcCusCity C/End-Exec C/Copy AppSrc/RpgCpySrc,SQLErrChk
Commandes SQL statiques
Un SELECT statique ne peut récupérer plus d'une ligne (enregistrement).
Exemple
....1....+....2....+....3....+....4....+....5....+....6....+
D CustomerR DS
D CsNumCli 7P 0
D CsNomCli 30A
D CsShpCity 30A
D CsRemise 5P 3
D CsCityNull S 4B 0
D CsDiscNull S 4B 0
C* Get the values for the customer from user input and
C* place in the host variables CsNumCli, etc.
C* Also set the CsCityNull and CsDiscNull indicator
C* variables to 0 if a value is supplied for the column,
C* or to -1 if the column should be set to null.
.
.
.
C/Exec SQL
C+ Insert Into Customer
C+ ( NumCli,
C+ NomCli,
C+ ShpCity,
C+ Remise )
C+ Values ( :CsNumCli,
C+ :CsNomCli,
C+ :CsShpCity :CsCityNull,
C+ :CsRemise :CsDiscNull )
C/End-Exec
SQL dynamique
On construit la commande au sein du programme. Exemples
....1....+....2....+....3....+....4....+....5....+....6....+....7. D SQLStmtStr S 256A C Eval SQLStmtStr = 'Delete From Customer + C Where ShpCity Is Null' C/Exec SQL C+ Execute Immediate :SQLStmtStr C/End-Exec
Utilisation de conditions introduites par l'opérateur
....1....+....2....+....3....+....4....+....5....+....6....+....7.
D SQLStmtStr S 256A
D InpSrchCnd S 256A
C* Get a search condition (as a string) from user input
C* and place the string in the InpSrchCnd variable.
.
.
.
C Eval SQLStmtStr = 'Delete From Customer +
C Where '
C Eval SQLStmtStr = SQLStmtStr + InpSrchCnd
C/Exec SQL
C+ Execute Immediate :SQLStmtStr
C/End-Exec
Traîtement en deux étapes : préparation et exécution
D SQLStmtStr S 256A
D InpSrchCnd S 256A
C* Get a search condition (as a string) from user input
C* and place the string in the InpSrchCnd variable.
.
.
.
C Eval SQLStmtStr = 'Delete From Customer +
C Where '
C Eval SQLStmtStr = SQLStmtStr + InpSrchCnd
C/Exec SQL
C+ Prepare DynSQLStmt
C+ From :SQLStmtStr
C/End-Exec
C If ( SQLCod = 0 ) And ( SQLWn0 = *BLANK )
C/Exec SQL
C+ Execute DynSQLStmt
C/End-Exec
C If ( SQLCod <> 0 ) Or ( SQLWn0 <> *BLANK )
C ExSr SQLError
C EndIf
C Else
C ExSr SQLError
C EndIf
Les commandes SQL suivant peuvent être exécutées dynamiquement
Alter Table Drop Call Grant Comment On Insert Commit Label On Create Collection Lock Table Create Index Revoke Create Procedure Rollback Create Table Set Transaction Create View Update Delete
Un "?" peut être utilisé pour désigner l'emplacement d'une variable
D SlcShpCity S 30A
D SQLStmtStr S 256A
C Eval SQLStmtStr = 'Delete From Customer +
C Where ShpCity = ?'
C/Exec SQL
C+ Prepare DynSQLStmt
C+ From :SQLStmtStr
C/End-Exec
C If ( SQLCod = 0 ) And ( SQLWn0 = *BLANK )
C* Get the selected city from user input and place
C* the value in the SlcShpCity variable.
.
.
.
C/Exec SQL
C+ Execute DynSQLStmt
C+ Using :SlcShpCity
C/End-Exec
C If ( SQLCod <> 0 ) Or ( SQLWn0 <> *BLANK )
C ExSr SQLError
C EndIf
C Else
C ExSr SQLError
C EndIf
Utilisation de curseurs
Les curseurs sont un moyen de récupérer plusieurs enregistrements dans une commande SQL statique incluse dans un programme. Voir Open et Close ci-dessous. Fetch est la lecture des données récupérées.
....1....+....2....+....3....+....4....+....5....+....6....+
D CustomerR DS
D CsNumCli 7P 0
D CsNomCli 30A
D CsShpCity 30A
D CsRemise 5P 3
D CsCityNull S 4B 0
D CsDiscNull S 4B 0
.
.
.
C/Exec SQL
C+ Declare CustomerCursor Cursor
C+ For Select *
C+ From Customer
C+ Order By NumCli
C/End-Exec
C/Exec SQL
C+ Open CustomerCursor
C/End-Exec
C* Repeat the following Fetch until no more rows or error...
C/Exec SQL
C+ Fetch Next
C+ From CustomerCursor
C+ Into :CsNumCli,
C+ :CsNomCli,
C+ :CsShpCity :CsCityNull,
C+ :CsRemise :CsDiscNull
C/End-Exec
C/Exec SQL
C+ Close CustomerCursor
C/End-Exec
Structures de Données et Tables
Exemples
....1....+....2....+....3....+....4....+....5....+....6....+
D CustomerR DS
D CsNumCli 7P 0
D CsNomCli 30A
D CsShpCity 30A
D CsRemise 5P 3
D CustomerI DS
D CsIDNull 4B 0
D CsNameNull 4B 0
D CsCityNull 4B 0
D CsDiscNull 4B 0
.
.
.
C/Exec SQL
C+ Select NumCli,
C+ Name,
C+ ShpCity,
C+ Remise
C+ Into :CustomerR :CustomerI
C+ From Customer
C+ Where NumCli = :SlcNumCli
C/End-Exec
Le Select Into résultant équivaut à :
C/Exec SQL C+ Select NumCli, C+ Name, C+ ShpCity, C+ Remise C+ Into :CsNumCli :CsIDNull, C+ :CsNomCli :CsNameNull, C+ :CsShpCity :CsCityNull, C+ :CsRemise :CsDiscNull C+ From Customer C+ Where NumCli = :SlcNumCli C/End-Exec