gmane.comp.db.tds.freetds

headers Chris Reeves | 21 Aug 19:16

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

Prepared statements with no parameters exhibit strange behaviour