Difference between revisions of "SQLRPGLE"

From MidrangeWiki
Jump to: navigation, search
 
(→‎V5R4+)
 
(14 intermediate revisions by 4 users not shown)
Line 1: Line 1:
 +
[[Category:RPG]]
 +
[[Category:SQL]]
 +
[[Category:SQL]]
 +
[[Category:RPG]]
 
= RPG IV with Embedded SQL =
 
= RPG IV with Embedded SQL =
−
<table  style="table-layout: auto; border-width: thin; border-color: navy; border-style: ridge inset">
+
== Introduction to RPG IV with Embedded SQL==
−
  <tr><th colspan=3 style="color: WHITE; background-color: NAVY">SQL Directives</th></tr>
+
SQL is a powerful tool in any programmer's skill set.  It is possible to leverage this tool from within [[:Category:RPG|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:
 +
<pre>
 +
    C/EXEC SQL
 +
    C+ SELECT * FROM mylib/myfile
 +
    C/END-EXEC
 +
</pre>
 +
And again in V5r4+ free-form RPG:
 +
<pre>
 +
    exec sql select * from mylib/myfile;
 +
</pre>
 +
 
 +
[[Category:Quick Reference]]
 +
 
 +
== SQL Directives ==
 +
<!-- removed extra SQL Directives row so sorting is meaningful -->
 +
<table  style="table-layout: auto; border-width: thin; border-color: navy; border-style: ridge inset" class="sortable">
 
   <tr>
 
   <tr>
 
     <th style="color: WHITE; background-color: NAVY;">Directive</th>
 
     <th style="color: WHITE; background-color: NAVY;">Directive</th>
Line 7: Line 27:
 
   </tr>
 
   </tr>
 
   <tr>
 
   <tr>
−
     <th>SELECT</th>
+
     <th style="border-color: navy; border-style: solid">SELECT</th>
−
     <td>The '''Select''' directive is used to extract data from a Physical file or Table</td>
+
     <td style="border-color: navy; border-style: solid">The '''Select''' directive is used to extract data from a Physical file or Table</td>
 
   </tr>
 
   </tr>
 +
  <tr>
 +
    <th style="border-color: navy; border-style: solid">DECLARE</th>
 +
    <td style="border-color: navy; border-style: solid">The '''Declare Cursor''' directive is used to define a data path in your program</td>
 +
  </tr>
 +
  <tr>
 +
    <th style="border-color: navy; border-style: solid">OPEN</th>
 +
    <td style="border-color: navy; border-style: solid">The '''Open Cursor''' directive is used to open a data path defined by a '''Declare Cursor''' directive.</td>
 +
  </tr>
 +
  <tr>
 +
    <th style="border-color: navy; border-style: solid">CLOSE</th>
 +
    <td style="border-color: navy; border-style: solid">The '''Close Cursor''' directive is used to close a data path opened by a '''Open cursor''' directive</td>
 +
  </tr>
 +
  <tr>
 +
    <th style="border-color: navy; border-style: solid">FETCH</th>
 +
    <td style="border-color: navy; border-style: solid">The '''Fetch''' directive is used to extract records from a data path opened via the '''Open Cursor''' directive</td>
 +
  </tr>
 +
  <tr>
 +
    <th style="border-color: navy; border-style: solid">PREPARE</th>
 +
    <td style="border-color: navy; border-style: solid">The '''Prepare''' directive is used to prepare an dynamic SQL statement for execution</td>
 +
  </tr>
 +
  <tr>
 +
    <th style="border-color: navy; border-style: solid">EXECUTE</th>
 +
    <td style="border-color: navy; border-style: solid">The '''Execute''' directive is used to execute an dynamic SQL statement prepared via the '''Prepare''' directive</td>
 +
  </tr>
 +
  <tr>
 +
    <th style="border-color: navy; border-style: solid">EXECUTE IMMEDIATE</th>
 +
    <td style="border-color: navy; border-style: solid">The '''Execute Immediate''' directive is used to execute an SQL statement directly & does not require a '''Prepare''' directive</td>
 +
  </tr>
 +
  <tr>
 +
    <th style="border-color: navy; border-style: solid">DELETE</th>
 +
    <td style="border-color: navy; border-style: solid">The '''Delete''' directive is used to delete data from a Physical file or Table</td>
 +
  </tr>
 +
  <tr>
 +
    <th style="border-color: navy; border-style: solid">DROP TABLE</th>
 +
    <td style="border-color: navy; border-style: solid">The '''Drop Table''' directive is used to delete a Physical file or Table</td>
 +
  </tr>
 +
  <tr>
 +
    <th style="border-color: navy; border-style: solid">CREATE TABLE</th>
 +
    <td style="border-color: navy; border-style: solid">The '''Create Table''' directive is used to create a Physical file or Table</td>
 +
  </tr>
 +
  <tr>
 +
    <th style="border-color: navy; border-style: solid">ALTER TABLE</th>
 +
    <td style="border-color: navy; border-style: solid">The '''Alter Table''' directive is used to change file & field attributes in a Physical file or Table</td>
 +
  </tr>
 +
  <tr>
 +
    <th style="border-color: navy; border-style: solid">UPDATE</th>
 +
    <td style="border-color: navy; border-style: solid">The '''Update''' directive is used to update data from a Physical file or Table</td>
 +
  </tr>
 +
 
</table>
 
</table>
 +
 +
== Sample Embedded SQL program ==
 +
===V5R1-V5R3===
 +
<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.
 +
    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
 +
</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
 +
 +
      /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
 +
</pre>

Latest revision as of 23:22, 11 February 2008

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
Retrieved from "https://wiki.midrange.com/index.php?title=SQLRPGLE&oldid=9518"