Re: Prepared statements with no parameters exhibit strangebehaviour

> 
> I noticed some strange behaviour last week when using ODBC to 
> prepare and
> execute a statement with no parameters. This could be readily 
> reproduced using
> a call sequence along the lines of:
>   SQLPrepare(handle, "select * from foo where bar = 1", SQL_NTS);
>   while (1) {
>     SQLExecute(handle)
>     SQLNumResultCols(handle, &nCols)
>     printf("Number of columns: %i", nCols);
>     while (SQLFetch(handle) != SQL_NO_DATA) {
>       // Some code (or no code)
>     }
>     SQLFreeStmt(handle, SQL_CLOSE)
>   }
> 
> This call sequence would produce something like:
>   Number of columns: 65
>   Number of columns: 0 
>   Number of columns: 65
>   Number of columns: 0 
>   Number of columns: 65
>   Number of columns: 0 
>   Number of columns: 65
>   Number of columns: 0 
>   ...
> assuming it didn't just die with:
> 08S01 (20004): [unixODBC][FreeTDS][SQL Server]Read from the 
> server failed
> on the third call to SQLExecute.
> 
> Removing the calls to SQLFetch would eliminate the problem 
> (number of columns
> would always be 65), as would preparing a statement with 
> parameters. I tracked
> this down today (with the aid of tdsdump) to an extra query 
> being sent to the
> server when a prepared statement with no parameters is executed. When
> SQLFetch is *not* called, the driver sends a cancel and 
> throws away packets
> until it finds one with the cancel flag set (that's what the 
> function doc of
> tds_process_cancel says, although I'm not 100% convinced that 
> it does this -
> it appears to return the first time tds_process_tokens 
> returns), leaving the
> token stream in a sane state for the next call to SQLExecute. 
> When SQLFetch
> *is* called, the one result is already retrieved, the cancel packet is
> unnecessary and the token stream is left in the non-sane 
> state with the reply
> to the empty query the next token to be read (the 0-column result).
> 
> The internal call sequence for this is as follows:
>   _SQLExecute ---> tds_submit_execute ---> tds_send_emulated_execute
>                                        |
>                                        |-> tds_query_flush_packet
> 
> However, when the prepared statement has no parameters there 
> is an additional
> call to tds_flush_packet within tds_send_emulated_execute. 
> The 'real' packet
> gets flushed with this first call, then the call to 
> tds_query_flush_packet
> sends an empty query (which the server duly responds to with 
> an empty result
> set). The fix is to remove this extraneous call to 
> tds_flush_packet. I've
> attached two patches - one for the 0.82 release and one for 
> the current CVS
> version.
> 
> Cheers,
>     Chris
> 

Applied modified versions of the patches. tds_query_flush_packet was really wrong in
tds_send_emulated_execute!
I noted however that probably you are using wrong protocol version. ODBC should use this
function only on Sybase with some text fields or using a former protocol (4.2). Currently
you should use protocol version 5.0 for Sybase, 8.0 for mssql2k and 7.0 for mssql < 2k.

freddy77

_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds