Forums >> Programming >> RPG Programming >>
Scrollable SQL Cursor with RPG



Posted:
bvstone

Scrollable SQL Cursor with RPG

 
Scrollable SQL Cursor with RPG

An example of using a scrollable cursor in RPG was requested.  Here is the example I had in my archives.  I apologize it's not free format, but it is easily converted should you want to do that.

This example also uses CGIDEV2 as it is a web application.  If you were using it as a subfile application you would need to find other ways to retrieve/store PerPage and LastRRN (and FirstRRN if you want to be able to page backwards).

      * Read Records Per Page and Last RRN from Web Page
      *
     C                   eval      PerPage = c2n2(ZhbGetVar('PerPage'))
     C                   eval      LastRRN = c2n2(ZhbGetVar('LastRRN'))
      *
      * Do some checking to make sure PerPage is > 0, if not, set it to a default
      *
      * Build Dynamic SQL into DynSQL String Making sure to get the RRN of each record as well
      *  Select field1, field2,... rrn(FILE) from FILE where... order by... 
      *   optimize for [perpage] rows for read only 
      *
     C/EXEC SQL
     C+ PREPARE S1 FROM :DynSQL
     C/END-EXEC
      *
     C/EXEC SQL
     C+ DECLARE C1 INSENSITIVE SCROLL CURSOR FOR S1
     C/END-EXEC
      *
     C/EXEC SQL
     C+ OPEN C1
     C/END-EXEC
      *
     C/EXEC SQL
     C+ FETCH RELATIVE :LastRRN
     C+   FROM C1
     C+     INTO :field1, field2,... :FileRRN
     C/END-EXEC
      *
     C                   dow       (SQLCOD <> 100) and (Count < PerPage)
     C                   eval      Count = (Count + 1)
     C                   eval      LastRRN = (LastRRN + 1)
      *
      * Do Processing for each record
      *
     C/EXEC SQL
     C+ FETCH
     C+   FROM C1
     C+     INTO :field1, field2,... :FileRRN
     C/END-EXEC
      *
     C                   enddo
     C/EXEC SQL
     C+ CLOSE C1
     C/END-EXEC
      *
      * write out the LastRRN to the page so it can be retrieved on the next call.
      *

 



Reply




Copyright 1983-2017 BVSTools
GreenBoard(v3) Powered by the eRPG SDK, MAILTOOL Plus!, GreenTools for Google Apps, jQuery, jQuery UI, BlockUI, CKEditor and running on the IBM i (AKA AS/400, iSeries, System i).