Re: An embedded SQL generated EVAL that does not evaluate???

[Reeve <rfritchman@xxxxxxxxx>]

Before you close the cursor, try
 EXEC SQL GET DIAGNOSTICS :rowsFetched = ROW_COUNT;

\reeve

On Fri, Jul 23, 2021 at 2:12 PM Alan Cassidy <cfuture@xxxxxxxxxxx> wrote:

> A very change thing going on with the SQL in my program:
>
> Loop control for going through selected rows is as follows here:
>
>           //
>           exsr PosFile ;
>           //
>           DOU RowsFetched = 0 or SqlCode <> 0 ;
>             exsr FetchNext ;
>             For rx = 1 to RowsFetched ;
>               Eval-Corr CS = MT(rx) ;
>               //
>               // .. Lots of code to process each row…
>               //
>             ENDFOR;
>           EndDo;
>
>           exsr CloseCK ;
>
>
> Subroutine Posfile declares and opens cursor CK...
>
>
>           //
>           svkey = sv15x3ad + %char(sv15pudt) +
>                   %editc(sv15ot:'X') + %editc(sv15pro:'X') ;
>           EXEC SQL Declare CK Cursor for
>              select
>                CS154X4USR ,
>                ….. <...list of columns...>
>                CSDLTUSR
>              from cs15p001
>              where CSACTINA <> 'I'
>                and ( : fleread in ('00', '01')  or
>                      : fleread = '02' and current_user = CS15NADUSR or
>                      : fleread = '03' and current_user = CS154X4USR )
>                and (CS15OT <> 0 and CS15PRO <> 0)
>                and (:SVKEY < CS15X3AD || char(CS15PUDT) ||
>                              digits(CS15OT) || digits(CS15PRO))
>              order by CS15X3AD, CS15PUDT, CS15OT, CS15PRO ;
>              //
>           exec sql Open CK ;
>           //
>         endsr ;
>
>
> Then there is subroutine FetchNext:
>           exec sql Fetch Next from CK for :Rows_To_Fetch rows
>                    into :MT ;
>           rowsFetched = SqlErrd(3) ;
>           *in99 = (rowsfetched = 0) ;
>
> What's going on is in the generated code from the SQL precompiler at the
> FETCH statement, pasted following from debug in the 5250 session running
> the program, copied below. At that first line (with the arrow indicating
> which one), before it executes, ROWS_TO_FETCH is equal to 1000 (one
> thousand). After it executes, SQLER5 is still equal to ZERO, even though
> ROWS_TO_FETCH is still 1000. 😮😮 Can somebody tell me how to fix this?
>
> //*  exec sql Fetch Next from CK for :Rows_To_Fetch rows
> //*           into :MT ;
>     SQLER5 = ROWS_TO_FETCH;      <==========
>     SQLER6 = -4;
>     SQCALL000013(
>          SQLCA
>        : SQL_00043
>        : MT
>     );
>
>
>
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com