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:

SSL Handshake Errors with GETURI, MAILTOOL and GreenTools Products SSL Handshake Errors with GETURI, MAILTOOL and GreenTools Products
Posted by October 18, 2021
BVSTools >> BVSTools Software Discussion
MAILTOOL Updated to Retry Sending when GSK SSL Handshake Error 415 (GSK_ERROR_BAD_PEER) is Encountered MAILTOOL Updated to Retry Sending when GSK SSL Handshake Error 415 (GSK_ERROR_BAD_PEER) is Encountered
Posted by August 19, 2021
BVSTools >> BVSTools Announcements >> eMail Tool (MAILTOOL) Specific Announcements
MAILTOOL Updated to Allow List-Unsubscribe and User Defined Headers MAILTOOL Updated to Allow List-Unsubscribe and User Defined Headers
Posted by August 13, 2021
BVSTools >> BVSTools Announcements >> eMail Tool (MAILTOOL) Specific Announcements
AWS signing process in as400 AWS signing process in as400
Posted by August 13, 2021
Programming >> Web Programming
2022 License Price Increase and Consulting Update for Non-Software Specific Assistance 2022 License Price Increase and Consulting Update for Non-Software Specific Assistance
Posted by August 9, 2021
BVSTools >> BVSTools Announcements
Journaling the IFS, SPLTOOL and Java Journaling the IFS, SPLTOOL and Java
Posted by August 4, 2021
BVSTools >> BVSTools Announcements >> Spooled File Tools (SPLTOOL) Specific Announcements
G4MSMAIL Now Allows Wildcard Attachments G4MSMAIL Now Allows Wildcard Attachments
Posted by June 10, 2021
BVSTools >> BVSTools Announcements >> GreenTools for Microsoft Apps (G4MS) Specific Announcements
G4G Update for Uploading Large Files G4G Update for Uploading Large Files
Posted by May 28, 2021
BVSTools >> BVSTools Announcements >> GreenTools for G Suite (Google Apps) (G4G) Specific Announcements
Can We Purchase License Keys for More Than One Year at a Time? Can We Purchase License Keys for More Than One Year at a Time?
Posted by May 21, 2021
BVSTools >> BVSTools Announcements
JOBWATCH 5.10 Released with New Feature to Monitor CPU Percentages JOBWATCH 5.10 Released with New Feature to Monitor CPU Percentages
Posted by May 10, 2021
BVSTools >> BVSTools Announcements >> Job Watch (JOBWATCH) Specific Announcements
Iseries Access data transfer Iseries Access data transfer
Posted by April 29, 2021
IBM Power Systems >> (QGPL) IBM i
YAJL - Parsing YAJL - Parsing
Posted by April 4, 2021
Programming >> RPG Programming
Create LPAR partition and install OS Create LPAR partition and install OS
Posted by March 24, 2021
IBM Power Systems >> (QGPL) IBM i
G4MS Now Allows User/Password Authentication Option G4MS Now Allows User/Password Authentication Option
Posted by March 16, 2021
BVSTools >> BVSTools Announcements >> GreenTools for Microsoft Apps (G4MS) Specific Announcements
IPDS printer file using CHRSIZ IPDS printer file using CHRSIZ
Posted by February 25, 2021
Programming >> RPG Programming

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