Le web de Dominique Guebey – IBM AS/400 iSeries

Page : http://www.dg77.net/tekno/as400/db2emb.htm


   D o m i n i q u e   G u e b e y    J u n g l e     IBM AS/400 iSeries

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
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. Celà 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