RE: Load a subfile with clients selected by user

-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] De la part de Marc Couture
Envoyé : mardi 30 juin 2009 13:25
À : RPG programming on the IBM i / System i
Objet : Re: Load a subfile with clients selected by user

David,

Please post the client file and address file definition.

Regards,

Marc C

--------------------------------------------------
From: "David FOXWELL" <David.FOXWELL@xxxxxxxxx>
Sent: Tuesday, June 30, 2009 4:46 AM
To: "RPG programming on the IBM i / System i" <rpg400-l@xxxxxxxxxxxx>
Subject: Re: Load a subfile with clients selected by user

Rob thanks,

I feel this is the way to go, but the question of the number of
cursors is a difficult one for me.

I thought I'd have 2, one for the event of the user

entering only the

client information and another for when address information

is entered.

My select criteria would always be the same ( governed by

the subfile

to fill), the from file will be the client file or the

address file

joined to the client file.

I'd have to build the WHERE criteria depending on the user

data entered.

So, I <think> I'd have a function like getClientsByClientInfo and
getClientsByAddress. Each would create a cursor depending upon the
WHERE criteria received. If this criteria changed, I'd close and
recreate the cursor.

Does that sound reasonable?







* From: rob@xxxxxxxxx
* Date: Fri, 26 Jun 2009 09:52:46 -0400

In summary, you need a good skeleton of a SQLRPGLE program that
processes a subfile, backwards and forwards.

And the other question is: Are you better off using multiple
cursors<http://archive.midrange.com/rpg400-l/200906/msg00442.html#>
(for the different selection criteria) or using "prepared"

statements?

Dynamic

sql<http://archive.midrange.com/rpg400-l/200906/msg00442.html#

or not?

There are better 5250 programmers out there so I'll hope you get a
good template from them.

As far as the cursors go, study this:

Step 1: Build a rpg variable that has all this in it.
MySqlStmt =
Declare C1 cursor for
Select t1.col11, t1.name, t1.col13,
t2.zip<http://archive.midrange.com/rpg400-l/200906/msg00442.html#>,
t3.city
from table1 t1
join table2 t2
on t1.col12 = t2.itskey
join table3 t3
on t1.col13 = t3.itskey
where t1.name between ? and ?
and t2.zip between ? and ?
and t3.city between ? and ?
order by ...
Comment: The question marks are called "parameter markers". More on
this later...

Step 2: Prepare a sql statement from this variable exec sql Prepare
Stmt1 from :MySqlStmt;

Step 3: Declare the cursor from the prepared statement.
exec sql Execute Stmt1;

Step 4: Open the cursor. Pass in variables into the

parameter markers.

If you only want a single value for "t2.col2a" then pass the same
variable into the left and the right of the and.
exec sql Open c1 using :FromName, :ToName, :FromZip, :ToZip,
:FromCity, :ToCity;

Then you do your fetches and whatnot.

Study also:
- Soundex http://preview.tinyurl.com/sqlsoundex
- like http://preview.tinyurl.com/sqllike


Rob Berendt
--
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.