Re: SQL Position-To with scroll-backwards capability

On Fri, 2005-07-29 at 19:09, Dan wrote:
> On 7/29/05, Wilt, Charles <CWilt@xxxxxxxxxxxx> wrote:
> > First answer, can't be done.
> > When you say "where FIRSTNAME like 'Dan%', the cursor begins at 'Dan', 
> > you can't got backwards past the beginning.
> 
> I think you misunderstand my question.  I want all 10,000 rows that
> meet the criteria (where FIRSTNAME like 'Dan%'), be able to position
> to 'Dann', *AND* then be able to scroll backwards from that point.
> 
> I am trying to avoid doing a PREPARE/OPEN everytime the user wants to Page Up.

You can use a scrollable cursor which supports "RELATIVE N" positioning,
where N is the relative to the CURRENT row (not to the entire set). 
This single CURSOR could hold all 10,000 rows and would not have to be
reopened, but you would have to do a lot of work in the background to
achieve the desired affect.  

Here is one possibility:
Fetch the entire 10,000 rows, then process the cursor top to bottom
until you find 'DANN'.  This becomes your CURRENT row.  PAGEDOWN is no
big deal, just fetch subfilesize number of rows forward.  If the user
presses page up, position RELATIVE -N where N is the subfile size (I
would use a variable for this).  Read forward (populating your subfile)
the subfile size number of rows.  Now for the tricky part... if the user
presses PAGEUP again, your cursor position is no longer correct, so you
will need to capture whether or not PAGEUP was the previous action, and
if it was, then you will need to position RELATIVE N twice.

Another possibility would be to have two cursors prepared, one for
forward reading, one for backwards.  Have each one fetch only the
subfile size number of rows.  Open and Close the cursor for each PAGEUP
PAGEDOWN event.  You should only have to PREPARE each cursor once using
parameter markers (?) for the where options, but you'll have to use
branching to determine which cursor to open/fetch/close.

Between the two, I'd probably pick the first one. Sorry if this isn't
very clear... it's definitely friday...

Joel Cochran
http://www.rpgnext.com