× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



David

You've been playing with pointers - there is not much more to do.

Look up the create user space API in Google - QUSCRTUS - *http://tinyurl.com/yl3mb2o* is a link to info about making a CRTUSRSPC command.

Once you have the user space, you need to get the pointer to it - *http://tinyurl.com/yj6qy8n is an Infocenter page that tells how to work with user spaces with pointers - it includes a link to an ILE RPG sample.

Finally, you can go to the Redbook "Who Knew You Could Do ..." at **http://tinyurl.com/yegfney - it has an example on using qsort.

You mention possibly having a large result set - remember that a user space has a maximum size of just over 16 000 000

HTH
Vern
*
David FOXWELL wrote:
Vern,

I'd love to do it with a user space, but I never did one before. And management wants this done for yesterday. As always.

Thanks.
-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] De la part de Vern Hamberg
Envoyé : jeudi 12 novembre 2009 14:18
À : RPG programming on the IBM i / System i
Objet : Re: Load a subfile with clients selected by user

Hi David

There are limits, such as 9999 rows in a subfile - for elements in an array, max is 32767 in v5r4 and before - over 16 million in v6r1.

An alternative to an array is a user space. Easiest way would be to fill the user space with fixed-length entries, incrementing a pointer for each entry by the entry's length. You would then use the qsort API to sort it, then read it by pointer to get as many as you can in the subfile at a time. There are ways to handle paging beyond the subfile limit of 9999 with this technique, too.

Filling a user space via pointer is very fast.

Vern

David FOXWELL wrote:
Hope this thread still works!

Hi Vern, I'm back on this subject again and I'm looking
into your idea of using an intermediate array. Now to do this, I'd have to put all the clients into the array in one go, otherwise there'd be no way of sorting them. That means having an array that will hold about 50 000 records. I haven't looked, but maybe an array can't be that big. Then, there's the issue of the time taken to fill the array, sort it and copy it to the subfile.
I don't think I'll have that problem with SQL.
Tell me if I'm missing something.




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

Well, David, something is going to read those extra
tables, whether
you go with embedded SQL or native - either you or the SQL engine. With native, I'm suggesting filling an array, then sorting
it, then
filling the subfile from it - probably.
I do this with some maintenance screens that have mixed
sorting, gets
me what I want - and that is even using SQL!!

Your description sounds simple enough - again, SQL has to do something similar.

read Adresss-file;
dow not %eof(Address-file);
Chain address Client-address-file;
if %found(Client-address-file);
chain client Client-file;
if %found(Client-file) and [name-matches];
[do whatever you need to do - add data to subfile, or to array, sort later];
endif;
endif;
read Address-file;
enddo;

And yes - this is a priming read - there is another way, so no comments
required on that front. ;-)

As to a logical on client name, well, if you did the
innermost chain
on that logical, you'd have to compare the client. Maybe
you have a
logical on client AND name - then the innermost chain would be

chain {client : name} Client-file;

Then your if %found(Client-file); is all you need to determine whether to continue processing.

This seems simple - easily maintained - and goes directly to the files.
And if you do not need the data in the Client file, you
could use a
setll instead of the chain - this would not require the IO to retrieve the actual data, only the IO on the index (the logical file). Remember, when reading through an index, you have 2
IOs - one
for the logical file, one for the physical file it points
to - that
is a generalization, of course.

Anyhow, that seems easy enough. Am I missing something?
With SQL you
need a 3-way JOIN - it might work well but maybe not.
And if you put a sort on it by name, it still might not
use that name
index, because it will have to sort the result, probably.

Mock up the SELECT and run it in iSeries Access into
Visual Explain
and see what it does.

Vern

David FOXWELL wrote:
Vern,

I'v been thinking it over.
I'm kind of wishing I'd never discovered embedded SQL. I
think that the native solution would be complicated, less
efficient
and more difficult to modify.
For example, if a user wants all clients in Bradford with a
surname beginning with F :
Read Bradford addresses from address file,
For each address,
Read client numbers associated with that address
from client/address file
For each client at that address
Chain to client file and compare name.

In this case, the index on client name will never be used.

If I use the SQL solution, I'll effectively just read from
one file, ie a cursor that I'd have created.
* From: Vern Hamberg <vhamberg@xxxxxxxxxxx>
* Date: Fri, 26 Jun 2009 09:22:25 -0500

David

If you go
SQL<http://archive.midrange.com/rpg400-l/200906/msg00445.html#
, you would be better off doing it all with SQL - I'd say use > CHAINs or SETLL into the address file - if you have a logical over postcode or the other fields, SETLL is enough to test
existence and
is very inexpensive in IO and
CPU<http://archive.midrange.com/rpg400-l/200906/msg00445.html#>.
You need several logical files, but you do, anyhow, to get
good performance with SQL.
SQL is not the answer for every situation, IMHO.

HTH
Vern

David FOXWELL wrote:
Hi, I have this tricky ( for me) problem.

We started off with a screen where a
user<http://archive.midrange.com/rpg400-l/200906/msg00445.html
#> 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<http://archive.midrange.com/rpg400-l/200906/msg00445.html#
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<http://archive.midrange.com/rpg400-l/200906/msg00445.h
tml#> 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?
--
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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.