Re: GET DIAGNOSTICS question

I always feel that the other problem with using count is that there is no guarantee that the table has not changed between the time of obtaining the count and the data retrieval. Yes - I know there are ways of avoiding this but most folks don't bother and/or it would cause other issues.

As long as the result set fits, these days I like retrieving directly into an automatically sized array and using %Elem to tell me how many rows I got. Or, if I only need the count to iterate over the result set, to simply use For-Each.


Jon P.

On May 10, 2023, at 6:40 PM, Alan Cassidy <cfuture@xxxxxxxxxxx> wrote:

Thanks Buck! I've been using the DIAGNOSTIC values, but that seems a more sure thing.

But wait, you need a SELECT to do the count, and you can't do it when you open a cursor. I don't think you would run the cursor and then do this count with the same SELECT statement, so how is this?

The SQL cursors I use take a long time. It's probably quicker for the second SELECT, but I don't think that much.

--alan

On 5/10/2023 9:30 AM, Buck Calabro wrote:

On Wed, 10 May 2023 at 04:38, 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.

If I want a count, I do SELECT COUNT(*) INTO...
To me, the diagnostic counts always seemed more like approximations
rather than actionable data.
--buck

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