single-page-subfile


Q&ACategory: SubFilesingle-page-subfile
Manoranjan asked 2 years ago

Hi, How we will write Single Page Subfile in Embedded SQL (SQLRPGLE) ? Thanks Manoranjan

1 Answers
Sumit goyal Staff answered 2 years ago

Single page subfile is much easy in SQLRPGLE then FILE IO. You need a scroll able cursor and for each page up and page down you just need to more the cursor pointer.

Here are the details on scrollable

You can use WrtSFL utility to generate subfile code. Check here for details.
Example
So cursor declaration will look like this:

Start your code here
Exec SQL 
 Declare #Main Dynamic Scroll Cursor for 
 SELECT * FROM TBL1; 

Let say your table has 25 records and your subfile page (SFLPAG) is 10.
when you open the cursor pointer is before the first record.

When you start loading you need to read maximum 10 record for cursor (based on SFLPAGE).

BegSr LoadSubfile ;                               
    SavedRRN    = RRN1 + 1;                       
    Exec SQL Fetch  #Main into :MainDS;           
    DoW SQLCODE = 0;                              
      ExSr MoveCorrsponding;                      
      RRN1 += 1 ;                                 
      Write SFLRCD001     ;                       
      //  Load only 1 page at a time              
      If RRN1>= PageSize ;                        
        Leave;                                    
      EndIf;                                      
      Exec SQL Fetch  #Main into :MainDS;         
    EndDo;                                        
    //  END LOOP                                  
    //  Check If last record is alread read       
    If SQLCODE = 100;                             
      EndSubfile  = YES;                          
    Else;                                         
      Exec SQL Fetch  #Main into :MainDS;         
      If SQLCODE = 100;                           
       EndSubfile  = YES;                                    
     Else;                                                   
       Exec SQL Fetch  #Main into :MainDS;                   
       If SQLCODE = 100;                                     
         EndSubfile  = YES;                                  
       EndIf;                                                
       Exec SQL Fetch Relative -1 FROM #Main into :MainDS;   
     EndIf;                                                  
 EndSr;                                                      

Page Down:
Mostly You dont need to do anything. Just go back to subroutine LoadSubfile and it will load next 10 records from cursor as cursor pointer is already on last record loaded in subfile.

Page Up:
For page up you need to move cursor backwards. Let say you press page page up on 2nd page. Means your cursor pointer is on 20th record and your maximum RRN loaded is 10(same as SFLPAG). So on page up you need to display first page containing records 1 to 10.

Cursor pointer need to move from 20 to 1 ( based on current page and page size)

So use “Fetch Relative ” with a negative value to move cursor backwards.

 

 

Start your code here
 When KeyPressed= PageUp; 
      MoveBy = -1 * (RRN1 + PageSize + 1); 
      Exec SQL Fetch Relative :MoveBy FROM #Main into :MainDS; 
      //Clear and load subfile again without touching the cursor. 
 When KeyPressed= PageDown; 
     If EndSubfile = NO; 
          // CLEAR and Load subfile again without touching the cursor
     EndIf;

 

Your Answer