Pageup and Pagedown in Single page Subfile using SQL

2505 pts.
Tags:
AS/400 Subfiles
Hi All, Am looking embedded SQL to do page up and page down. I know this can be achieved with Fetch relative. But somewhere I am missing some logic. Here is my loadsubfile routine logic. First Prepare the SQL string into variable WSQLSTR then c/exec sql c+ PREPARE S1 From :WSQLSTR C/end-exec c/exec sql c+ DECLARE C1 SCROLL CURSOR FOR S1 C/end-exec c/exec sql c+ OPEN C1 C/end-exec c/exec sql C+ FETCH NEXT FROM C1 INTO :MYDS C/end-exec DoW       SQLCOD = 0  And Sfrrn1 <  7 (I used SFRRN1 < 7 because my sflpage and size = 007 ) write to the subfile after incrementing sfrrn1 c/exec sql C+ FETCH NEXT FROM C1 INTO :MYDS C/end-exec EndDo Am not closing the cursor. Then when user press rollup SrRollup      Begsr clear the subfile c/exec sql C+ FETCH Relative +7 from C1 INTO :MYDS C/end-exec DoW       SQLCOD = 0 and SfRRN1 <  7 Increment    SfRRN1  and Write to Subfile c/exec sql C+ FETCH Relative +7 from C1 INTO :MYDS C/end-exec EndDo Similarly for page down, same logic except -7 after relative. But the result is not what is expected. Page down is skipping one record and page up is enterly different from the previous page. I know this logic is completely wrong. But am new to embedded SQL. From the site,all I came to know is I should use Fetch relative with +/_ n. Kindly point out where I went wrong.

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 18  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb

    Subroutine to load a page .. assumes that data structure has a record that was fetched

    LoadPage Begsr

    DoW       SQLCOD = 0  And Sfrrn1 <  7    

    (I used SFRRN1 < 7 because my sflpage and size = 007 )

    write to the subfile after incrementing sfrrn1

    c/exec sql                               
    C+ FETCH NEXT FROM C1 INTO :MYDS         
    C/end-exec   

    EndDo

    IF SQLCOD <> 0

    MoreInd = *on

    Endif

    ENDSR

     

    50,860 pointsBadges:
    report
  • philpl1jb

    Correct LoadPage subroutine should begin with initialize subfile

    50,860 pointsBadges:
    report
  • philpl1jb

    LoadFirstPage begsr

    c/exec sql                             
     c+ PREPARE S1 From :WSQLSTR            
     C/end-exec               

                   
     c/exec sql                             
     c+ DECLARE C1 SCROLL CURSOR FOR S1     
     C/end-exec                             
     c/exec sql                             
     c+ OPEN C1                             
     C/end-exec                             

    c/exec sql                               
    C+ FETCH NEXT FROM C1 INTO :MYDS         
    C/end-exec                               

    PageCount = 1

    Endsr

    50,860 pointsBadges:
    report
  • philpl1jb

    PageDown begsr

    Exsr LoadPage

    Eval PageCount += 1

    Endsr

    50,860 pointsBadges:
    report
  • philpl1jb

    PageUp Begsr

    If pageCount > 1

    c/exec sql                               
    C+ FETCH Relative -14 FROM C1 INTO :MYDS         
    C/end-exec   

    LoadPage

    Eval PageCount - = 1

    Endsr


    50,860 pointsBadges:
    report
  • philpl1jb

    The problem you're having is that on the first processing you read 8 using the first 7, reading the 8th will allow you to set the more and pagedown key indicators

    The subsequent page downs don't need to start with a fetch .. the first necessary record was fetched in previous load.  Just continue fetching next.

    PageUp needs the relative so it can go up 14 .. two pages and fetching record -14 and then loading from there.

    so I've given you some rough code for the load a page .. this should be used on initial load, page down, page up. It assumes that the first row is already in the data structure.

    the LoadFirstPage subroutine opens the cursor and fetches the first record and then uses LoadPage to load the page

    the PageDown subroutine just calls the loadPage

    the PageUp subroutine needs to know that a pageup is available .. I've done that by keeping the pageCount variable if it's greater than 2 then pageup can Fetch the relative record -14. 

    Hope that helps

    50,860 pointsBadges:
    report
  • philpl1jb

    Sorry ... page up logic should be a relative position that is up two pages + 1 .. a relative -15 not -14. That's because we've always read the first record of the next page.

    In the loadPage

    IF SQLCOD <> 0

    MoreInd = *on

    Endif

    should be

    IF SQLCOD <> 0

    MoreInd = *on

    Else

    MoveInd = *off

    Endif


    50,860 pointsBadges:
    report
  • philpl1jb

    IF SQLCOD <> 0

    MoreInd = *on

    Endif

    should be

    IF SQLCOD <> 0

    MoreInd = *on

    Else

    MoreInd = *off

    Endif

    50,860 pointsBadges:
    report
  • philpl1jb

    No, I think the indicator is backwards.


    IF SQLCOD <> 0

    MoreInd = *on

    Endif

    should be

    IF SQLCOD <> 0

    MoreInd = *off

    Else

    MoreInd = *on

    Endif

    50,860 pointsBadges:
    report
  • RamvishakRamesh
    Instead of starting with a Fetch in Page down, I have called the SrLodSfl routine again. But now I am missing one record. Because when I first execute SrLodSfl, 8th record if already fetched which allow to set the More indicator. So when page down execute SrLodSfl routine again, it starts fetching from 9th onwards. 8th record is not being written in that case..
    2,505 pointsBadges:
    report
  • RamvishakRamesh

    In Page down routine, I first write and then execute load routine. Like this

    C                   If        SqlCod = *Zero                       
    C                   Eval      SFOPTN = *Blanks                     
    C                   Eval      SfLDel = *Blanks                     
     *                                                                 
     * Write to Subfile                                                
    C                   Eval      SfRRN1  =  SfRrn1 + 1                
    C                   Write     SFL01                                
     *                                                                 
    C                   EndIf                                          
    C                   ExSr      SrLodSfl                             
    C                   Eval      wPageCnt  = wPageCnt + 1             
     *                                                                 
    C                   EndSr                                          

    Now it writes the 8th record and in SrLodSfl it fetch from 9th onwards

     

    2,505 pointsBadges:
    report
  • RamvishakRamesh

    Where will we close the cursor in this case? If I dont close the cursor in Load routine, then page up page down works fine. But my filter is not working. Filter works only if I close it.. :(

    2,505 pointsBadges:
    report
  • TomLiotta

    But my filter is not working.

    "Filter"? What is your "filter"? You haven't shown anything that you said was a "filter", so we have no way to know why it isn't working.

    Are you referring to a WHERE clause in your CURSOR?

    Tom

    125,585 pointsBadges:
    report
  • RamvishakRamesh
    Yes, the WHERE clause in cursor. Depending on the user selection, the Where clause is changed Dynamically. But this will have an impact only if I close the cursor first. But when I close, page up and page down is not working..
    2,505 pointsBadges:
    report
  • TomLiotta

    But this will have an impact only if I close the cursor first.

    That's right. If you change the WHERE clause, you're defining a different CURSOR. Until you OPEN it, you can't SELECT from it. A SELECT will retrieve rows from the current CURSOR, not from one that hasn't been OPENed.

    Tom

    125,585 pointsBadges:
    report
  • RamvishakRamesh
    Is it possible to do Filter and page up page down login then in Single page Subfile with SQL?
    2,505 pointsBadges:
    report
  • philpl1jb

    Yes, when the user changes the filter close the cursor, prepare and declare and open the cursor and clear the subfile and load the first page. 

    50,860 pointsBadges:
    report
  • TomLiotta
    You might consider using a blocked FETCH instead FETCHing single rows in a loop. You could use an array DS that has DIM() equal to SFLPAG, and FETCH FOR :SflPag ROWS. The DS array could then be written to the subfile in a loop for however many rows were successfully FETCHed. -- Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following