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

Thank you Reeve, and Birgitta, for your responses.

Reeve, Note that the Do-Until loop continues as long as SQLERRD(3) is <> 0. At each FETCHNEXT, I execute _RowsFetched = SqlErrd(3)_. The For loops through each element of the DS-array until done, another Fetch is done and if rows returned is zero the For loop is skipped and the Do-loop is also exited.

Note in the code snippet next below, however, my puzzle is at the statement marked by the arrow to the right. It is a statement generated by the precompiler in the source output of the CRTSQLRPGI command, generated from the Exec Sql Fetch next from CK for :rows_to_fetch rows into :MT.

SQLER5 = ROWS_TO_FETCH.

Immediately before this statement runs, debug says ROWS_TO_FETCH = 1000. Immediately after it runs, SQLER5 = 0. So it evaluates a value of 1000 in ROWS_TO_FETCH, and the result is ZERO?!!?? 😮😮 The debug is over the compile of the RPG output from the precompiler.

I copied out and pasted the SQL statement used to create the cursor, and in the interactive SQL it returns a great many records. But how can RPG evaluate a thousand to be zero? The listing from compiling says SQLER5 is defined as BINDEC(9), and my definition for ROWS_TO_FETCH is INT(10). So a value of 1000 should work fine.

It's baffling to me. If somebody has a workaround, I'd appreciate it.

--Alan

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

On 7/24/2021 4:48 AM, Birgitta Hauser wrote:

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