Size of data obtained from SQL query via ODBC API

Question

Hi, does anybody know how I can get the number of the elements (rows*cols) returned after I do an SQL query? If that can't be done, then is there something that's going to be relatively representative of the size of data I get back?

I'm trying to make a status bar that indicates how much of the returned data I have processed so I want to be somewhere relatively close... any ideas?

Please note that SQLRowCount only returns returns the number of rows affected by an UPDATE, INSERT, or DELETE statement; not the number of rows returned from a SELECT statement (as far as I can tell). So I can't multiply that directly to the SQLColCount...

My last option is to have a status bar that goes back and forth, indicating that data is being processed...

Answer 1

That is frequently a problem when you wan to reserve dynamic memory to hold the entire result set.

One technique is to return the count as part of the result set.

WITH
data AS
(
    SELECT interesting-data
    FROM   interesting-table
    WHERE  some-condition
)
SELECT COUNT(*), data.*
from data

If you don't know beforehand what columns you are selecting or use a *, like the example above, then number of columns can be selected out of the USER_TAB_COLS table

SELECT COUNT(*)
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'interesting-table'

Answer 2

SQLRowCount can return the number of rows for SELECT queries if the driver supports it. Many drivers dont however, because it can be expensive for the server to compute this. If you want to guarantee you always have a count, you must use COUNT(*), thus forcing the server into doing the potentially time consuming calculation (or causing it to delay returning any results until the entire result is known).

My suggestion would be to attempt SQLRowCount, so that the server or driver can decide if the number of rows is easily computable. If it returns a value, then multiply by the result from SQLNumResultCols. Otherwise, if it returns -1, use the back and forth status bar. Sometimes this is better because you can appear more responsive to the user.