SQLRPGLE
From MidrangeWiki
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
/free
// 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
