bvstone

Scrollable SQL Cursor with RPG

Posted:

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.
      *

 




Latest Posts:

G4MS Updated to v7.10 - Required Update for All G4MS Users G4MS Updated to v7.10 - Required Update for All G4MS Users
Posted by November 12, 2020
BVSTools >> BVSTools Announcements >> GreenTools for Microsoft Apps (G4MS) Specific Announcements
MAILTOOL Errors with Microsoft Office 365 (2020) MAILTOOL Errors with Microsoft Office 365 (2020)
Posted by September 3, 2020
BVSTools >> BVSTools Software Discussion >> Email Tools (MAILTOOL) Specific Discussion
Using MAILTOOL With Office 365 and Two Factor Authentication (2FA or MFA) Using MAILTOOL With Office 365 and Two Factor Authentication (2FA or MFA)
Posted by August 17, 2020
BVSTools >> BVSTools Software Discussion >> Email Tools (MAILTOOL) Specific Discussion
Updating DNS With Dynamic IP Addresses After a Router Reboot/Power Outage Updating DNS With Dynamic IP Addresses After a Router Reboot/Power Outage
Posted by August 16, 2020
Programming >> Proof of Concept (POC)
GETURI v10.10 Released with New ILE Functions GETURI v10.10 Released with New ILE Functions
Posted by August 12, 2020
BVSTools >> BVSTools Announcements >> Get URI (GETURI) Specific Announcements
MAILTOOL Updated to Allow G4GSMAIL as Option in Routers File MAILTOOL Updated to Allow G4GSMAIL as Option in Routers File
Posted by June 28, 2020
BVSTools >> BVSTools Announcements >> eMail Tool (MAILTOOL) Specific Announcements
BVSTools Now Offers Interface with Infor's ION APIs BVSTools Now Offers Interface with Infor's ION APIs
Posted by May 15, 2020
BVSTools >> BVSTools Announcements
More V7R4 IFS File CCSID Issues and The Fix More V7R4 IFS File CCSID Issues and The Fix
Posted by March 4, 2020
IBM Power Systems >> (QGPL) IBM i
Error Retrieving IP Address by Name Error Retrieving IP Address by Name
Posted by February 25, 2020
BVSTools >> BVSTools Software Discussion
Logging jobs that hit an outq Logging jobs that hit an outq
Posted by February 13, 2020
Programming >> CL Programming
GreenTools for Google Apps (G4G) v12.60 Released with Shared Drive Features and More... GreenTools for Google Apps (G4G) v12.60 Released with Shared Drive Features and More...
Posted by February 4, 2020
BVSTools >> BVSTools Announcements >> GreenTools for G Suite (Google Apps) (G4G) Specific Announcements
Allowing Requests over Port 80 For SSL Validation (ie, Namecheap, etc) Allowing Requests over Port 80 For SSL Validation (ie, Namecheap, etc)
Posted by January 31, 2020
Programming >> Web Programming
GreenTools for Slack (G4SLK) v3.00 Released GreenTools for Slack (G4SLK) v3.00 Released
Posted by January 17, 2020
BVSTools >> BVSTools Announcements >> GreenTools for Slack (G4SLK) Specific Announcements
Calling a QSH Command from RPG Calling a QSH Command from RPG
Posted by December 26, 2019
Programming >> RPG Programming
SPLTOOL Print Range (PRTRNG) Function Updated to Handle Spooled Files up to 999,999,999 Pages SPLTOOL Print Range (PRTRNG) Function Updated to Handle Spooled Files up to 999,999,999 Pages
Posted by December 14, 2019
BVSTools >> BVSTools Announcements >> Spooled File Tools (SPLTOOL) Specific Announcements

Reply




Copyright 1983-2020 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).