Re: Help with SQL Multi Row Fetch

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Off the top of my head...

Fetch Item_Data for :Records_to_Read Rows

I didn't think you could do this.  I think you have to have a set number of
rows...

Fetch Item_Data for 300 Rows


On Fri, Jun 26, 2015 at 4:13 PM, Jeff Young <jyoung0950@xxxxxxxxx> wrote:

> All,
> I have been doing this for years, but now for some reason, my multi row
> fetch is only returning 1 row even though I requested up to 300 rows.
> running the exact same SQL using QMQRY, I get 4 rows returned.
>
> My code is as follows:
> <snip>
>            Exec SQL
>              Fetch Item_Data for :Records_to_Read Rows
>                Into :Item_Details;
>            If SqlState < *Zero;
>              Leave;
>            EndIf;
>
>            Exec SQL
>              Get Diagnostics
>                :Last_Row     = DB2_Last_Row,
>                :Records_Read = Row_Count;
> Where Item_Data is the cursor the I defined, Records_to_Read is 300 and
> Item_Details is a MODS defined with Occurs(300).
>
> My actual SQL is  about 100 lines, and when I copy and paste it into a
> separate source member (without the Declare Cursor) and use it the create a
> QMQRY object, I get 4 rows returned, so I know my SQL is correct.
>
> System is at V7R1M0
> SF99710               CUMULATIVE PTF PACKAGE C4143710
> SF99709               GROUP HIPER
> SF99708               GROUP SECURITY
> SF99707               TECHNOLOGY REFRESH
> SF99701               DB2 FOR IBM I
>
> Any idea why this is happening?
>
> Jeff Young
> Sr. Programmer Analyst
> --
> This is the RPG programming on the IBM i (AS/400 and iSeries) (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.