SQL record count

[gregmonay]

Hi,
Is there a functionality in the SQLCA to return the record count affected by the last sql select query. This is to eliminate my process of coding a

select count(field) into :reccount from mytable
where field=:myvar

What i am trying to do is query the file

declare mycursor cursor for 
select field,field1,field2 from mytable
where field=:myvar

and i want to know how many records will be processed in my loop without resorting to the code above with the count()

Thanks,
Greg

[rckass]

Re: SQL record count

Sorry - I'm very old school - the reason you don't just want to add 1 to your field reccount within the do loop is?

[B.Hauser]

Re: SQL record count

Hi,

you may try a get diagnostics statement with DB2_NUMBER_ROWS specified immediately after the OPEN statement.

C/EXEC SQL   GET DIAGNOSTICS :NbrOfRows = DB2_NUMBER_ROWS
C/End-Exec 

SQL Reference Get Diagnostics
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. If the previous statement was a PREPARE statement, returns the estimated number of rows in the result table for the prepared statement. Otherwise, the value zero is returned.

If you need the exact number of rows, you either have to count the returned rows or to execute a second statement that counts the rows.

Birgitta