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.