Skip to Content.
Sympa Menu

freetds - Re: [freetds] Prepared statements with no parameters exhibit strangebehaviour

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] Prepared statements with no parameters exhibit strangebehaviour
  • Date: Fri, 22 Aug 2008 08:49:59 +0200

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

Attachment: smime.p7s
Description: S/MIME cryptographic signature




Archive powered by MHonArc 2.6.24.

Top of Page