Prepared statements with no parameters exhibit strange behaviour

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

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

diff -Naur freetds-0.82.orig/src/tds/query.c freetds-0.82/src/tds/query.c
--- freetds-0.82.orig/src/tds/query.c	2008-02-12 15:41:51.000000000 +0000
+++ freetds-0.82/src/tds/query.c	2008-08-21 17:57:02.000000000 +0100
@@ -2839,21 +2839,20 @@
 	START_QUERY;
 	if (!num_placeholders) {
 		tds_put_string(tds, query, -1);
-		return tds_flush_packet(tds);
-	}
-
-	s = query;
-	for (i = 0;; ++i) {
-		e = tds_next_placeholder(s);
-		tds_put_string(tds, s, e ? e - s : -1);
-		if (!e)
-			break;
-		/* now translate parameter in string */
-		tds_put_param_as_string(tds, params, i);
+	} else {
+		s = query;
+		for (i = 0;; ++i) {
+			e = tds_next_placeholder(s);
+			tds_put_string(tds, s, e ? e - s : -1);
+			if (!e)
+				break;
+			/* now translate parameter in string */
+			tds_put_param_as_string(tds, params, i);
 
-		s = e + 1;
+			s = e + 1;
+		}
 	}
-	
+
 	return TDS_SUCCEED;
 }

diff -Naur freetds.orig/src/tds/query.c freetds/src/tds/query.c
--- freetds.orig/src/tds/query.c	2008-08-18 14:31:28.000000000 +0100
+++ freetds/src/tds/query.c	2008-08-21 18:14:04.000000000 +0100
@@ -2970,21 +2970,20 @@
 	START_QUERY;
 	if (!num_placeholders) {
 		tds_put_string(tds, query, -1);
-		return tds_flush_packet(tds);
-	}
-
-	s = query;
-	for (i = 0;; ++i) {
-		e = tds_next_placeholder(s);
-		tds_put_string(tds, s, e ? e - s : -1);
-		if (!e)
-			break;
-		/* now translate parameter in string */
-		tds_put_param_as_string(tds, params, i);
+	} else {
+		s = query;
+		for (i = 0;; ++i) {
+			e = tds_next_placeholder(s);
+			tds_put_string(tds, s, e ? e - s : -1);
+			if (!e)
+				break;
+			/* now translate parameter in string */
+			tds_put_param_as_string(tds, params, i);
 
-		s = e + 1;
+			s = e + 1;
+		}
 	}
-	
+
 	return TDS_SUCCEED;
 }