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:

Create QRCODE in DDS Create QRCODE in DDS
Posted by September 21, 2018
Programming >> RPG Programming
Base64 Encoding a File with RPG Base64 Encoding a File with RPG
Posted by September 6, 2018
Programming >> RPG Programming
Building JSON with RPG and YAJL and Writing to Standard Output Building JSON with RPG and YAJL and Writing to Standard Output
Posted by August 31, 2018
Programming >> Proof of Concept (POC)
How to Delete Files or Empty Trash From Your Google Drive with your IBM i and RPG/ILE How to Delete Files or Empty Trash From Your Google Drive with your IBM i and RPG/ILE
Posted by July 24, 2018
BVSTools >> BVSTools Software Discussion >> GreenTools for G Suite (Google Apps) (G4G) Specific Discussion
GreenTools for G Suite (G4G) Updated to Include Delete and Empty Trash Function GreenTools for G Suite (G4G) Updated to Include Delete and Empty Trash Function
Posted by July 24, 2018
BVSTools >> BVSTools Announcements >> GreenTools for G Suite (Google Apps) (G4G) Specific Announcements
What to Do If Your License Keys Don't Work What to Do If Your License Keys Don't Work
Posted by July 18, 2018
BVSTools >> BVSTools Software Discussion
MAILTOOL Updated to Allow Failed Message on Invalid Recipient MAILTOOL Updated to Allow Failed Message on Invalid Recipient
Posted by May 20, 2018
BVSTools >> BVSTools Announcements >> eMail Tool (MAILTOOL) Specific Announcements
Non HTTPS Callbacks Removed from GreenTools for G Suite (G4G) Non HTTPS Callbacks Removed from GreenTools for G Suite (G4G)
Posted by April 15, 2018
BVSTools >> BVSTools Announcements >> GreenTools for G Suite (Google Apps) (G4G) Specific Announcements
IBM i Related Survey Available IBM i Related Survey Available
Posted by April 7, 2018
IBM Power Systems >> (QGPL) IBM i
BVSTools Releases Braintree Webhook Open Source Application - Node.js BVSTools Releases Braintree Webhook Open Source Application - Node.js
Posted by April 5, 2018
Programming >> Open Source
BVSTools Now Offering Web Services (BETA) BVSTools Now Offering Web Services (BETA)
Posted by April 3, 2018
BVSTools >> BVSTools Announcements
Creating a Reverse SSL Proxy Using RPG on the IBM i - Part 2 Creating a Reverse SSL Proxy Using RPG on the IBM i - Part 2
Posted by March 29, 2018
Programming >> Web Programming
Still on V7R1 or Earlier?  Here's Why You Should Upgrade NOW! Still on V7R1 or Earlier? Here's Why You Should Upgrade NOW!
Posted by February 21, 2018
IBM Power Systems >> (QGPL) IBM i
Converting a MMDDYY date format to YYMMDD for Sorting Using SQL Converting a MMDDYY date format to YYMMDD for Sorting Using SQL
Posted by February 16, 2018
Programming >> RPG Programming
Moving All Files from a Google Drive Folder to the Trash Using GreenTools for Google Apps (G4G) Moving All Files from a Google Drive Folder to the Trash Using GreenTools for Google Apps (G4G)
Posted by February 3, 2018
BVSTools >> BVSTools Software Discussion >> GreenTools for G Suite (Google Apps) (G4G) Specific Discussion

Reply




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