RE: READE on a file selected by user

Thanks for the clarification, Joe.

In my case the original program looks for transactions using a client number and displaying the amount in the subfile. The novelty is to be able to search the same file for transactions using a transaction amount and displaying the client number in the subfile. If I show a list of transactions at 10cents ( using cents as it's common between France and the US), I don't think it'll be nécessary to page up to 9 cents and less, or forwards to 11 and more. But, this has me thinking further : the program at the moment loads all the transactions for the chosen client in one go. The largest number of transactions for 1 client is less than 200, but there are some amounts that occur more than 3000 times.

-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] De la part de Joe Pluta
Envoyé : jeudi 24 février 2011 16:20
À : RPG programming on the IBM i / System i
Objet : Re: READE on a file selected by user

Unfortunately, no. All a scrollable cursor does is allow you
to position within the result set, and even then only by an
offset of number of records, not by key. The problem is that
if you use a position-to field (let's say customer 1234),
then your SELECT will have "WHERE MYCUST >= 1234". The
result set will then by definition only contain records with
customers .GE. 1234, and so you cannot scroll back to, for
example, customer 1000.

That's why I create two cursors, one going forward and one
going backward. When the user pages up from the initial
position, I use the backward cursor. You go back one page,
find the key for that position, then reset both cursors. Not
particularly pretty, but it works.

Joe

Joe,

Excuse me for hijacking the thread but regarding your comment about
how you "build two SQL cursors, one going in each

direction, but it's

a lot of work.", wouldn't this be the place where declaring a
scrollable cursor would do the trick? (I agree, though,

that many times RLA is easier to work with).

Regards,


Luis Rodriguez
IBM Certified Systems Expert - eServer i5 iSeries
--



On Thu, Feb 24, 2011 at 9:23 AM, Joe

Pluta<joepluta@xxxxxxxxxxxxxxxxx>wrote:

David, there are pluses and minuses to the various architectures.
I've used several, and I'll give you my take on each. There are
really two areas you need to consider. First, is the mechanics of
the program
control:

1. Do exactly what you're suggesting, with inline SELECT

or IF/ELSE blocks.

2. Break out the code into subroutines (GETFIRST, GETNEXT,

etc.) 3.

Break out the code into subprocedures

Inline is ugliest, subprocedures take the most work (although with
7.1 it's gotten a lot easier because you don't need to prototype
internal procedures). Subroutines are a reasonable compromise but
procedures get you this:

if position();
dow getNext();
// Do my business logic!
enddo;
endif;

That's good looking code!

Next is the RLA vs SQL vs dynamic SQL.

RLA has the benefit of easily supporting backwards and forwards
positioning. This is crucial if you want to be able to

position to a

key and then be able to page either up or down. RLA lets

you do this

easily, SQL not so much. I have a pattern where I build two SQL
cursors, one going in each direction, but it's a lot of work.

On the other hand, SQL is easier for ad hoc inquiries. It

will allow

you to do queries where you have no index, although for production
you probably want those indexes anyway. If you're worried about
overhead when writing records, you can create logicals with
MAINT(*DLY), and this works on both DDS logicla files and DDL
indexes. So to a point RLA still keeps up. But as your

ordering and

selection criteria get more complex, SQL becomes a better choice.

The question there, then, is dynamic vs. static. Some people love
dynamic SQL but I personally prefer taking a little extra time and
making things static if I can. There are ways to fairly easily
enable conditional selection and ordering within limits. However,
there are limits; there comes a point where nothing but

dynamic SQL

will do. The biggest drawbacks with dynamic SQL are that

they tend

to have slightly worse performance, formatting the clauses

yourself

is difficult (escaping especially) and you need to be

careful of injection attacks.

One thing is that subprocedures (or even subroutines) allow you to
use the same skeleton code for your primary processing loop
regardless of whether you use RLA, static SQL or dynamic SQL.

So, to answer your question, I would break your DB logic out into
subprocedures. I would continue to use RLA with a SELECT

or IF/ELSE

until the selection or ordering criteria got complex enough to
justify the switch to SQL. I would strive to stick with

static SQL

unless dynamic was absolutely necessary.


Joe

Hi,

I'm sure someone must have already run into this problem :

User types in search criteria.

Program performs
SETLL searchFile
DOU %eof (searchFile)
READE(searchFile)

etc etc and loads a subfile with the results.


A new search field is added and a different searchFile

must be used,

depending upon the search criteria used by the user.

So I could do

if searchType1
SETLL searchFile1
else
SETLL searchFile2

etc,

Very messy modified code!

I could copy and paste the original code and execute one or the
other (

lots of duplicate code)

I could use an SQL cursor. In this case I would have a lot more

modifications to make.

Any tips would be greatly appreciated.

--
This is the RPG programming on the IBM i / System i (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.

--
This is the RPG programming on the IBM i / System i
(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.