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.
midrange.com
