| 
SQL/400 et programmation en langage HLL(RPG, COBOL ,etc) --------------------------------------------------------- ORDRES SQL INCLUS DANS LE SOURCE DU LANGAGE RPG/400 et ILE/RPGIV -------------------- C/EXEC SQL C+ ORDRE SQL EN C+ FORMAT LIBRE C/END-EXEC COBOL: ------ EXEC SQL ORDRE SQL EN FORMAT LIBRE END-EXEC. |
|
Des paramètres d'exécution peuvent être indiqués SET OPTION (ce doit être la première instruction SQL) SET OPTION----+-ALWCPYDTA = copie des données admise ? *NO une copie des données n'est pas admise *YES une copie est admise quand cela est nécessaire *OPTIMIZE, à chaque fois que cela permet d'aller plus vite +-COMMIT = niveau de COMMIT *NONE hors contrôle de validation *CHG verrouillage des lignes modifiées *CS idem *CHG + une ligne lue (test de disponibilité) *ALL verrouillage de toutes les ligneslues et modifiées +-CONACC = concurrence d'accès *DFT mode de fonctionnement par défaut (cf QAQQINI) *WAIT Attendre (60s) que l'enregistrement se libère *CURCMT travailler avec la dernière version validée |
|
+-DATFMT = *ISO | *EUR | *DMY ... +-DATSEP = (un séparateur) +-TIMFMT = *ISO | *EUR | *HMC ...-----+ +-TIMSEP = (un séparateur) +-DBGVIEW = *STMT | *SOURCE +-USRPRF = *USER | *OWNER | *NAMING profil de référence pour les droits *USER, l'utilisateur ayant lancé le pgm *OWNER le propriétaire du pgm *NAMING si convention *SQL = *OWNER, sinon *USER +-DYNUSRPRF = *USER | *OWNER même notion pour les instructions dynamiques +-NAMING = *SYS | *SQL convention d'appellation |
|
+-RDBCNNMTH = nom RDB (enregistré par WRKRDBDIRE) connexion automatique à une base éloignée +-SRTSEQ séquence de classement *HEX l'EBCDIC fait référence *LANGIDSHR tenir compte de la langue, mais é <> ê *LANGIDUNQ tenir compte de la langue et e=é=è=ê +-LANGID code langage (FR par exemple) |
|
VARIABLES HOST -------------- UNE VARIABLE "HOST" est une variable du programme . SQL les reconnait car elles commencent par ":" ex :SALBRU, :NUMSTG, etc... une augmentation du salaire en fonction d'un coefficient, s'écrira : UPDATE personp1 set SALBRU = SALBRU * :augmentation where COEF = :coef SQL Communication area SQLCA ----------------------------- EN RPG c'est une data structure générée automatiquement par le précompilateur en RPG4, la meme zone est définie 2 fois : - une fois en nom sur 6 lettres (pour compatibilité RPG III) - une fois en nom long pour etre à la norme |
|
D* SQL Communications area en V5 D SQLCA DS D SQLCAID 8A INZ(X'0000000000000000') D SQLAID 8A OVERLAY(SQLCAID) D SQLCABC 10I 0 D SQLABC 9B 0 OVERLAY(SQLCABC) D SQLCODE 10I 0 D SQLCOD 9B 0 OVERLAY(SQLCODE) D SQLERRML 5I 0 D SQLERL 4B 0 OVERLAY(SQLERRML) D SQLERRMC 70A D SQLERM 70A OVERLAY(SQLERRMC) D SQLERRP 8A D SQLERP 8A OVERLAY(SQLERRP) D SQLERR 24A D SQLER1 9B 0 OVERLAY(SQLERR:*NEXT) D SQLER2 9B 0 OVERLAY(SQLERR:*NEXT) D SQLER3 9B 0 OVERLAY(SQLERR:*NEXT) D SQLER4 9B 0 OVERLAY(SQLERR:*NEXT) D SQLER5 9B 0 OVERLAY(SQLERR:*NEXT) D SQLER6 9B 0 OVERLAY(SQLERR:*NEXT) |
|
D SQLERRD 10I 0 DIM(6) OVERLAY(SQLERR) D SQLWRN 11A D SQLWN0 1A OVERLAY(SQLWRN:*NEXT) D SQLWN1 1A OVERLAY(SQLWRN:*NEXT) D SQLWN2 1A OVERLAY(SQLWRN:*NEXT) D SQLWN3 1A OVERLAY(SQLWRN:*NEXT) D SQLWN4 1A OVERLAY(SQLWRN:*NEXT) D SQLWN5 1A OVERLAY(SQLWRN:*NEXT) D SQLWN6 1A OVERLAY(SQLWRN:*NEXT) D SQLWN7 1A OVERLAY(SQLWRN:*NEXT) D SQLWN8 1A OVERLAY(SQLWRN:*NEXT) D SQLWN9 1A OVERLAY(SQLWRN:*NEXT) D SQLWNA 1A OVERLAY(SQLWRN:*NEXT) D SQLWARN 1A DIM(11) OVERLAY(SQLWRN) D SQLSTATE 5A D SQLSTT 5A OVERLAY(SQLSTATE) D* End of SQLCA En COBOl il faut déclarer par un INCLUDE : EXEC SQL INCLUDE SQLCA END-EXEC. |
|
01 SQLCA. 05 SQLCAID PIC X(8). 05 SQLCABC PIC S9(9) COMP-4. 05 SQLCODE PIC S9(9) COMP-4. 05 SQLERRM. 49 SQLERRML PIC S9(4) COMP-4. 49 SQLERRMC PIC X(70). 05 SQLERRP PIC X(8). 05 SQLERRD OCCURS 6 TIMES PIC S9(9) COMP-4. 05 SQLWARN. 10 SQLWARN0 PIC X. 10 SQLWARN1 PIC X. 10 SQLWARN2 PIC X. 10 SQLWARN3 PIC X. 10 SQLWARN4 PIC X. 10 SQLWARN5 PIC X. 10 SQLWARN6 PIC X. 10 SQLWARN7 PIC X. 05 SQLEXT PIC X(8). |
|
variables de SQLCA importantes: ------------------------------- +SQLCODE ou SQLCOD 0 = pas d'erreur >0 = ordre exécuté mais avec erreur (ex +100 = fin de fichier) <0 = erreur, ordre non exécuté +SQLERRMC Texte du message SQL d'erreur si SQLCODE<0 +SQLERRML longueur significative de SQLERRMC +SQLERRD ou SQLERR (ERREUR) 6 fois 4 octets binaires ==> en RPG4, COBOL et PLI une table ==> en RPG, aussi 6 zones de SQLER1 à SQLER6 |
|
SQLERRD(1) ou SQLER1 contient le N° message CPF si SQLCODE < à 0. SQLERRD(2) ou SQLER2 contient le N° message CPD si SQLCODE < à 0. SQLERRD(3) ou SQLER3 donne le nb de lignes affectées par un ordre UPDATE,DELETE, ou INSERT +SQLWARN ou SQLWRN (WARNING) 8 indications alpha contenant ' ' ou 'W' SQLWARN0 ou SQLWN0 contient 'W' si une des indications contient 'W' SQLWARN1 ou SQLWN1 contient 'W' si une colonne a été tronquée SQLWARN3 ou SQLWN3 contient 'W' si le nb de variables HOST est invalide SQLWARN4 ou SQLWN4 contient 'W' si un ordre PREPARE pour UPDATE ou DELETE ne contient pas la clause WHERE |
|
+SQLSTATE ou SQLSTT SQLSTATE est ammené à remplacer SQLCOD/SQLCODE (orientation ISO) SQLSTATE est composé de CINQ caractères (chiffres ou lettres) les deux premiers caractères représentent la classe 00 Sucess (terminé noprmalement) 01 Warning (SQLCOD positif) 02 No data (particulièrement 02000, équivalent à SQLCOD 100) 03 à ZZ Error (dans les faits 07 à 58) représentent une erreur grave (SQLCOD < 0) un etat SQLSTATE peut correspondre à plusieurs SQLCODE |
|
ORDRES SQL de gestion d'erreur ------------------------------ I-NOT FOUND---I I-CONTINUE-I WHENEVER ----I-SQLERROR----I----I-GOTO-----I I-SQLWARNING--I I-GO TO----I NOT FOUND => SQLCOD = +100 SQLWARNING => SQLCOD >0 et <> 100 SQLERROR => SQLCOD <0 (= MONMSG) ordre CONTINUE (instruction suivante) ou GOTO (GO TO) nom-de-label Label du programme en format du langage HLL |
|
Exemples: EXEC SQL WHENEVER SQLERROR GO TO GESTERREUR END-EXEC EXEC SQL WHENEVER SQLWARNING CONTINUE END-EXEc EXEC SQL WHENEVER NOT FOUND GOTO FINFICHIER END-EXEC Vous pouvez aussi utiliser SQLCOD (SQLCODE) avec les ordres IF , DOW/DOU , PERFORM -> IF SQLCOD = 0 -> DOU (ou PERFORM UNTIL) SQLCOD > 0 , etc ... |
|
ou bien, retrouver ces informations, plus clairement, par : GET DIAGNOSTICS exec,sql GET DIAGNOSTICS CONDITION 1 :message = MESSAGE_TEXT exec sql GET DIAGNOSTICS CONDITION 1 :etat = DB2_RETURNED_SQLSTATE | DB2_RETURNED_SQLCODE exec sql CALL procedure; exec sql GET DIAGNOSTICS :valretour = RETURN_STATUS; exec sql DELETE From fichier Where ...; exec sql GET DIAGNOSTICS :nbrdelignes = ROW_COUNT; |
|
SQL STATIQUE ---------------- INSERT C/EXEC SQL INSERT INTO SQLDB/STGTBL C+ (NUMSTG, NOM, PRENOM, AGENCE) C+ VALUES(:NOSTAG, :NMSTG, :PRESTG, :AGENCE) C/END-EXEC UPDATE C/EXEC SQL UPDATE SQLDB/STGTBL C+ SET NOM = :NMSTG, PRENOM = :PRESTG C+ WHERE NUMSTG = :NOSTAG C/END-EXEC Ou C/EXEC SQL UPDATE SQLDB/STGTBL C+ SET ROW = :DataStructure C+ WHERE NUMSTG = :NOSTAG C/END-EXEC |
|
DELETE C/EXEC SQL DELETE FROM SQLDB/STGTBL C+ WHERE NUMSTG = :NOSTAG C/END-EXEC SELECT (cette syntaxe n'est valide que si l'ordre ne permet de retrouver qu'UNE LIGNE à la fois) C/EXEC SQL SELECT NOM, PRENOM, AGENCE C+ INTO :NMSTG, :PRESTG, :AGENCE C+ FROM SQLDB/STGTBL C+ WHERE NUMSTG = :NOSTAG C/END-EXEC OU |
|
IENREG DS (en GAP3) I 1 15 NMSTG I 16 30 PRESTG I 31 320AGENCE C/EXEC SQL SELECT NOM, PRENOM, AGENCE C+ INTO :ENREG C+ FROM SQLDB/STGTBL C+ WHERE NUMSTG = :NOSTAG C/END-EXEC OU (DS externe utilisant la description du fichier) IENREG E DSSTGTBL * C/EXEC SQL SELECT * C+ INTO :ENREG C+ FROM SQLDB/STGTBL C+ WHERE NUMSTG = :NOSTAG C/END-EXEC |
|
D ENREG DS (en GAP4) D NMSTG 15 D PRESTG 15 D AGENCE 2 0 C/EXEC SQL SELECT NOM, PRENOM, AGENCE C+ INTO :ENREG C+ FROM SQLDB/STGTBL C+ WHERE NUMSTG = :NOSTAG C/END-EXEC OU (DS externe utilisant la description du fichier) D ENREG E DS EXTNAME(STGTBL) C/EXEC SQL SELECT * C+ INTO :ENREG C+ FROM SQLDB/STGTBL C+ WHERE NUMSTG = :NOSTAG C/END-EXEC |
|
ATTENTION, cette syntaxe suppose que les variables lues n'acceptent pas la valeur nulle. (en cas de valeur nulle vous recevrez un message SQL et SQLCOD <>0) Pour gérer le valeur nulle, écrivez : C/EXEC SQL SELECT NOM, PRENOM, AGENCE C+ INTO :NMSTG:NMSTGi, :PRESTG:PRESTGi, :AGENCE:AGENCEi C+ FROM SQLDB/STGTBL C+ WHERE NUMSTG = :NOSTAG C/END-EXEC NMSTGi, PRESTGi et AGENCEi étant des variables binaires sur 2 octets (en rpg "4B 0" ou "5I 0", en COBOL comp-4) si la variable binaire (SQL parle d'indicateur HOST) contient : 0 : la variable associée à un contenu significatif -1 : la variable contient la valeur nulle -2 : SQL a assigné la valeur nulle suite à une erreur de mappage. |
|
EXTRACTION DE DONNEES MULTIPLES -------------------------------- UTILISATION D'UN CURSEUR Un curseur est un fichier intermédiaire géneré par SQL, rempli par le résultat d'un ordre SELECT et destiné à être lu séquentiellement par l'ordre FETCH. 1/ déclaration du curseur ---------------------- C/EXEC SQL C+ DECLARE nomcur [SENSITIVE | INSENSITIVE] CURSOR FOR C+ SELECT * FROM SQLDB/STGTBL WHERE AGENCE = :AGENCE C+ [WITH HOLD | WITHOUT HOLD] C+ [FOR UPDATE OF NMSTG, PRESTG] C/END-EXEC |
|
Un curseur peut etre déclaré WITH HOLD ou WITHOUT HOLD s'il est déclaré WITH HOLD, il ne sera pas désactivé, meme en cas de COMMIT simple (sans paramètre). En effet, l'ordre SQL COMMIT possède un paramètre important HOLD : - Les ressources sont suspendues. Les curseurs actifs ne sont pas désactivés , les instructions SQL préparées sont conservées et toutes les ressources acquises pendant la transaction sont suspendues. (sans) - Les ressources ne seront pas suspendues. Les curseurs actifs sont désactivés sauf WITH HOLD , les instructions SQL préparées sont supprimées et les ressurces suspendues libérées. |
|
Un curseur peut etre déclaré "sensitif" ou non SENSITIVE CURSOR le curseur est construit dynamiquement, il reflète les modifications effectuées sur les données non encore lues de la base, par le pgm en cours ou éventuellement par d'autre,suivant le niveau d'isolation INSENSITIVE CURSOR le curseur est construit une fois pour toutes avec des données copiées. le pgm est insensible aux modification effectuées sur la base (y compris par lui). le SELECT ne doit pas contenir FOR UPDATE et ALWCPYDTA <> de *NO ASENSITIVE CURSOR le curseur sera sensitif ou pas suivant la requête et le degré d'optimisation (paramètre ALWCPYDTA) |
|
2/ ouverture du curseur -------------------- C/EXEC SQL C+ OPEN nomcur C/END-EXEC (SELECT exécuté et résultat placé dans le curseur) 3/ lecture séquentielle du curseur ------------------------------- A/ avec WHENEVER et GOTO (hélas !) B/ en utilisant SQLCOD (programmation structurée) C EXSR Lecture C DOW SQLCOD = 0 .... C EXSR lecture C ENDDO |
|
C LECTURE BEGSR C/EXEC SQL C+ FETCH nomcur INTO :ENREG C/END-EXEC C ENDSR 4/ mise à jour et suppression (éventuellement) --------------------------- C/EXEC SQL C+ UPDATE SQLDB/STGTBL C+ SET PRENOM = :PRESTG C+ WHERE CURRENT OF nomcur C/END-EXEC C/EXEC SQL C+ DELETE FROM SQLDB/STGTBL C+ WHERE CURRENT OF nomcur C/END-EXEC ATTENTION , il s'agit ici de manipuler le DERNIER enregistrement Lu |
|
5/ fermeture du curseur (dans tous les cas) -------------------- C/EXEC SQL C+ CLOSE nomcur C/END-EXEC EXEMPLE A partir d'un fichier produit affichage par sous-fichier du nombre de produits et de la quantité moyenne stockée par sous-famille. Le premier écran demande la famille de produit. Le deuxième écran affiche le sous-fichier. |
|
FICHIER ECRAN
---------------
A REF(PRODUIP1)
INDARA
A R TITRE
A 1 2'NOM-PGM'
A 1 18' Titre'
A DSPATR(UL)
A 1 68DATE
A EDTCDE(Y)
A R F1
A CA03(03 'exit')
A OVERLAY ERASE(SFL CLT)
A 4 4'FAMILLE DU PRODUIT:'
A FAMPRO R Y I 4 25
A 40 ERRMSG('Famille en erreur' 40)
A R SFL SFL
A SFAPRO R O 7 6
A NBPRO 6 0O 7 13
A MOYEN 9 2O 7 24
|
|
A EDTCDE(3) A 7 2'!' A DSPATR(HI) A R CTL SFLCTL(SFL) A OVERLAY A PROTECT A 30 SFLDSP A 30 SFLDSPCTL A N30 SFLCLR A 30 SFLEND A SFLSIZ(0029) A SFLPAG(0028) A SFLLIN(0008) A 6 2'Entète sous-fichier' A DSPATR(UL) |
|
PROGRAMME RPG ------------- Frpgsqle cf e workstn indds(indic) F sfile(sfl : rang) dIndic DS D f3 n overlay(indic : 3) D sfldsp n overlay(indic : 30) D erreur_famille n overlay(indic : 40) DRang s 4 0 C/EXEC SQL C+ DECLARE C1 CURSOR FOR C+ SELECT SFAPRO, COUNT(*), AVG(QTSPRO) C+ FROM PRODUIP1 WHERE FAMPRO = :FAMPRO C+ GROUP BY SFAPRO C/END-EXEC * * Corps du programme (boucle sur image 1) * C write TITRE |
|
C exfmt F1 C DOw not f3 C exsr Principal C exfmt F1 C ENDDO C eval *inlr = *on * * * Sous pgm traitement d'un produit * C Principal BEGSR * Ouverture curseur (le SELECT est exécuté) C/EXEC SQL C+ OPEN C1 C/END-EXEC C exsr LECTURE C IF SQLCOD <> 0 C eval erreur_famille = *on C ELSE C eval sfldsp = *off C WRITE CTL C eval sfldsp = *on |
|
C eval rang = 0 C DOu sqlcod <> 0 C eval rang = rang + 1 C write SFL C exsr LECTURE C ENDDO C exfmt CTL C ENDIF * fermeture du curseur * C/EXEC SQL C+ CLOSE C1 C/END-EXEC C ENDSR * * LECTURE SEQUENTIELLE DU CURSEUR * C LECTURE BEGSR C/EXEC SQL C+ FETCH C1 INTO :SFAPRO, :NBPRO, :MOYEN C/END-EXEC C ENDSR |
|
SQL/400 permet de manipuler plusieurs enregistrements à la fois par l'intermèdiaire de tableaux de structure: (V2R20). - en RPG : DATA STRUCTURE à occurences multiples - en COBOL : Table (OCCURS) Utilisation: ° INSERT--INTO --nom--------------------VALUES :var-host ! ! ! ! !--X------ROWS---! !-var--! ° FETCH--nom-curseur----------------------------INTO :var-host ! ! ! ! !-FOR---X------ROWS---! !-var--! |
|
SQLCA: SQLERRD(3) = nb d'enregistrements (lus ou insérés) SQLERRD(4) = lg enreg SQLERRD(5) = indicateur signalant si le dernier poste de la structure a été rempli par FETCH. En parallèle: Il est possible d'indiquer un facteur d'optimisation) (= OPTIMIZE sur OPNQRYF) SELECT ------------idem-----------------------------------------> ! ! !--OPTIMIZE-FOR x ROWS---------! (Pour un traitement par sous-fichier , indiquer SFLPAG.) |
|
CURSEURS FLOTTANTS : SQL donne la possibilité de se repositionner dans un curseur. DECLARE-------idem---------------------------------CURSOR--------> ! ! !----------------SCROLL-----! !-DYNAMIQUE-! SCROLL seul = en lecture pure DYNAMIQUE SCROLL = en mise à jour. (FOR UPDATE). ce qui permet d'utiliser FETCH de la manière suivante: FETCH--------------------------------curseur-------> !--NEXT--------------------! !--PRIOR-------------------! !--FIRST-------------------! !--LAST--------------------! !--BEFORE------------------! !--AFTER-------------------! !--CURRENT-----------------! !--RELATIVE----entier------! !-var-host--! |
|
MANIPULATION DES BLOB --------------------------- Vous pouvez maintenant manipuler vos variables HOST par un ordre SQL SET :variable = expression ou bien VALUE expression INTO :variable par exemple C/EXEC SQL C+ SET :Result = LEFT(:NOM, 10) CONCAT :PRENOM C/END-EXEC C/EXEC SQL C+ VALUE POSSTR(:RAISOC , 'Assurance') INTO :debut C/END-EXEC |
|
> Une colonne de type LOB peut-être manipulée par -son contenu (si votre langage supporte des variables aussi grandes) vous devez déclarer en RPG par: DMYBLOB S SQLTYPE(BLOB:500) ce qui génère : D MYBLOB DS D MYBLOB_LEN 10U 0 D MYBLOB_DATA 500A -vous pouvez utiliser un identifiant appelé "LOCATOR", qui permet : + de ne pas transférer les data dans le programme (donc sur le PC) + de faire des copies de fichiers ... |
|
vous devez déclarer en RPG par: D MYCLOB S SQLTYPE(CLOB_LOCATOR) ce qui génère : D MYCLOB S 10U 0 vous pouvez utiliser l'identifiant en lieu et place de la colonne par les nouvelles instructions SET et VALUE. C/EXEC SQL C+ VALUE POSSTR(:MYCLOB, 'formation') INTO :debut C/END-EXEC -vous pouvez utiliser un fichier appelé "FILE LOCATOR", qui permet : + de copier le contenu d'un LOB dans un fichier IFS + de renseigner un LOB à partir du contenu d'un fichier IFS. |
|
vous devez déclarer en RPG par: D MYFILE S SQLTYPE(CLOB_FILE) ce qui génère : D MYFILE DS D MYFILE_NL 10U 0 [lg du nom] D MYFILE_DL 10U 0 [lg des Data] D MYFILE_FO 10U 0 [file permission] * SQL génère SQFRD (2) ouverture en lecture * SQFCRT (8) création (erreur si existe déja) * SQFOVR(16) création si inexistant (ou écrasement) * SQFAPP(32) ouverture en ajout D MYFILE_NAME 255A [nom du fichier] Exemple en COBOL |
|
en Working Storage Section : 01 rapport USAGE IS SQL TYPE IS CLOB-FILE * SQL-FILE-READ * SQL-FILE-CREATE * SQL-FILE-OVERWRITE * SQL-FILE-APPEND puis en traitement move "RAPPORT.TXT" to rapport-NAME. move 11 to rapport-LENGTH. move SQL-FILE-OVERWRITE to rapport-FILE-OPTIONS. EXEC SQL SELECT rapport INTO :rapport FROM ... WHERE ... END-EXEC. ce traitement place copie du contenu de la colonne "rapport" dans le fichier "RAPPORT.TXT" qui est un fichier IFS. un ordre INSERT aurait renseigné la colonne par copie du fichier. |
|
Exemple en RPG-IV * un champs de type BLOB_FILE contient non pas une image * mais le chemin d'une image * a/ à lire lors d'un insert ou d'un update * b/ à écrire lors d'un select * * d image s SQLTYPE(BLOB_FILE) d articles e ds C/exec sql C+ DECLARE C1 CURSOR FOR SELECT noart FROM articles FOR UPDATE C+ OF image C/end-exec C/exec sql C+ open c1 C/end-exec c dou sqlcod <> 0 C/exec sql C+ fetch c1 into :noart C/end-exec |
|
c if sqlcod <> 0 c leave c endif c eval image_fo = SQFRD * les images sont dans /html/tools/GIF/(noart).gif c eval image_name = '/html/TOOLS/GIF/' + c %trimr(noart) + '.gif' c eval image_nl = %len(%trim(image_name)) * l'image est physiquement copié dans le champ de cet enregistrement C/exec sql C+ update articles set image = :image C+ where current of c1 C/end-exec c enddo C/exec sql C+ close c1 C/end-exec C/exec sql C+ commit C/end-exec c eval *inlr = *on |
|
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 Exemple en cobol: *------------------------ WORKING-STORAGE SECTION. *------------------------ 01 VAR pic X(150). EXEC SQL INCLUDE SQLCA END-EXEC. LINKAGE SECTION. 01 VARMODIF PIC X(30). 01 VARSELECT PIC X(25). |
|
*-------------------------------------------- PROCEDURE DIVISION USING VARMODIF VARSELECT. *-------------------------------------------- PGM. EXEC SQL WHENEVER SQLERROR GO TO PROBLEM END-EXEC. *-VARMODIF contient la modification à réaliser *-VARSELECT contient la sélection des enregistrements à modifier STRING "UPDATE SQLDB/STGTBL SET " VARMODIF " WHERE " VARSELECT DELIMITED BY SIZE INTO VAR. EXEC SQL EXECUTE IMMEDIATE :VAR END-EXEC. GO TO FIN. PROBLEM. DISPLAY "ERREUR PENDANT LA MISE A JOUR". FIN. STOP RUN. |
|
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 dans l' EXECUTE Exemple en RPG-IV: |
|
D requete s 512
D famille s 2 0
D fichier s 10 INZ('?????????')
*
* mise en place de l'ordre SQL dans requete
*
C eval requete = 'DELETE FROM SQLDB/' +
C fichier + ' WHERE FAMPRO = ?'
*
* préparation de l'ordre SQL sous le nom P1
*
C/EXEC SQL
C+ PREPARE P1 FROM :requete
C/END-EXEC
*
C for famille = 10 to 25
C/EXEC SQL
C+ EXECUTE P1 USING :FAMILLE
C/END-EXEC
C endfor
|
|
PREPARE et EXECUTE avec un ordre 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) Exemple RPGIV C *entry plist C parm critere C eval var = 'SELECT ZON1, ZON2 FROM FICHIER' C + ' WHERE ZON1 > ' + critere * * préparation de l'ordre SQL sous le nom P1 * C/EXEC SQL C+ PREPARE P1 FROM :VAR C/END-EXEC * |
|
* déclaration d'un curseur résultant de P1 * C/EXEC SQL C+ DECLARE C1 CURSOR FOR P1 C/END-EXEC * * ouverture du curseur * C/EXEC SQL C+ OPEN C1 C/END-EXEC * * traitement du curseur comme précédement * par l'ordre FETCH puis fermeture par CLOSE C EXSR lecture C if sqlcod <> 0 C eval *in40 = *on C else * préparation sous fichier (clear) C eval *in30 = *off C write CTL C eval *in30 = *on |
|
C eval rang = 0 * boucle sur toutes les lignes du curseur C dou sqlcod <> 0 C eval rang = rang + 1 C write SFL C exsr lecture C enddo C exfmt CTL C endif * Fermeture curseur C/EXEC SQL CLOSE C1 C/END-EXEC * * LECTURE SEQUENTIELLE DU CURSEUR * C lecture BEGSR C/EXEC SQL FETCH C1 INTO :ZONE1, :ZONE2 C/END-EXEC C ENDSR même exemple en COBOL : |
|
* *------------------------------ PROCEDURE DIVISION USING CRITERE. *------------------------------ PGM. * * mise en place de l'ordre SQL dans var * STRING "SELECT ZON1, ZON2 FROM FICHIER WHERE ZON1 >" CRITERE DELIMITED BY SIZE INTO VAR. * * préparation de l'ordre SQL sous le nom P1 * EXEC SQL PREPARE P1 FROM :VAR END-EXEC. * * déclaration d'un curseur résultant de P1 * |
|
EXEC SQL DECLARE C1 CURSOR FOR P1 END-EXEC. * * ouverture du curseur * EXEC SQL OPEN C1 END-EXEC. * * traitement du curseur comme précédement * par l'ordre FETCH puis fermeture par CLOSE * * (même principe) Si les variables extraites par l'ordre SELECT ne sont pas toujours les mêmes cette technique n'est plus utilisable Il faut passer par l'ordre DESCRIBE qui place dans SQLDA la liste des zones et leur définition après un ordre PREPARE. |
|
Il faudra allouer de l'espace mémoire de manière dynamique (pointeurs) 1°/ Mise en place de l'ordre SELECT dans VAR 2°/ PREPARE p1 FROM :var 3°/ Allocation d'espace mémoire pour SQLDA (suivant nbre de colonnes) 4°/ DESCRIBE p1 INTO :sqlda 5°/ DECLARE c1 CURSOR FOR p1 6°/ OPEN c1 7°/ Allocation d'espace mémoire (devant recevoir un enregistrement) et stockage de l'adresse dans SQLDA 8°/ jusqu'à EOF - FETCH c1 USING DESCRIPTOR :sqlda traitement de la ligne lue - fin jusqu'à. 9°/ CLOSE c1. |
|
FONCTIONS DE VALIDATION INVALIDATION -------------------------------------- 1/ on indique le niveau de validation avec lequel on travail - sur la commande de compilation (voir plus bas) 2/ on valide (ou pas) par les ordres COMMIT Validation ROLLBACK Invalidation ATTENTION, l'ordre SQL COMMIT possède un paramètre important HOLD : - Les ressources sont suspendues. Les curseurs actifs ne sont pas désactivés , les instructions SQL préparées sont conservées et toutes les ressources acquises pendant la transaction sont suspendues. |
|
(sans) - Les ressources ne seront pas suspendues. Les curseurs actifs sont désactivés, les instructions SQL préparées sont supprimées et les ressources suspendues libérées. - Un curseur déclaré WITH HOLD ne sera pas désactivé 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 (V5R20) Cette notion permet de matérialiser des étapes dans une transaction offrant la possibilité de revenir à une étape précise et non au début de la transaction en cas de ROLLBACK. |
|
un point de reprise est posé par l'instruction SAVEPOINT (un SEUL AS/400) UPDATE client ... ; SAVEPOINT MAJ ON ROLLBACK RETAIN CURSORS ; DELETE FROM CLIENTS ... ; DELETE FROm COMMANDES ; SAVEPOINT DLT ON ROLLBACK RETAIN CURSORS ; INSERT INTO ... ; IF xxx ROLLBACK TO SAVEPOINT MAJ ELSE RELEASE SAVEPOINT ; 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 cataloguées [STORED PROCEDURES] : ----------------------------------------------- SQL permet maintenant de demander l'exécution d'un programme sur un serveur distant (normalement devant réaliser une action sur la base de ce serveur) Cette fonction n'est pas encore normalisée (mais largement répandue). - certains SGBD n'autorisent que des procédures SQL. - DB2/400 autorise l'appel à n'importe quel programme. + avec passage de paramètres. + ces paramètres pouvant être renseignés par le pgm distant. Syntaxe CALL nom-procédure---------------------------------------------- | | |-(param1,[param2,...])-| |
|
nom-procédure = nom du pgm à appeler ou procédure telle que définie par DECLARE PROCEDURE (voir ci-dessous) la procédure est recherchée dans la collection en cours pour qualifier : BIB/PGM avec la convention système BIB.PGM avec la convention SQL voir: STRSQL paramètre NAMING CRTSQLxxx paramètre OPTION paramx = paramètre envoyé (ou reçu) qui peut être + une constante + une variable du pgm (:var) (par défaut :var est en entrée/sortie avec SQL statique en entrée (envoyée) avec SQL dynamique) + NULL + une valeur spéciale (CURRENT DATE/TIME/TIMEZONE , SERVER) (USER) |
|
Il est possible de déclarer une procédure afin de préciser certaines options DECLARE -nom-proc-PROCEDURE------------------------------------------------> | |-IN----| | |-(param---OUT-----type de données(lg)-----| |-INOUT-| |-EXTERNAL------------------| >------------------------------LANGUAGE--langage--------------------------- |-EXTERNAL NAME-nom-externe-| |-SIMPLE CALL-----------| |-SIMPLE CALL WITH NULL-| définition des paramètres : IN le paramètre est uniquement reçu par la procédure OUT le paramètre est renvoyé par la procédure INOUT le paramètre est utilisé dans les deux sens type de donnée(lg) : idem CREATE TABLE CHAR(xx) INT(lg, dec) etc ... |
|
EXTERNAL NAME permet de définir le nom réel du pgm sur le serveur, si cette clause n'est pas renseignée c'est le nom de procédure qui est utilisé. LANGUAGE permet de définir le langage d'origine du pgm à exécuter C, CL, COBOL, COBOLLE, RPG, RPGLE, REXX, ... ceci est obligatoire avec une procédure REXX si cette clause n'est pas utilisé le server recherche l'attribut du pgm et par défaut le considère comme un pgm C. SIMPLE CALL WITH NULL signifie que le pgm doit recevoir les indicateurs indiquant les valeurs nulles (254 param maxi) SIMPLE CALL le pgm ne reçoit pas ces indicateurs (255 param maxi) l'instruction DECLARE PROCEDURE doit être utilisée pour appeler des procédures REXX, et doit être écrite AVANT l'instruction CALL. |
|
Particularité de SQL : il ne reconnait pas les membres.
vous pouvez quand même outrepasser en utilisant les procédures :
EXEC SQL
CALL QSYS.QCMDEXC ('OVRDBF FIC MBR(xxx) OVRSCOPE(*JOB)',
0000000035,00000)
END-EXEC.
Attention
le point (dans QSYS.CMDEXC) doit être le qualifiant en cours
la virgule (dans 0000000035,00000) doit être la marque décimale.
Cette solution peut-être utilisée aussi avec ODBC.
|
|
Avec JDBC/ODBC vous pouvez même renvoyer un groupe d'enregistrements en une seule fois : Dans la procédure appelée, ajoutez : SET RESULT SETS ----------------------------------------------------- ! ! !-----CURSOR nomcurseur-----------------! ! ! ! ! !--ARRAY nomdetableau -----FOR x ROWS---- Le curseur indiqué (il doit être ouvert) ou l'ensemble des enregistrements placés dans le tableau (ou la table en COBOL) représentent les valeurs en retour retournées au micro. Cet ordre n'est valide qu'avec un appel depuis ODBC/JDBC avant la V7 |
|
Vous pouvez enfin préparer tout cela à l'avance en déclarant une fois pour toute la procédure et ses particularités ( ce qui est conseillé ) CREATE PROCEDURE nom-proc---(même syntaxe que DECLARE ---)----> ---------------------------------------------> ! ! !--DYNAMIC RESULT SETS---n-----------! --- PARAMETER STYLE GENERAL ------------. n définit le nombre de "result sets" retournés (si > à 1) La définition est enregistrée dans les fichiers SYSPROCS et SYSPARMS du catalogue. |
|
Depuis la version 7 de l'OS, on peut récupérer le RESULT SET d'une procédure avec le scénario suivant(si le procédure ne retourne qu'un seul RESULT SET) CALL PROCxxx si SQLCODE = +466 // il y a un jeu de résultat retourné ASSOCIATE LOCATORS (:RS1) WITH PROCEDURE PROCxxx // :RS1 doit être déclaré SQLTYPE(RESULT_SET_LOCATOR) ALLOCATE C1 CURSOR FOR RESULT SET :RS1 |
|
Exemple : D nom_du_jour s 10 D date_du_jour s 10 D RS1 S SQLTYPE(RESULT_SET_LOCATOR) /free exec sql CALL FREE03; if SQLCODE = +466; exec sql ASSOCIATE LOCATORS (:RS1) WITH PROCEDURE FREE03; exec sql ALLOCATE C1 CURSOR FOR RESULT SET :RS1; exec sql FETCH c1 into :nom_du_jour , :date_du_jour; dow sqlcode = 0; // traitement des variables lues... exec sql FETCH c1 into :nom_du_jour , :date_du_jour; ENDDO; exec sql CLOSE C1; ENDIF; *inlr = *on; /end-free |
|
Fonctions Définies par l'utilisateur (V4R40) : ------------------------------------------------ une fonction est un programme ou une procédure dans un programme de service enregistré(e) dans les catalogues SQL par CREATE FUNCTION. par exemple : CREATE FUNCTION AF4TEST/DT8_CHAR8 (DEC(8 , 0) ) RETURNS CHAR(8) EXTERNAL NAME 'AF4TEST/DT_SQL(DT8_CHAR8)' (1) PARAMETER STYLE GENERAL (2) RETURNS NULL ON NULL INPUT ; (3) (1) fait référence à DT8_CHAR8 dans DT_SQL (2) le passage de paramètres se fait sans gestion de la val. nulle (3) la fonction retourne nul si un des argument est nul (il n'y aura pas d'appel) voici le source RPG de la procédure associée à cette fonction : |
|
H nomain * prototype ========================================================= D dt8_char8 pr 8 D 8 0 * fonction ========================================================== Pdt8_char8 b export d pi 8 d dt8_recue 8 0 d date s D datfmt(*dmy/) c *iso test(d e) dt8_recue c if %error c return '01/01/40' c else c *iso move dt8_recue date c return %char(date) c endif p e la création se réalise par : |
|
CRTRPGMOD MODULE(DT8_CHAR8) et CRTSRVPGM SRVPGM(DT_SQL) MODULE(DT8_CHAR8) EXPORT(*ALL) ___________________________________________________________________________ cela permet maintenant de passer des ordres SQL comme : [soit un fichier client avec DATCRT DEC(8 , 0) ] SELECT * FROM CLIENTS WHERE DT8_CHAR8(DATCRT) <> '01/01/40' SELECT * FROM CLIENTS WHERE DATE( DT8_CHAR8(DATCRT) ) = current date .................................................................. : : : la fonction SQL date attend une chaîne de caractères au format : : du JOB, soit en interactif 'JJ/MM/AA'. : : : :................................................................: |
|
Deuxième exemple retournant une date au format caractère en ISO d dt8_char10 pr 10 d 8 0 Pdt8_char10 b export d pi 10 d dt8_recue 8 0 d date s D datfmt(*iso) c *iso test(d e) dt8_recue c if %error c return '0001-01-01' c else c *iso move dt8_recue date c return %char(date) c endif c P e |
|
déclaration : CREATE FUNCTION AF4TEST/DT8_CHAR10 (DEC(8 , 0) ) RETURNS CHAR(10) EXTERNAL NAME 'AF4TEST/DT_SQL(DT8_CHAR10)' PARAMETER STYLE GENERAL RETURNS NULL ON NULL INPUT ; lors de la déclaration de la fonction vous pouvez convertir (caster) un/les argument(s) ou la valeur retour CREATE FUNCTION AF4TEST/DT8_DATE (DEC(8 , 0) ) RETURNS DATE CAST FROM CHAR(10) EXTERNAL NAME 'AF4TEST/DT_SQL(DT8_CHAR10)' PARAMETER STYLE GENERAL RETURNS NULL ON NULL INPUT ; ce qui permet directement : SELECT * FROM CLIENTS WHERE DT8_DATE(DATCRT) = current date |
|
Vous pouvez aussi créer des fonctions à l'aide du PL/SQL (ou en full SQL) à condition d'avoir le compilateur C, avant la V5R10. En effet une fonction écrite complétement en SQL génère un source C contenant du SQL (dans QTEMP) et lance la commande CRTSQLCI afin de créer un programme de service. exemple : create function af4test/dec_franc_euros (VALEUR decimal(9, 2)) returns decimal(9, 2) LANGUAGE SQL RETURNS NULL ON NULL INPUT BEGIN DECLARE VALEUROS DECIMAL ( 9 , 2 ) ; SET VALEUROS = VALEUR / 6.55957; RETURN VALEUROS ; END; C'est le même langage que pour l'ordre CREATE PROCEDURE. |
|
et bien évidement la fonction inverse : create function af4test/dec_euro_francs (VALEUR decimal(9, 2)) returns decimal(9, 2) LANGUAGE SQL RETURNS NULL ON NULL INPUT BEGIN (1) (3) DECLARE VALFRANCS DECIMAL ( 9 , 2 ) ;(2) (4) SET VALFRANCS= VALEUR * 6.55957; (5) RETURN VALFRANCS ; END; (1) le corps de la fonction est encadré de BEGIN - END; (2) chaque ligne se termine par ";" (3) les déclarations se font par DECLARE (même types que CREATE TABLE) (4) les manipulations se font par SET ou VALUES (5) RETURN termine la fonction en indiquant la valeur retournée. |
|
En ce qui concerne les UDT ,francs et euro par exemple, le plus simple est d'écrire des fonctions. une fonction de conversion francs/euros une fonction de conversion euros/francs create function af4test/franc_euros (VALEUR FRANCS) returns EUROS LANGUAGE SQL RETURNS NULL ON NULL INPUT STATIC DISPATCH BEGIN DECLARE VALEUROS DECIMAL ( 9 , 2 ) ; SET VALEUROS = CAST(VALEUR as decimal(9,2) ) / 6.55957; RETURN CAST(VALEUROS as euros); END; L'option STATIC DISPATCH est obligatoire quand une fonction manipule des UDT |
|
create function af4test/euro_francs (VALEUR EUROS) returns FRANCS LANGUAGE SQL RETURNS NULL ON NULL INPUT STATIC DISPATCH BEGIN DECLARE VALFRANCS DECIMAL ( 9 , 2 ) ; SET VALFRANCS= cast(VALEUR as decimal(9,2) ) * 6.55957; RETURN cast(VALFRANCS as FRANCS); END; > select * from voitures where prixe = prixf Opérandes de l'opérateur = incompatibles. > select * from voitures where prixe <> franc_euros(prixf) donne un résultat , ainsi que > update voitures set prixe = franc_euros(prixf) where prixe = cast(0 as euros) |
|
il vous faut aussi, refédinir toutes les fonctions SUM, AVG, etc... on parle alors de fonctions sourcées.(parlerons nous de surcharge ??) il peut donc y avoir plusieurs fonctions ayant le même nom, si elles acceptent des types de données différents.(ou bien de polymorphisme) SQL choisi la bonne fonction, suivant le type manipulé. create function af4test/SUM (francs) returns francs source QSYS2/SUM (decimal() ) ; create function af4test/AVG (francs) returns francs source QSYS2/AVG (decimal() ) ; create function af4test/MAX (francs) returns francs source QSYS2/MAX (decimal() ) ; create function af4test/MIN (francs) returns francs source QSYS2/MIN (decimal() ) ; |
|
Toutes les fonctions SQL sont réputées pour être stockées dans QSYS2. (ne cherchez pas un programme physique, il n'y en a pas) toutes vos fonctions, tous vos types : 1/ peuvent être qualifiés, 2/ seront recherchés dans *LIBL, sinon. (à condition que vous soyez en convention système) 3/ vous pouvez préciser le chemin au préalable par: SET PATH ..... et retrouver cette valeur par le registre CURRENT_PATH . |
|
COMMANDES CRTSQLxxx (RPG,CBL,C,PLI = OPM ------------------------ RPGI, CBLI = ILE) PARAMETRES IMPORTANTS COMMIT *NONE pas de contrôle de validation (DFT) *CHG contrôle de validation actif *CS *ALL (même signification que sur SET OPTION) Attention la valeur par défaut (*CHG), provoquera une erreur à l'exécution si vos fichiers ne sont pas journalisés. |
|
OPTION *SRC liste du Source des ordres SQL (automatique en cas d'erreur) (DFT) *NOSRC pas de liste source *XREF références croisees (DFT) *NOXREF pas de références croisees (DFT) *SYS convention d'appellation système (BIBLI/TABLE) *SQL convention d'appellation SQL (BIBLI.TABLE) DATFMT() DATSEP() format des variables DATE/HEURE dans la base. TIMFMT() TIMSEP() RDB() Choix de la base DRDA (CONNECT automatique) |
