Re: SQL Prepare question

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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