[freetds] SQLMoreResults() and dbresults() sometimes return extra phantom resultset

Lowden, James K james.k.lowden at alliancebernstein.com
Wed Jul 5 18:57:26 EDT 2006


Here is the text of the procedure we're testing with in the rpc.c unit
tests:

CREATE PROCEDURE %s 
          @null_input varchar(30) OUTPUT 
        , @first_type varchar(30) OUTPUT 
        , @nullout int OUTPUT
        , @nrows int OUTPUT 
        , @c varchar(20)
AS 
BEGIN 
select @null_input = max(convert(varchar(30), name)) from systypes 
select @first_type = min(convert(varchar(30), name)) from systypes 
select name from sysobjects where 0=1
select distinct convert(varchar(30), name) as 'type'  from systypes 
        where name in ('int', 'char', 'text') 
select @nrows = @@rowcount 
select distinct convert(varchar(30), name) as name  from sysobjects
where type = 'S' 
return 42 
END


The above stored procedure returns 3 results: 
	0 rows
	3 rows
	N rows (N > 15)

It also has 4 output parameters and, of course, a return status.  

The tests are in CVS HEAD:

$ ident $(find src -name rpc.c | grep unit)
src/dblib/unittests/rpc.c:
     $Id: rpc.c,v 1.25 2006/07/05 19:44:52 jklowden Exp $

src/odbc/unittests/rpc.c:
     $Id: rpc.c,v 1.2 2006/07/05 19:44:52 jklowden Exp $

SQLMoreResults() and dbresults() both sometimes return SUCCEED *4*
times, indicating 4 resultsets.  The fourth is a phantom, and I bet
that's what's upsetting DBD::ODBC.  AFAICT SQLMoreResults() always does
this.  

dbresults() sometimes behaves correctly.  However, if dbhasretstat() is
*not* called, the phantom fourth resultset appears!  

In src/dblib/unittests/rpc.c you'll find:

#define NORMAL_PROCESSING 0
#define USE_DBHASRETSTAT 0
#if NORMAL_PROCESSING

If either #define is 1, the test succeeds.  As it is, it fails.  

I also discovered something odd about the dbretstatus() documentation.
(How long have I been doing this, and I *still* discover things?)  

Microsoft:

	"Process the normal results, and then call dbretstatus after
dbresults returns ... NO_MORE_RESULTS...."

Sybase:

	"When a stored procedure has been executed as an RPC command
using
dbrpcinit, dbrpcparam, and dbrpcsend, then the return status can be
retrieved after all other results have been processed."
[but]
	"If the batch can contain multiple commands, then the return
status
should be retrieved inside the dbresults loop, after all rows have been
fetched with dbnextrow."

My dblib unit test is therefore wrong.  It calls dbrpcsend(); according
to both vendors, it should fetch parameters and the return status
*after* the last call to dbresults(), not inside the loop.  

Processing order should be:
	
	dbinit
	dblogin
	dbopen
	dbrpcinit
	dbrpcparam ...
	dbrpcsend
	dbsqlok
	dbresults ...
		dbnumcols
		dbbind ...
		dbnextrow ...
	dbhasretstat
	dbretstatus
	dbnumrets
		dbretname
		dbrettype
		dbretlen
		dbretdata
	dbexit

FWIW, I don't understand the TDS_PARAM_TOKEN code in src/tds/token.c at
line 617.  I think output parameter storage should be kept in the TDS
structure as a linked list or array (as in db-lib), and as each
parameter arrives from the server, we stuff it into that list.  When the
API library needs the parameters -- bound or not -- they're there.  If
we don't free the storage until the next query is sent, the client can
safely retrieve it when he likes.  

Log follows.

--jkl

=== begin ===
build/src/dblib/unittests/rpc at Wed Jul 5 16:05:08 EDT 2006:
Creating procedure #t0022
executing dbrpcinit
executing dbrpcparam
executing dbrpcparam
executing dbrpcparam
executing dbrpcparam
executing dbrpcparam
executing dbrpcsend
executing dbsqlok
bound 1 of 1 columns ('name') in result 1.
	name
	-----------
bound 1 of 1 columns ('type') in result 2.
	type
	-----------
	char
	int
	text
bound 1 of 1 columns ('name') in result 3.
	name
	-----------
	syscolumns
	syscomments
	sysdepends
	sysfilegroups
	sysfiles
	sysfiles1
	sysforeignkeys
	sysfulltextcatalogs
	sysfulltextnotify
	sysindexes
	sysindexkeys
	sysmembers
	sysobjects
	syspermissions
	sysproperties
	sysprotects
	sysreferences
	systypes
	sysusers
bound 1 of 0 columns ('(null)') in result 4.
	(null)
	-----------
DB-LIBRARY error (severity 7, dberr 20032, oserr 0, dberrstr Attempt to
bind to a non-existent column, oserrstr Undefined error: 0):
Expected retname to be '@nrows', got <NULL> instead.

build/src/odbc/unittests/rpc at Wed Jul 5 16:05:08 EDT 2006:

connecting
odbctest
--------

Dropping procedure freetds_odbc_rpc_test
Creating procedure freetds_odbc_rpc_test
running test
executing SQLAllocStmt
executing SQLBindParameter for parameter 1
executing SQLBindParameter for parameter 2
executing SQLBindParameter for parameter 3
executing SQLBindParameter for parameter 4
executing SQLBindParameter for parameter 5
executing SQLBindParameter for parameter 6
executing SQLPrepare: {?=call freetds_odbc_rpc_test(?,?,?,?,?)}
executing SQLExecute
executing SQLMoreResults...
executing SQLNumResultCols for result set 1
executing SQLDescribeCol for 1 column 
col   name             type  size scale nullable
----- --------------- ----- ----- ----- --------
1     type               12    30     0        Y
executing SQLFetch...
	type                          
	------------------------------
	char                          	( 4 bytes)
	int                           	( 3 bytes)
	text                          	( 4 bytes)
done.
0 rows expected, 3 found
executing SQLNumResultCols for result set 2
executing SQLDescribeCol for 1 column 
col   name             type  size scale nullable
----- --------------- ----- ----- ----- --------
1     name               12    30     0        Y
executing SQLFetch...
	name                          
	------------------------------
	syscolumns                    	(10 bytes)
	syscomments                   	(11 bytes)
	sysdepends                    	(10 bytes)
	sysfilegroups                 	(13 bytes)
	sysfiles                      	( 8 bytes)
	sysfiles1                     	( 9 bytes)
	sysforeignkeys                	(14 bytes)
	sysfulltextcatalogs           	(19 bytes)
	sysfulltextnotify             	(17 bytes)
	sysindexes                    	(10 bytes)
	sysindexkeys                  	(12 bytes)
	sysmembers                    	(10 bytes)
	sysobjects                    	(10 bytes)
	syspermissions                	(14 bytes)
	sysproperties                 	(13 bytes)
	sysprotects                   	(11 bytes)
	sysreferences                 	(13 bytes)
	systypes                      	( 8 bytes)
	sysusers                      	( 8 bytes)
done.
3 rows expected, 19 found
executing SQLNumResultCols for result set 3
executing SQLDescribeCol for 0 columns
col   name             type  size scale nullable
----- --------------- ----- ----- ----- --------
executing SQLFetch...
	                              
	------------------------------
SQL error 24000 -- [FreeTDS][SQL Server]Invalid cursor state
../../../../src/odbc/unittests/rpc.c:193 Unable to execute SQLFetch

===  end  ===


-----------------------------------------
The information contained in this transmission may be privileged and
confidential and is intended only for the use of the person(s) named
above. If you are not the intended recipient, or an employee or agent responsible
for delivering this message to the intended recipient, any review, dissemination,
distribution or duplication of this communication is strictly prohibited. If you are
not the intended recipient, please contact the sender immediately by reply e-mail
and destroy all copies of the original message. Please note that we do not accept
account orders and/or instructions by e-mail, and therefore will not be responsible
for carrying out such orders and/or instructions.  If you, as the intended recipient
of this message, the purpose of which is to inform and update our clients, prospects
and consultants of developments relating to our services and products, would not
like to receive further e-mail correspondence from the sender, please "reply" to the
sender indicating your wishes.  In the U.S.: 1345 Avenue of the Americas, New York,
NY 10105.



More information about the FreeTDS mailing list