Re: SQL Prepare question

Rob is correct, you only need 1 declare & prepare for a given statement...

For efficiency, you should be doing a multi-row fetch.. ROW_COUNT will
tell you how many rows were actually returned, up to the number requested
(200 in the below case)
Lastly, you should pick better names than C0 and S0, if by chance you end
up calling some other process that happens to use the same names, you'll
have problems. You should always use a name likely to be unique.

dcl-c LAST_ROW_RECEIEVED const(100);
dcl-c NO_DATA const(100);

exec SQL prepare LogRetrievalStmt from :SQLstmt;
exec SQL declare LogRetrievalCsr cursor for LogRetrievalStmt;
DoW process <> 'STOP';
exec SQL open LogRetrievalCsr using :startTimeStamp;
if SQLCODE <> *ZEROS;
//handle error
endif;
DOU SQLCODE <> *ZEROS or DB2_LAST_ROW = LAST_ROW_RECIEVED;
exec SQL fetch LogRetrievalCsr FOR 200 ROWS INTO :LogDS;

if SQLCODE <> *ZEROS;
if SQLCODE <> NO_DATA;
//handle error
endif;
leave;
endif;

for x = 1 to ROW_COUNT;
<processing entries received>
endfor;
ENDDO;
exec SQL close LogRetrievalCsr;
if SQLCODE <> *ZEROS;
//handle error
endif;
enddo;

Charles

On Mon, Nov 1, 2021 at 2:50 AM Don Brown via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

I have a program that reads a log, sleeps for a while then checks if there
are any more records.

I am doing;

DoW process <> 'STOP';
exec SQL prepare S0 from :SQLstmt;
exec SQL declare C0 cursor for S0;
exec SQL open C0 using :startTimeStamp;
exec SQL fetch C0 INTO :LogDS;

DOW SQLCOD = *ZEROS;

<processing entries received>
ENDDO;
exec SQL close C0;
enddo;

The value for startTimeStamp is incremented for any logs found and the
latest value will be used when the program wakes up.

Is this the correct and efficient way to do this ? ( I think I am just too
used to SETLL ==> Read )

Thank you to any comments

Cheers

Don


--
This email has been scanned for computer viruses. Although MSD has taken
reasonable precautions to ensure no viruses are present in this email, MSD
cannot accept responsibility for any loss or damage arising from the use of
this email or attachments..
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-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