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