FreeTDS Development Group

["ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.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.  
> 

Don't mix SQLMoreResults and dbresults, they are quite different... Yes,
is strange that SQLMoreResults returns 4 SQL_SUCCESS... you should also
see done_handling test. This currently is not a test cause mainly print
some output results without testing it... it seems that proprietary
libraries read some token before our libraries. 

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

This cause dbhasretstat sometimes read some token... as I said our
libraries read less tokens so sometimes dbhasretstat would return false
while proprietary libraries returns true so it try to read some other
tokens in order to make programs happy...

> 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  ===
> 

On the same lines looks at
http://freetds.sourceforge.net/out/test/test127.html (rpc2 PHP tests)...
it seems that dbhasretstat do not behave correctly...

Another question is... 0.64 behave correctly?

Note that all that questions are very longstanding with lot of small
fixes but, it seems, not real fix... the problem is that libraries have
sligtly different behavior that should be tested with care... I think
that dblib is the former library so probably fixing it will help to
understand the real behavior. This is why I start writing done_handling
but I never finish it. For instance some days ago I tried testing
done_handling under windows and I discovered that ms dblib seems to
returns 0 columns instead of -1... also is very odd that
http://freetds.sourceforge.net/out/test/test073.html never detect
retstat... I think I'll start trying all dblib tests under windows

freddy77