Skip to Content.
Sympa Menu

freetds - Re: [freetds] possible SQLMoreResults/SQLNumResultCols bug

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] possible SQLMoreResults/SQLNumResultCols bug
  • Date: Tue, 5 Feb 2008 09:13:42 +0100

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




Archive powered by MHonArc 2.6.24.

Top of Page