RE: An embedded SQL generated EVAL that does not evaluate??? -- RPG400-L

SQLER5 - Returns *Zeros as long as there are other rows to be returned. If the last row (to be returned) is in the bloc 100 is returned.

If you want to know how many records were (really) returned, you have to use SQLER3 (or as Reeve mentioned execute a GET DIAGNOSTICS Statement and return the value for ROW_COUNT)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Reeve
Sent: Samstag, 24. Juli 2021 02:03
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: An embedded SQL generated EVAL that does not evaluate???

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

--
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