Re: EXTERNAL: SQL Procedure Result Sets Array Processing

Hi Buzz,

In this case - I wouldn't try to externalize too much, as pretty much every Python, PHP or .NET program is using SQL in one way or another.

If the I/O is pretty complex - try to create an UDTF like in Scott's papers. But otherwise simply don't bother and use embedded SQL to access your data.

In SQL you always access the base table directly - the system determines which index (key) is used to have the best access path. This is completely the other way as traditional RPG I/O (SETLL/READ/CHAIN) is doing it.

For the fetch of multiple columns - you can to a

DECLARE myCursor CURSOR FOR
SELECT * FROM ... WHERE ...;
...
FETCH myCursor INTO :myDataStruct;

Or even

SELECT * INTO :myDataStruct WHERE ...;

When you only have to retrieve 1 row (record).

In both cases myDataStruct is an externally defined data structure from that table. But using this, when the table changes in the future, you might have to re-compile the program.

The better might be, to only SELECT the fields that you really need, and FETCH into an data structure that you define for this purpose or directly into a list of fields. You can even mix both.

This way your program is as independent as possible from the underlying database structure as possible.

If you are new to SQL and especially embedded SQL, I would recommend to get some training - maybe from Birgitta Hauser. Or you can try to work your way through this "jungle" yourself - and ask some questions here and there - but this might take a lot longer.

If you're still lost, you can also send me a PM with more details. If you only have to access the data by 3 different search criteria it's not so hard to do - I can send you some example code.

The I/O externalization was a trend once - but IMHO its pretty much futile with SQL. But that's my personal opinion.

HTH and kind regards,
Daniel

Am 30.04.2025 um 23:06 schrieb Buzz Fenner via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>:

On Wed, 30 Apr 2025 14:41:17 Vern Hamberg wrote:

"...creating SQL stored procedures that returns a result set(s) to an RPG-ILE array..." wasn't clear to me.

Maybe I'm making it too complicated, though! :)

Or, I'm making it too complicated! I'm a complete neophite with stored procs & UDTF's, so I apologize for probably misusing terminology. Let me start over by detailing the problem I'm trying to solve. To wit:

1) I'm re-factoring an RPG III program to ILE-RPG.
2) The user is prompted to enter a value in 1 of 3 input fields; the value is used to fetch 1 or multiple records from a PF.
3) The rub here is that each of the input fields represents a key field; therefore the program had to manage DB retrieval
using CHAIN & SETLL/READE opcodes in conjunction with three LF's built over the PF. It's ugly.
4) I wish to externalize as much of the DB access as possible, by having the SQL code fetch the (multi-column) rows (for
read only) and return them in as few program statements as possible.

I hope this better describes what I'm trying to accomplish. BTW, as a practical matter I'd wish that the solution is flexible enough to be called from other environments (i.e. Python, PHP, .NET, etc).

HTH...

--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
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.