RE: Loading arrayDS from sql select into statement?

Actually, it is better to tell SQL the maximum number of rows to retrieve, and use (get diagnostics :var = row_count) to find out how many rows were actually retrieved after the fetch completes. Then if the row count is less than the number of rows requested, you know it got them all. Otherwise, you need to loop back around and get another block until there aren't any more to get.

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx


-----"Koester, Michael" <mkoester@xxxxxxxxxxxxx> wrote: -----
To: "RPG programming on the IBM i (AS/400 and iSeries)" <rpg400-l@xxxxxxxxxxxx>
From: "Koester, Michael" <mkoester@xxxxxxxxxxxxx>
Date: 09/14/2015 01:58PM
Subject: RE: Loading arrayDS from sql select into statement?

Charles,

Would be nice if you could use SELECT INTO along with FETCH FIRST XX
ROWS...

After getting the count (select count(*) into :resultCount from...), and declaring and opening a cursor, I can successfully use

exec sql
fetch MyCursor for :resultCount rows
into :ArrayDS;

But there apparently are no shorter shortcuts.

Michael Koester

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles
Wilt
Sent: Monday, September 14, 2015 1:47 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Loading arrayDS from sql select into statement?

Always a good idea to fact check anything on the net...

The manual says: "The SELECT INTO statement produces a result table
consisting of at most one row, and assigns the values in that row to
variables."

SELECT INTO :MYDS is a commoning recommended solution. But there's no
array there.

Would be nice if you could use SELECT INTO along with FETCH FIRST XX
ROWS...

Charles

On Mon, Sep 14, 2015 at 1:33 PM, Koester, Michael <mkoester@xxxxxxxxxxxxx>
wrote:

Thanks Alan.
So the people on this list that have suggested to others that they use
a "select into :arrayDS" have never actually tried that?
My faith has been shaken.

Michael Koester

--