RE: Even more embedded SQL.... -- RPG400-L

First off, this request is something that would be much, much easier
with the right GUI since the GUI separates the result set from the
presentation of that result set and usually by default includes the
capabilities to sort by any column.

Secondly, how realistic is it to allow the user to be able to page
through 15,000 records.  Is the user really going to hit page down
(15,000/20 =) 750 times???  Consider how you could subset the results to
a more reasonable amount.

There's basically two methods of dealing with this on the green screen.

1) Opening and closing cursors as needed prepared from a dynamic
statement (Johnny Johnson's method)
---But consider that the usual "position to" is replaced by "subset by"
or "start at".  The difference being with "position to" I can goto a
record in the middle and hit page up.  With "subset by" or "start at",
the first record on the new screen is the first record in the result
set, I can't hit page up.  You could try automatically closing the
current cursor and opening one that provides prior records. Position to
= MICHAEL means WHERE name >= 'MICHAEL', to hit page up, I'd need to
close the WHERE name >= 'MICHAEL' cursor and open one WHERE name <
'MICHAEL'.  Remember to handle the fact your reading backwards!

2) Emulate the GUI, separating the result set from the presentation.
Retrieve a single results from the DB and store it in memory (
RPG-array, userspace, ect) You'll need a way to search and sort this
result set in memory (bsearch & qsort).  This is going to be the most
flexible, including allowing for the normal "position to" or "subset
by".  But requires more work.

HTH,


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx 
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Thursday, August 03, 2006 9:53 AM
To: RPG programming on the AS400 / iSeries
Cc: RPG programming on the AS400 / iSeries; 
rpg400-l-bounces@xxxxxxxxxxxx
Subject: Even more embedded SQL....

  Hi All,                                                     

  I have been given a project that requires the need to 
display, within a subfile, the data from 2 
  separate files with the ability to sort that data from any 
number of different columns. So I     
  thought to myself, this smacks of embedded SQL and Dynamic 
Sorting Subfiles. The unfortunate     
  thing is that the data I have to display is over 15,000 
records long (and growing) so if anyone  
  can help me, I am looking for an example of embedded SQL 
and Dynamic Sorting Subfiles - Page at  
  a time (with the ability to position to a particular piece 
of data)"                             
  Some title for an article huh? If anyone has an example of 
what is required, or could point me   
  in some direction, I would be very grateful.                

Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
-- 
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.