Re: Result sets in SQLRPGLE

Hi Stefan,

I want to be open - I can't help you with result sets - but I also think that they are a quite "traditional" method of returning a set of rows.

The more modern approach would be to write a user defined table function (UDTF) - with that you can even use the returned data set inside of CTEs or any SQL construct (aside from updates/deletes).

Scott Klement created a nice presentation about UDTFs some while ago, and it's still circulating in the net: https://www.scottklement.com/udtf/

Maybe think about it - it's the "modern SQL" approach to returning row data that isn't available directly in SQL.

I recently came to a "legacy" solution where the team has stored an RPG array in a large char-buffer in a table - well, obviously because they wanted to access the data as an indexed array and not a individual fields. So I used their array definition and mapped the data into an UDTF - now I simply join the original table with my table function and I have all data together in one simple SQL query.

HTH
Daniel

Am 18.07.2024 um 21:10 schrieb stefan@xxxxxxxxxx:

Hi,

Just started playing with result sets in rpg.

I declare a ds dbt dim(10 000);

Then I declare, prepare ,open and fetch from a cursor.

When reaching eof I do:

exec sql
set result sets array :dbt
for :rowsreturned rows
;

How to deal with a situation when I get more than 10 000 rows? Previous fetch is then overlaid with new data ....

I guess I can only run the set result set statement once?

The expectation is to return a few 100 000 rows ( more than fits into a 16Mb ds );

Obviously I’m missing something.....

Thanks in advance for any kick in the right direction.

Stefan