FreeTDS Development Group

["Lowden, James K" <james.k.lowden AT alliancebernstein.com>]

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.