Hi, How we will write Single Page Subfile in Embedded SQL (SQLRPGLE) ? Thanks Manoranjan
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.
You can use WrtSFL utility to generate subfile code. Check here for details.
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;
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.
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;