Re: SQL Procedure Result Sets Array Processing

Yes - that's true - so let's discuss it. My first question would be: Why?

In 99% of all cases, the idea of filling an array with data from a table, is a bad idea.

The "transformation" should - whenever possible - happen inside the database - using SQL and JOIN - which is easy with an UDTF, and impossible with an SP returning a result set.

I don't know the exact problem on hand - but with an UDTF the probability of solving the problem inside the database, is much higher. You can simply JOIN the UDTF to your data, and don't have to make LOOKUPs or anything to find data in an array - and the database is very effective in optimizing those kinds of operations.

So yes - you can FETCH multiple rows from both an result set and an UDTF - but that doesn't mean you should.

Kind regards,
Daniel

Am 30.04.2025 um 20:03 schrieb Vern Hamberg via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>:

OK, but how do any of these address the OPs question about returning into an RPG array? Except the one that returned several rows into a DS array?

*Regards*

*Vern Hamberg*

IBM Champion 2025 <cid:part1.1FRBb0SJ.L6Wg4YCb@centurylink.net> CAAC (COMMON Americas Advisory Council) IBM Influencer 2023

On 4/30/2025 11:06 AM, Charles Wilt wrote:
I'd second the recommendation for a UDTF...

Much more flexible than a SP returning a results set..

Charles

On Wed, Apr 30, 2025 at 9:55 AM Daniel Gross<daniel@xxxxxxxx> wrote:

Hi Buzz,

you can find some info in the Info Center:


https://www.ibm.com/docs/en/i/7.5.0?topic=procedures-returning-result-sets-from-stored

But from a modern view of SQL I wouldn't recommend using a stored
procedure returning a result set.

IMHO a user defined table function is the better and more modern "attack
vector" as you can use the UDTF anywhere in any SQL query, where a table is
usable.


https://www.ibm.com/docs/en/i/7.5.0?topic=code-example-weather-table-function

https://www.itjungle.com/2016/06/14/fhg061416-story03/

For external (RPG) UDTF Scott has created some very nice docs:

https://www.scottklement.com/udtf/

HTH and kind regards,
Daniel

Am 30.04.2025 um 17:05 schrieb Buzz Fenner via MIDRANGE-L <

midrange-l@xxxxxxxxxxxxxxxxxx>:

I've gotten the go-ahead to do a POC for creating SQL stored procedures

that returns a result set(s) to an RPG-ILE array. My searches for articles
and code haven't turned up any "complete" articles (although I've hit on
snippets from Jon, Birgitta, etc.).

Anyone know of any good end-to-end articles that I can read to get up to

speed?

--Buzz
--

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email:MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:https://lists.midrange.com/mailman/listinfo/midrange-l
or email:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
athttps://archive.midrange.com/midrange-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.