RE: subfile page up & down using embedded SQL

[CWilt@xxxxxxxxxxxx]

I had problems with BOF myself.

Basically, I ended up keeping track of the current record number, so that as
I was paging back, I knew were BOF was.

Don't recall any prob with EOF.


Here's the relevant section of code:
     d MAX_SUBFILE_RECORDS...
     d                 c                   10
      // Since we can't use MAX_SUBFILE_RECORDS + 1 in the occurs
     d MAX_RECORDS_FETCHED...
     d                 c                   11                           

<snip>

         // where do we start fetching from?
         select;
           when pDirectionToRead = READ_START;
             //First screen
             wRelativePos = 1;
             wCurRecNbr = 0;
           when pDirectionToRead = READ_BACKWARD
              and wCurRecNbr <= MAX_RECORDS_FETCHED;
             // Start over at begining
             wRelativePos = 1 - wNbrRecsRead;
             wCurRecNbr = 0;
           when pDirectionToRead = READ_BACKWARD;
             // back to top of prior screen
             wRelativePos = 1 - MAX_SUBFILE_RECORDS - wNbrRecsRead;
             wCurRecNbr += wRelativePos - 1;
           // must be READ_FORWARD
           when wEOF;
             // top of current screen (redisplay)
             wRelativePos = 1 - wNbrRecsRead;
             wCurRecNbr += wRelativePos - 1;
           other;
             //last record fetched was never displayed
             wRelativePos = 0;
             wCurRecNbr -= 1;
         endsl;

      /end-free
     c/exec SQL
     c+  fetch relative :wRelativePos from C1
     c+     for :wNbrRowsToFetch rows
     c+     into :wReturnedData
     c/end-exec
      /free



Note, I'm fetching 11 but displaying 10 so I'll know if there are any more
records after the 10 displayed.


Finally, about 'Position To...'  SQL doesn't offer a good way to do this.
Basically, you'd have to read forward or backward till you found where you
needed to be.  Either one record at a time or using some sort of a search
algorithm, like the manual binary search method mentioned by Bruce.
However, if instead of 'Position To...' you think 'Subset', then all you
need to do is close the current cursor, build and open a new cursor with the
appropriate where clause.  This is what my program does.  In my case, the
subsetting was exactly what the users wanted.  Yours may be used to the
position to, but perhaps subset would work better for them.  The biggest
limitation of subset vs. Position to is that you can't go backward from the
first record displayed via subset.


HTH,

Charles


> -----Original Message-----
> From: Dan Bale [mailto:dbale@xxxxxxxxxxxxx]
> Sent: Wednesday, October 13, 2004 1:01 PM
> To: RPG programming on the AS400 / iSeries
> Subject: RE: subfile page up & down using embedded SQL
> 
> 
> I plan on using BLOCK.  First things first, though.
> 
> Could you expound a bit on your problems with BOF & EOF?  Is 
> it related to
> using RELATIVE?  Or subsequent FETCHes?
> 
> > -----Original Message-----
> > From: rpg400-l-bounces@xxxxxxxxxxxx / Wayne.James@xxxxxxxxxxxx
> > Sent: Wednesday, October 13, 2004 12:31 PM
> >
> > I use block fetches when I do this kinda stuff.
> >
> > So it will perform MUCH better than single FETCH PRIOR in a loop.
> >
> > The RELATIVE is indeed in the order of the (opened) access path.
> >
> > I have had poor experiences with hitting BOF and EOF.  The FETCH
> > that is suppose to "start over" did not.  I ended up working
> > around the issue.  If I had the time, I would chase IBM with a
> > stick over how it did not work.  *SIGH*
> >
> > But do not let my poor experience deter you from your 
> attempt.  What you
> > are attempting is WONDERFUL, once you get it working.
> >
> > L. Wayne James
> > Senior Developer
> > Agilysys, Inc.
> > Enterprise Solutions Group
> > Hospitality Solutions
> > 11545 Wills Road
> > Alpharetta, GA 30004
> > Phone:  770-962-6425 x1252
> > Email:  Wayne.James@xxxxxxxxxxxx
> 
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) 
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>