Hello Neil!

Replying to a message of Neil Heller to Darin McBride:

 NH>> The count command became part of the SQL standard in SQL-92.  
 NH>> Unfortunately, I have not been able to find a corresponding
 NH>> command in  ODBC.  If I were to use the count command in an
 NH>> SQLExecDirect statement,  I don't have a clue as to how I could
 NH>> retrieve any sort of return value.

 DM>> SQLGetData?  Same as any select?

 NH> No, that's a different animal.  Here is what Watcom says about 
 NH> SQLGetData():

 NH> SQLGetData returns result data for a single column in the current row. 

That's exactly what you want.  Remember that SELECT COUNT() is no different
than any other SELECT statement.  SELECT creates a temporary table with the
data selected, and allows you to retrieve that data one row at a time.  In
this case, the COUNT() function summarises the results into a single row. 
Further, COUNT() also occupies a single column.  If you were to do:

SELECT COUNT(*), AVG(income) FROM tbl WHERE ...

then you'd get one row, but two columns - column 1 is the count, and column
2 is the average of the income column in the tbl table, both using only the
subset that matches the WHERE condition.

 NH> Unlike SQLBindCol, which causes data to be transferred to a program 
 NH> variable when SQLFetch or SQLExtendedFetch is called, SQLGetData can 
 NH> only return result data for a single column in the current row. In

Nothing prevents you from calling SQLGetData multiple times per row.

 NH> other  words, SQLFetch or SQLExtendedFetch moves the cursor to the
 NH> next row and  implicitly transfers data when SQLBindCol has been
 NH> used, but SQLFetch or  SQLExtendedFetch merely move the cursor to the
 NH> next row when SQLGetData  is used - the data is only transferred to
 NH> the program variable when  SQLGetData is called. It is possible to
 NH> use SQLBindCol for some columns  and use SQLGetData for others within
 NH> the same row.  This function can be  used to retrieve long data
 NH> values in portions (for example, data from  SQL_LONGVARBINARY or
 NH> SQL_LONGVARCHAR columns).

Sounds like SQLGetData is the only way to get the results back from long
var binary or long varchar columns...

 DM>> SQLINTEGER count;
 DM>> if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_DATA)
 DM>> {
 DM>>   SQLINTEGER cb;
 DM>>   SQLGetData(bstmt, &count, 0, &cb);
 DM>> }

 DM>> (This is just from reading the msdn for the last 5 minutes 
 DM>> after a google search on "odbc api" - I've never dealt with 
 DM>> odbc before in my life.)

 NH> That's good way - I wish ODBC were implemented that way.

It is - that's the MS API docs I was quoting from!

 NH> Syntax

 NH> RETCODE SQLGetData(hstmt,icol,fCType,rgbValue,cbValueMax,pcbValue) The
 NH> SQLGetData function accepts the following arguments:

 NH> SDWORD FAR * pcbValue

 NH> Output: The total number of bytes (excluding the null termination
 NH> byte)  available to return in rgbValue or SQL_NO_TOTAL if the number
 NH> of  available bytes cannot be determined. If pcbValue is SQL_NO_TOTAL
 NH> or is  greater than or equal to cbValueMax, the data in rgbValue is
 NH> truncated  to cbValueMax - 1 bytes. If the data value is NULL,
 NH> pcbValue is SQL_NULL_DATA.

 NH> Returns

 NH> SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA_FOUND, 
 NH> SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.

 NH>> ... as the one above will really result in time savings over
 NH>> constructing a linked list on the heap for about 200 entries.

 DM>> YES!  I use count() soooo much, and as long as it's 
 DM>> counting either everything or something where there's an 
 DM>> index, it's way faster.  Way, way, way faster.  Even 
 DM>> without using the index it's still faster than reading 
 DM>> everything into memory in a linked list and then counting 
 DM>> that yourself.

 NH> If I can construct a linked-list, there's no need to do a count.  Each 
 NH> node of the list consists of four pointers, so I don't even have to 
 NH> consider paragraph boundaries when constructing the list.

 NH> I'm back to trying to implement "count".  It's helpful to know that
 NH> the  speed difference is significant.

Darin
C_PLUSPLUS moderator
dmcbride{at}tower.to.org

---