Difference between revisions of "SQLRPGLE"
From MidrangeWiki
(→SQL Directives) |
(→Sample Embedded SQL program) |
||
Line 82: | Line 82: | ||
== Sample Embedded SQL program == | == Sample Embedded SQL program == | ||
+ | ===V5R1-V5R3=== | ||
<pre> | <pre> | ||
H ActGrp(*CALLER) | H ActGrp(*CALLER) | ||
Line 177: | Line 178: | ||
C/END-EXEC | C/END-EXEC | ||
/free | /free | ||
+ | Select; | ||
+ | When sqlstt='00000'; | ||
+ | // cursor was closed, normal | ||
+ | ReturnVar=*on; | ||
+ | Other; | ||
+ | // alert the troops! | ||
+ | ReturnVar=*off; | ||
+ | EndSl; | ||
+ | return ReturnVar; | ||
+ | /end-free | ||
+ | P CloseCursor E | ||
+ | </pre> | ||
+ | ===V5R4+=== | ||
+ | '''SQL is now supported in freeform''' | ||
+ | <pre> | ||
+ | H ActGrp(*CALLER) | ||
+ | H DftActGrp(*NO) | ||
+ | |||
+ | D OpenCursor PR n | ||
+ | D FetchCursor PR n | ||
+ | D CloseCursor PR n | ||
+ | |||
+ | D MyLib s 10a | ||
+ | D MyFile s 10a | ||
+ | |||
+ | /free | ||
+ | *inlr=*on; | ||
+ | if not OpenCursor(); | ||
+ | // perform error routine to alert the troops | ||
+ | // ... | ||
+ | Else; | ||
+ | Dow FetchCursor(); | ||
+ | // putting the fetchcursor on the do loop allows the user of | ||
+ | // iter, and thus iter will not perform an infinite loop | ||
+ | // normal processing here... | ||
+ | EndDo; | ||
+ | CloseCursor(); | ||
+ | EndIf; | ||
+ | return; | ||
+ | /end-free | ||
+ | |||
+ | P OpenCursor B | ||
+ | D OpenCursor PI like(ReturnVar) | ||
+ | D ReturnVar s n | ||
+ | |||
+ | // The immediately following /EXEC SQL is SQL's version of RPG's H Spec | ||
+ | // It is never executed. Just used at compile time. | ||
+ | EXEC SQL | ||
+ | Set Option | ||
+ | Naming = *Sys, | ||
+ | Commit = *None, | ||
+ | UsrPrf = *User, | ||
+ | DynUsrPrf = *User, | ||
+ | Datfmt = *iso, | ||
+ | CloSqlCsr = *EndMod; | ||
+ | |||
+ | |||
+ | EXEC SQL | ||
+ | Declare C1 cursor for | ||
+ | Select System_Table_Schema as library, | ||
+ | System_Table_Name as file | ||
+ | from qsys2/systables; | ||
+ | |||
+ | EXEC SQL Open C1; | ||
+ | |||
+ | Select; | ||
+ | When SqlStt='00000'; | ||
+ | return *on; | ||
+ | Other; | ||
+ | return *off; | ||
+ | EndSl; | ||
+ | /end-free | ||
+ | P OpenCursor E | ||
+ | |||
+ | /eject | ||
+ | P FetchCursor B | ||
+ | D FetchCursor PI like(ReturnVar) | ||
+ | D ReturnVar s n | ||
+ | C/free | ||
+ | EXEC SQL | ||
+ | Fetch C1 into :MyLib, :MyFile; | ||
+ | Select; | ||
+ | When sqlstt='00000'; | ||
+ | // row was received, normal | ||
+ | ReturnVar=*on; | ||
+ | When sqlstt='02000'; | ||
+ | // same as %eof, sooner or later this is normal | ||
+ | ReturnVar=*off; | ||
+ | Other; | ||
+ | // alert the troops! | ||
+ | ReturnVar=*off; | ||
+ | EndSl; | ||
+ | return ReturnVar; | ||
+ | /end-free | ||
+ | P FetchCursor E | ||
+ | |||
+ | /eject | ||
+ | P CloseCursor B | ||
+ | D CloseCursor PI like(ReturnVar) | ||
+ | D ReturnVar s n | ||
+ | /free | ||
+ | EXEC SQL | ||
+ | Close C1; | ||
Select; | Select; | ||
When sqlstt='00000'; | When sqlstt='00000'; |
Revision as of 23:20, 11 February 2008
Contents
RPG IV with Embedded SQL
Introduction to RPG IV with Embedded SQL
SQL is a powerful tool in any programmer's skill set. It is possible to leverage this tool from within RPG (& other languages). The source member type for these programs is SQLRPGLE. Here is an example of an embedded SQL statement in non-free RPG:
C/EXEC SQL C+ SELECT * FROM mylib/myfile C/END-EXEC
And again in V5r4+ free-form RPG:
exec sql select * from mylib/myfile;
SQL Directives
Directive | Function performed |
---|---|
SELECT | The Select directive is used to extract data from a Physical file or Table |
DECLARE | The Declare Cursor directive is used to define a data path in your program |
OPEN | The Open Cursor directive is used to open a data path defined by a Declare Cursor directive. |
CLOSE | The Close Cursor directive is used to close a data path opened by a Open cursor directive |
FETCH | The Fetch directive is used to extract records from a data path opened via the Open Cursor directive |
PREPARE | The Prepare directive is used to prepare an dynamic SQL statement for execution |
EXECUTE | The Execute directive is used to execute an dynamic SQL statement prepared via the Prepare directive |
EXECUTE IMMEDIATE | The Execute Immediate directive is used to execute an SQL statement directly & does not require a Prepare directive |
DELETE | The Delete directive is used to delete data from a Physical file or Table |
DROP TABLE | The Drop Table directive is used to delete a Physical file or Table |
CREATE TABLE | The Create Table directive is used to create a Physical file or Table |
ALTER TABLE | The Alter Table directive is used to change file & field attributes in a Physical file or Table |
UPDATE | The Update directive is used to update data from a Physical file or Table |
Sample Embedded SQL program
V5R1-V5R3
H ActGrp(*CALLER) H DftActGrp(*NO) D OpenCursor PR n D FetchCursor PR n D CloseCursor PR n D MyLib s 10a D MyFile s 10a /free *inlr=*on; if not OpenCursor(); // perform error routine to alert the troops // ... Else; Dow FetchCursor(); // putting the fetchcursor on the do loop allows the user of // iter, and thus iter will not perform an infinite loop // normal processing here... EndDo; CloseCursor(); EndIf; return; /end-free P OpenCursor B D OpenCursor PI like(ReturnVar) D ReturnVar s n // The immediately following /EXEC SQL is SQL's version of RPG's H Spec // It is never executed. Just used at compile time. C/EXEC SQL C+ Set Option C+ Naming = *Sys, C+ Commit = *None, C+ UsrPrf = *User, C+ DynUsrPrf = *User, C+ Datfmt = *iso, C+ CloSqlCsr = *EndMod C/END-EXEC C/EXEC SQL C+ Declare C1 cursor for C+ Select System_Table_Schema as library, C+ System_Table_Name as file C+ from qsys2/systables C/END-EXEC C/EXEC SQL C+ Open C1 C/END-EXEC /free Select; When SqlStt='00000'; return *on; Other; return *off; EndSl; /end-free P OpenCursor E /eject P FetchCursor B D FetchCursor PI like(ReturnVar) D ReturnVar s n C/EXEC SQL C+ Fetch C1 into :MyLib, :MyFile C/END-EXEC /free Select; When sqlstt='00000'; // row was received, normal ReturnVar=*on; When sqlstt='02000'; // same as %eof, sooner or later this is normal ReturnVar=*off; Other; // alert the troops! ReturnVar=*off; EndSl; return ReturnVar; /end-free P FetchCursor E /eject P CloseCursor B D CloseCursor PI like(ReturnVar) D ReturnVar s n C/EXEC SQL C+ Close C1 C/END-EXEC /free Select; When sqlstt='00000'; // cursor was closed, normal ReturnVar=*on; Other; // alert the troops! ReturnVar=*off; EndSl; return ReturnVar; /end-free P CloseCursor E
V5R4+
SQL is now supported in freeform
H ActGrp(*CALLER) H DftActGrp(*NO) D OpenCursor PR n D FetchCursor PR n D CloseCursor PR n D MyLib s 10a D MyFile s 10a /free *inlr=*on; if not OpenCursor(); // perform error routine to alert the troops // ... Else; Dow FetchCursor(); // putting the fetchcursor on the do loop allows the user of // iter, and thus iter will not perform an infinite loop // normal processing here... EndDo; CloseCursor(); EndIf; return; /end-free P OpenCursor B D OpenCursor PI like(ReturnVar) D ReturnVar s n // The immediately following /EXEC SQL is SQL's version of RPG's H Spec // It is never executed. Just used at compile time. EXEC SQL Set Option Naming = *Sys, Commit = *None, UsrPrf = *User, DynUsrPrf = *User, Datfmt = *iso, CloSqlCsr = *EndMod; EXEC SQL Declare C1 cursor for Select System_Table_Schema as library, System_Table_Name as file from qsys2/systables; EXEC SQL Open C1; Select; When SqlStt='00000'; return *on; Other; return *off; EndSl; /end-free P OpenCursor E /eject P FetchCursor B D FetchCursor PI like(ReturnVar) D ReturnVar s n C/free EXEC SQL Fetch C1 into :MyLib, :MyFile; Select; When sqlstt='00000'; // row was received, normal ReturnVar=*on; When sqlstt='02000'; // same as %eof, sooner or later this is normal ReturnVar=*off; Other; // alert the troops! ReturnVar=*off; EndSl; return ReturnVar; /end-free P FetchCursor E /eject P CloseCursor B D CloseCursor PI like(ReturnVar) D ReturnVar s n /free EXEC SQL Close C1; Select; When sqlstt='00000'; // cursor was closed, normal ReturnVar=*on; Other; // alert the troops! ReturnVar=*off; EndSl; return ReturnVar; /end-free P CloseCursor E