Lirik Lirik - 2 months ago 5
SQL Question

Size of data obtained from SQL query via ODBC API

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

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'