Skip to Content.
Sympa Menu

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

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Lowden, James K" <james.k.lowden AT alliancebernstein.com>
  • To: "TDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: [freetds] SQLMoreResults() and dbresults() sometimes return extra phantom resultset
  • Date: Wed, 5 Jul 2006 18:57:26 -0400

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.



  • [freetds] SQLMoreResults() and dbresults() sometimes return extra phantom resultset, Lowden, James K, 07/05/2006

Archive powered by MHonArc 2.6.24.

Top of Page