FreeTDS Development Group

["ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>]

> 
> 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.  
> 

You have to consider that a empty resultset IS a resultset (at least in
odbc). A resultset without rows is normal.

> 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'
> 

In odbc you get 3 resultset
- first select with 3 columns and one row
- second select with 3 columns and 0 rows
- third select with 1 column and 1 row

> 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.  
> 

The application should call SQLFetch before SQLGetData.

To complicate all things odbc 2 is different from odbc 3. In odbc 3 you
can have even resultset without columns (like inserts or updates!)

> 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

freddy77