Re: GET DIAGNOSTICS question

[Charles Wilt <charles.wilt@xxxxxxxxx>]

DB2_NUMBER_ROWS works, but you have to open an INSENSITIVE
cursor...otherwise it's just a guess..
ie. The Db will make a copy of the data...

DB2_NUMBER_ROWS
If the previous SQL statement was an OPEN or a FETCH which caused the size
of the result table to be
known, returns the number of rows in the result table. For SENSITIVE
cursors, this value can be
thought of as an approximation since rows inserted and deleted will affect
the next retrieval of this
value. Otherwise, the value zero is returned.


Personally, if you want to process to the end anyway, I'd just do so and
take a nap when you run out of data.

Charles

On Wed, May 10, 2023 at 2:38 AM Reeve <rfritchman@xxxxxxxxx> wrote:

> How do I determine the number of records in the cursor I've just opened?
> GET DIAGNOSTICS  :x = ROW_COUNT  returns the number of rows processed--not
> what I need here.  GET DIAGNOSTICS :x = DB2_NUMBER_ROWS seems to be the
> right call but it gives me a nonsense (I think) number (14932) when there
> are zero rows.  If I have zero rows available, I close the cursor, sleep,
> wake up, open the cursor again, and check for available rows.
>
> The goal is to put this application (a daemon) to sleep when there are no
> records to process.  Yes, I could go old skool and do an EOF-DELAY (but
> application logic precludes arrival sequence) , data queue is the same
> reason, or a file status data structure (the pre-SQL way).  A
> workaround isn't hard--I'd just count the number of records processed when
> SQLSTT = '02000'; if the number is zero, then there are no rows available
> and I'll take a nap.  As I think about it, ROW_COUNT would be the
> perfect solution--it's intuitive.
>
> Sidebar question: what's the format for using VALUES and GET DIAGNOSTICS in
> Run SQL Scripts?
>
> Thanks!
>
> -rf
> --
> 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.