Embedded SQL & RPG

Intégration du SQL au FreeFormRPG


Il vous faut être, si possible, à jour quant à la syntaxe SQL

Embedded SQL


Il s'agit d'embarquer du SQL à l'intérieur du langage RPG qui est dit langage Hôte (Host)

Le type de source doit être SQLRPGLE.


il y a un pré-compilateur qui s'occupe des ordres SQL (il doit donc les reconnaitre) puis lance la compilateur RPG s'il n'y a pas d'erreur

 

un ordre SQL est encadré de EXEC SQL et de ; ->

un ordre SQL à la fois

 

Des paramètres d'exécution peuvent être indiqués par  SET OPTION    (ce doit être la première instruction SQL)





Des variables RPG peuvent être utilisées dans les ordres SQL

On parle de variables HOST

Elles sont reconnues car elles commencent par : dans le SQL



Vous pouvez avoir un retour sur chaque ordre SQL

Avec la structure générée automatiquement par le précompilateur : SQLCA

en RPG4, la même zone est définie 2 fois :

 
  // SQL COMMUNICATION AREA                                            
         DCL-DS SQLCA;                              
           SQLCAID CHAR(8) INZ(X'0000000000000000');
           SQLAID CHAR(8) OVERLAY(SQLCAID);         
           SQLCABC INT(10);                         
           SQLABC BINDEC(9) OVERLAY(SQLCABC);       
           SQLCODE INT(10);                         
           SQLCOD BINDEC(9) OVERLAY(SQLCODE);       
           SQLERRML INT(5);                         
           SQLERL BINDEC(4) OVERLAY(SQLERRML);      
           SQLERRMC CHAR(70);                       
            SQLERM CHAR(70) OVERLAY(SQLERRMC);      
           SQLERRP CHAR(8);                         
            SQLERP CHAR(8) OVERLAY(SQLERRP);        
           SQLERR CHAR(24);                         
            SQLER1 BINDEC(9) OVERLAY(SQLERR:*NEXT); 
            SQLER2 BINDEC(9) OVERLAY(SQLERR:*NEXT); 
            SQLER3 BINDEC(9) OVERLAY(SQLERR:*NEXT); 
            SQLER4 BINDEC(9) OVERLAY(SQLERR:*NEXT); 
            SQLER5 BINDEC(9) OVERLAY(SQLERR:*NEXT); 
            SQLER6 BINDEC(9) OVERLAY(SQLERR:*NEXT); 
            SQLERRD INT(10) DIM(6) OVERLAY(SQLERR); 
           SQLWRN CHAR(11);                         
            SQLWN0 CHAR(1) OVERLAY(SQLWRN:*NEXT);   
            SQLWN1 CHAR(1) OVERLAY(SQLWRN:*NEXT);   
            SQLWN2 CHAR(1) OVERLAY(SQLWRN:*NEXT);   
            SQLWN3 CHAR(1) OVERLAY(SQLWRN:*NEXT);   
            SQLWN4 CHAR(1) OVERLAY(SQLWRN:*NEXT);   
            SQLWN5 CHAR(1) OVERLAY(SQLWRN:*NEXT);   
            SQLWN6 CHAR(1) OVERLAY(SQLWRN:*NEXT);   
            SQLWN7 CHAR(1) OVERLAY(SQLWRN:*NEXT);   
            SQLWN8 CHAR(1) OVERLAY(SQLWRN:*NEXT);   
            SQLWN9 CHAR(1) OVERLAY(SQLWRN:*NEXT);   
            SQLWNA CHAR(1) OVERLAY(SQLWRN:*NEXT);   
            SQLWARN CHAR(1) DIM(11) OVERLAY(SQLWRN);
           SQLSTATE CHAR(5);                        
            SQLSTT CHAR(5) OVERLAY(SQLSTATE);       
         END-DS SQLCA;


Variables de SQLCA importantes :

SQL STATIQUE

EXEC SQL  INSERT INTO SQLDB/STGTBL
            (NUMSTG, NOM, PRENOM, AGENCE)
          VALUES(:NOSTAG, :NMSTG, :PRESTG, :AGENCE);

Update 

EXEC SQL  UPDATE SQLDB/STGTBL
           SET NOM = :NMSTG,
               PRENOM = :PRESTG
          WHERE NUMSTG = :NOSTAG;

   ou bien 

EXEC SQL  UPDATE SQLDB/STGTBL
           SET ROW = :datastructure
          WHERE NUMSTG = :NOSTAG;


Delete

EXEC SQL  DELETE FROM SQLDB/STGTBL
            WHERE NUMSTG = :NOSTAG;


Select

(cette syntaxe n'est valide que si l'ordre ne permet de retrouver qu'UNE LIGNE à la fois)

DCL-S  nmstg char(15);
DCL-S  prestg char(15);
DCL-S  agence packed(2:0);

EXEC SQL  SELECT NOM, PRENOM, AGENCE
           INTO :NMSTG, :PRESTG, :AGENCE
          FROM SQLDB/STGTBL
          WHERE NUMSTG = :NOSTAG;


   ou bien 

DCL-DS DSstagiaire;
  nom char(15);
  prenom char(15);
  agence packed(2:0);
END-DS;

EXEC SQL  SELECT NOM, PRENOM, AGENCE
           INTO :DSstagiaire
          FROM SQLDB/STGTBL
          WHERE NUMSTG = :NOSTAG;


ou bien 

DCL-DS DSstagiaire EXTNAME('STGTBL') END-DS;

EXEC SQL  SELECT *
           INTO :DSstagiaire
          FROM SQLDB/STGTBL
          WHERE NUMSTG = :NOSTAG;

ou encore 

DCL-DS DSstagiaire EXTNAME('STGTBL') END-DS;
DCL-DS DSagence EXTNAME('AGETBL') END-DS;

EXEC SQL  SELECT S.*, A.*
           INTO :DSstagiaire , :DSagence 
          FROM SQLDB/STGTBL S join SQLDB/AGETBL A
                ON S.AGENCE = A.AGENCE
          WHERE NUMSTG = :NOSTAG;


Valeur Nulle

ATTENTION, cette syntaxe suppose que les variables lues n'acceptent  pas la valeur nulle.

     (en cas de valeur nulle vous recevrez un message SQL0305 et SQLCOD <>0)

      Pour gérer la valeur nulle, écrivez :

DCL-S  nmstg char(15);
DCL-S  prestg char(15);
DCL-S  agence packed(2:0);
// variables indicateurs
DCL-S  nmstgi int(5);
DCL-S  prestgi int(5);
DCL-S  agencei int(5);

EXEC SQL  SELECT NOM, PRENOM, AGENCE
           INTO :NMSTG:NMSTGi, :PRESTG:PRESTGi, :AGENCE:AGENCEi
          FROM SQLDB/STGTBL
          WHERE NUMSTG = :NOSTAG;

      ou bien

DCL-DS DSstagiaire;
  nom char(15);
  prenom char(15);
  agence packed(2:0);
END-DS;
// variables indicateurs
DCL-S WstagiaireI INT(5) DIM(3);

EXEC SQL  SELECT NOM, PRENOM, AGENCE
           INTO :DSstagiaire:WstagiaireI                      
          FROM SQLDB/STGTBL
          WHERE NUMSTG = :NOSTAG;

 

      ou encore

DCL-DS DSstagiaire;
  nom char(15);
  prenom char(15);
  agence packed(2:0);
END-DS;
// variables indicateurs
DCL-DS DSstagiaireI;
  nmstgi int(5);
  presti int(5);
  agencei int(5);
  WstagiairesI INT(5) DIM(3) POS(1);
END-DS;
EXEC SQL  SELECT NOM, PRENOM, AGENCE
           INTO :DSstagiaire:WstagiaireI                      
          FROM SQLDB/STGTBL
          WHERE NUMSTG = :NOSTAG;

NMSTGi, PRESTGi et AGENCEi étant des variables binaires sur 2 octets  (5,0 Integer)

Chaque variable binaire (SQL parle d'indicateur HOST) pouvant contenir :

Variables larges (BLOB & CLOB)

Pour lire des variables BLOB (binaires) ou CLOB (caractère)

Exemple avec le résultat de CALL CREATE_SQL_SAMPLE('DB2SAMPLE')

BLOB CLOB 
EXEC SQL  DECLARE nomcur [SENSITIVE | INSENSITIVE] CURSOR FOR
           SELECT * FROM SQLDB/STGTBL WHERE AGENCE = :AGENCE
            [WITH HOLD | WITHOUT HOLD]
            [FOR UPDATE OF NMSTG, PRESTG] ;

SQL DYNAMIQUE


Les ordres SQL (ou certains) ne sont plus compilés par le pré-compilateur mais interprètés et exécutés au moment de l'exécution du programme.


EXECUTE IMMEDIATE

exécution dynamique d'un ordre SQL 

 DCL-S requete CHAR(512);                        
 DCL-S famille PACKED(2:0);                     
 DCL-S fichier CHAR(10) INZ('nomfichier'); 

//                                                                    
// mise en place de l'ordre SQL dans requete                          
//                                                                   
  requete = 'DELETE FROM SQLDB/' + 
            fichier +  ' WHERE FAMPRO = ' + %char(famille); 
 EXEC SQL                                                            
   EXECUTE IMMEDIATE :requete;



PREPARE et EXECUTE

Si un ordre SQL dynamique doit être exécuté plusieurs fois il est plus performant de demander à SQL de
l'interpréter une fois par PREPARE et de demander son exécution x fois par EXECUTE.

Il faut donner un nom au résultat du PREPARE et utiliser ce nom dans l'ordre EXECUTE.

Il n'est pas possible d'utiliser des variables "HOST" dans l'ordre PREPARE.

Celles ci seront remplacées par "?" et on utilisera les variables via USING dans EXECUTE 

 DCL-S requete CHAR(512);                        
 DCL-S famille PACKED(2:0);                     
 DCL-S fichier CHAR(10) INZ('nomfichier'); 

//                                                                    
// préparation puis exécution                         
//                                                                   
  requete = 'DELETE FROM SQLDB/' + 
            fichier +  ' WHERE FAMPRO = ?'; 
 EXEC SQL                                                            
   PREPARE P1 FROM :requete;

 FOR famille = 10 to 25;
  EXEC SQL                                                            
    EXECUTE P1 USING :famille;
 ENDFOR;



PREPARE avec un Select

Ceci n'est possible que si les variables extraites par l'ordre SELECT sont TOUJOURS les mêmes.
(Même nombre de variables, même définition)

 DCL-S requete CHAR(512);                        
 DCL-PI *N ;                     
   critere char(2); 
 END-PI;

//                                                                    
// préparation puis exécution dans le curseur                        
//                                                                   
  requete = 'Select nocli, raisoc From CLIENTS WHERE DEPART > '
             +  critere; 
 EXEC SQL                                                            
   PREPARE P1 FROM :requete;
 EXEC SQL                                                            
    DECLARE C1 CURSOR FOR P1;

 EXEC SQL
    OPEN C1;

// ensuite FETCH et CLOSE comme un curseur "normal"

ou bien

 DCL-S requete CHAR(512);                        
 DCL-PI *N ;                     
   critere char(2); 
 END-PI;

//                                                                    
// préparation puis exécution dans le curseur                        
//                                                                   
  requete = 'Select nocli, raisoc From CLIENTS WHERE DEPART > ? ';

 EXEC SQL                                                            
   PREPARE P1 FROM :requete;
 EXEC SQL                                                            
    DECLARE C1 CURSOR FOR P1;

 EXEC SQL
    OPEN C1 Using :critere;

Prepare et performances

En général un ordre statique est plus performant, car traité par le pré-compilateur qui créé un plan d'accès (stratégie d'optimisation prévue) stocké dans l'objet *PGM
(on peut le voir par PRTSQLINF)

sauf :



Fonctions de Validation/Invalidation


cela permet de mettre en place la notion de transaction (je fais tout ou je ne fais rien, on dit aussi Atomiser)

Ce sont les ordres Commit et Rollback qui définissent la frontière

ils représentent la fin d'une transaction ET le début de la suivante

Sauf à utiliser la notion de SAVEPOINT, permettant de faire plus granulaire :

Cette notion permet de matérialiser des étapes dans une transaction, offrant la possibilité de revenir à un point précis
et non au début de la transaction, en cas de ROLLBACK. 

// un point de reprise est posé par l'instruction SAVEPOINT (un SEUL système) 

EXEC SQL         
 UPDATE client set dep = 44 where nocli = 1;    

EXEC SQL 
 SAVEPOINT MAJ ON ROLLBACK RETAIN CURSORS; 

EXEC SQL 
 DELETE FROM COMMANDES where nocli = 999; 
EXEC SQL 
 DELETE FROM CLIENTS where nocli = 999;
 
EXEC SQL
 SAVEPOINT DLT ON ROLLBACK RETAIN CURSORS ; 

EXEC SQL 
 INSERT INTO LOG ... ; 
 
IF SQLCODE > 0; // INSERT n'a pas marché
 EXEC SQL
  ROLLBACK TO SAVEPOINT MAJ; // annule les 2 DELETE, pas l'UPDATE
ELSE;
 EXEC SQL
  RELEASE SAVEPOINT ;
ENDIF;

ON ROLLBACK RETAIN CURSORS, permet de garder le(les) curseur(s) ouverts
ON ROLLBACK RETAIN LOCKS , permet de garder les verrouillages/ligne


RELEASE SAVEPOINT, libère (détruit) le point de reprise

Procédures stockées

cela permet de demander l'exécution d'un programme sur le serveur ( distant pour ODBC, JDBC) devant réaliser une série d'actions sur la base.

EXEC SQL
  CALL PROC01 (:P1 , :P2);

Compilation