Re: Load a subfile with clients selected by user

The best way to feel things out with moving SQL cursors back and forth in
a file is to simply write a small test program on a small and straight
forward physical file and see what happens. There is no better teacher
than experience. It can be done, I just don't remember off the top of my
head which program does it.


Thanks
Bryce Martin
Programmer/Analyst I
570-546-4777



David FOXWELL <David.FOXWELL@xxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
06/26/2009 08:35 AM
Please respond to
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
cc

Subject
Load a subfile with clients selected by user






Hi, I have this tricky ( for me) problem.

We started off with a screen where a user could enter part of a client's
name and a subfile would then be shown with those clients matching the
name entered. We have a PF that contains clients that can be persons or
companies. In that PF either client_name or company_name will exist for a
client, but never both. A logical exists on company_name and another on
client_name. Depending on the user input, the program decides which
logical to read from. Recently, a third logical was created when the users
wanted to search by maiden name.

Now, I've been asked to take into account zip or postcode, town and date
of birth into the equation. As the addresses are in an adress file with an
intermediate client/address file needed to get from the client to the
address, it is obviously no longer just a question of adding another
logical and reading from that.

It looks like a case for SQL. It probably should have started out that way
but at the time embedded SQL was unknown ( to us ).

I was thinking of leaving everything intact, as I don't want to completely
rewrite, but using SQL whenever one of the new fields gets used by the
user. In that case, I'd use the user input to create cursors and use these
to populate the subfile. At the moment the subfile is charged one page at
a time and pagedown or up always reads from the PF.

If there are any flaws with this choice, maybe someone would let me know.
I'm a little worried having never used a cursor to go backwards and
forwards and to populate a subfile. The program will be continually
opening and closing cursors with the same name. I think I need to manage
when a user changes a field so that I don't close the cursor
unneccessarily. Are there any issues to look out for when opening a
cursor, say C1 for SMITH then closing C1 and reopening it for JONES of
Birmingham, etc,etc.

I guess what I'm trying to say is am I at risk of any bad surprises given
that I have not a huge amount of experience with such use of SQL?