FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

I believe the ODBC driver is out of spec with regard to empty result sets.
 

AIUI, the only way for an ODBC application to know if there are rows to
retrieve is by calling SQLNumResultCols().  If it returns 0, there are no
results; else there are.   There is apparently no such thing as an empty
resultset: column metadata but zero rows.  

Given a table like this:

        create table T  
                ( t int NULL
                , r real NULL
                , s varchar(30) NULL
                )

and values

        t       r       s
        1       1.1     one
        2       2.2     two
        NULL    3.3     three

and the query

        select * from T where t = 2 
        select * from T where t = 0
        select 1 as 'last result'

I should get:

        SQLExecute OK
        SQLNumResultCols 3
        SQLGetData, SQLGetData, SQLGetData OK
        SQLFetch SQL_NO_DATA
        SQLMoreResults TRUE
and
        SQLNumResultCols 0
        SQLMoreResults TRUE
        ...

Instead I'm seeing:

        SQLExecute OK
        SQLNumResultCols 3
        SQLGetData, SQLGetData, SQLGetData OK
        SQLFetch SQL_NO_DATA
        SQLMoreResults TRUE
and
        SQLNumResultCols 3
        SQLGetData SQL_ERROR, Invalid cursor state

http://msdn2.microsoft.com/en-us/library/ms711684(VS.85).aspx says:

        "... the application calls SQLNumResultCols to determine the number of
columns in the result set. If this is 0, the statement did not create a
result set; if it is any other number, the statement did create a result
set."

I see no way for the application to know it shouldn't call SQLGetData()
after SQLNumResultCols() returns nonzero.  

Log snippet follows.  

--jkl

odbc.c:3525:SQLFetch: NO_DATA_FOUND
odbc.c:581:SQLMoreResults(0x806f600)
token.c:495:tds_process_tokens(0x806f200, 0xbfbff2f0, 0xbfbff2f4, 0x15914)
util.c:162:Changed query state from PENDING to READING
token.c:510:processing result tokens.  marker is  81(TDS7_RESULT)
token.c:1520:processing TDS7 result metadata.
mem.c:563:tds_free_all_results()
token.c:1544:set current_results (3 columns) to tds->res_info
token.c:1555:setting up column 0
token.c:1490:tds7_get_data_info: 
        colname = t (1 bytes)
        type = 38 (integer-null)
        server's type = 38 (integer-null)
        column_varint_size = 1
        column_size = 4 (4 on server)
token.c:1555:setting up column 1
token.c:1490:tds7_get_data_info: 
        colname = r (1 bytes)
        type = 109 (float-null)
        server's type = 109 (float-null)
        column_varint_size = 1
        column_size = 4 (4 on server)
token.c:1555:setting up column 2
token.c:3294:adjust_character_column_size:
        Server charset: CP1252
        Server column_size: 30
        Client charset: ISO-8859-1
        Client column_size: 30
token.c:1490:tds7_get_data_info: 
        colname = s (1 bytes)
        type = 39 (varchar)
        server's type = 167 (xvarchar)
        column_varint_size = 2
        column_size = 30 (30 on server)
util.c:162:Changed query state from READING to PENDING
odbc.c:3266:odbc_process_tokens: tds_process_tokens returned 1
odbc.c:3267:    result_type=4049, TDS_DONE_COUNT=0, TDS_DONE_ERROR=0
odbc.c:3337:odbc_process_tokens: returning result_type 4049
odbc.c:608:SQLMoreResults: result_type=4049, row_count=-1, lastrc=0
token.c:495:tds_process_tokens(0x806f200, 0xbfbff2f0, 0xbfbff2f4, 0x15914)
util.c:162:Changed query state from PENDING to READING
token.c:510:processing result tokens.  marker is  ff(DONEINPROC)
token.c:2201:tds_process_end: more_results = 1
                was_cancelled = 0
                error = 0
                done_count_valid = 1
token.c:2232:                rows_affected = 0
util.c:162:Changed query state from READING to PENDING
odbc.c:3266:odbc_process_tokens: tds_process_tokens returned 1
odbc.c:3267:    result_type=4054, TDS_DONE_COUNT=10, TDS_DONE_ERROR=0
odbc.c:3331:odbc_process_tokens: processed TDS_DONEINPROC_RESULT
odbc.c:608:SQLMoreResults: result_type=4054, row_count=0, lastrc=0
SQL_SUCCESS
odbc::ncolumns ... odbc.c:4097:SQLNumResultCols(0x806f600, 0xbfbff352)
3
odbc.c:1696:SQLDescribeCol(0x806f600, 1, 0xbfbff110, 512, 0xbfbff10e,
0xbfbff108, 0xbfbff104, 0xbfbff10c, 0xbfbff10a)
odbc.c:1696:SQLDescribeCol(0x806f600, 2, 0xbfbff110, 512, 0xbfbff10e,
0xbfbff108, 0xbfbff104, 0xbfbff10c, 0xbfbff10a)
odbc.c:1696:SQLDescribeCol(0x806f600, 3, 0xbfbff110, 512, 0xbfbff10e,
0xbfbff108, 0xbfbff104, 0xbfbff10c, 0xbfbff10a)
odbc::next_row ... eof
row     t       r       s
odbc::ncolumns ... odbc.c:4097:SQLNumResultCols(0x806f600, 0xbfbff412)
3
odbc::extract column 3 to a cell,  SQLGetData... 
SQLGetData(0x806f600, 3, 12, 0x806b220, 31, 0xbfbff248);
odbc.c:4565:SQLGetData(0x806f600, 3, 1, 0x806b220, 31, 0xbfbff248)
SQL_ERROR
error.c:630:SQLGetDiagField(3, 0x806f600, 0, 2, 0xbfbff208, 4, 0xbfbfedde)
error.c:609:SQLGetDiagRec(3, 0x806f600, 1, 0xbfbff1f0, 0xbfbff1ec,
0xbfbfede0, 1024, 0xbfbfedde)
error.c:510:_SQLGetDiagRec(3, 0x806f600, 1, 0xbfbff1f0, 0xbfbff1ec,
0xbfbfede0, 1024, 0xbfbfedde)
dbstreams::cell& dbstreams::providers::odbc<STATUS>::Extract(int) const
[with STATUS = dbstreams::dbstatus]:538:
        [SQLGetData]:
        error 0: "
[1] 24000: 0: [FreeTDS][SQL Server]Invalid cursor state."  (state is
failbit), 0 rows