FreeTDS Development Group

["ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>]

> > From: ZIGLIO, Frediano, VF-IT
> > Sent: Wednesday, July 12, 2006 10:27 AM
> > > 
> > > > From: Frediano Ziglio
> > > > Sent: Wednesday, July 12, 2006 1:08 AM
> > > > 
> > > > Well, currently test should work, I tested it with ms 
> odbc and it
> > > > returns 3 resultset (and IMO it has to do so...). So the only
> > > > problem is that SQLMoreResults returns one more SQL_SUCCESS...
> > > > 
> > > > Il giorno mar, 11/07/2006 alle 18.44 -0400, Lowden, James K ha
> > > > scritto:
> > > > 
> > > > > The problem is row_count=1; it should be TDS_NO_COUNT.
> > > > ...
> > > > > I hope that by fixing this, we'll make DBD::ODBC happy.
> > > > > 
> > > > 
> > > > ?? according to
> http://freetds.sourceforge.net/out/test/test.1.html
> > > > DBD::ODBC is already happy..
> > > 
> > > Sometimes DBD::ODBC is happy.  But look at this output:
> > ...
> > 
> > Ok, I just discovered the problem...
> > 
> > The key is the comment above in moreandcount.c:
> > 
> > /* MS driver collapse 2 recordset... very bad */
> > 
> > consider something like
> > 
> > select * from table
> > delete from table 
> > 
> > using prepared statements or inside a rpc it produce
> > 
> > ROWFMT
> > ROW
> > ROW
> > ...
> > DONEINPROC with row number (assume X)
> > DONEINPROC with row number (assume Y)
> > 
> > MS odbc returns a single recordset of X row and returns Y row
> > numbers (strange but true). For this reasons we collected second
> > row count in different location in order to be able to produce the
> > "phanton recordset"... now something (DM or DBD::ODBC) don't like
> > this strange recordset without rows, columns and messages... what
> > should we do?? Mimic MS behavior (that is very easy) ?? Discard
> > the phantom??
> 
> In a moment of desparation/inspiration, I read the MS-DAK 
> docs and tried
> my test using ActiveState Perl DBD::ODBC and Microsoft's ODBC driver.
> (ISTR at one time DBD::ODBC had really only been tested with 
> Microsoft's
> driver.)  I think we may want to back out my ODBC "fix" (and maybe
> yours).  
> 
> Here is my definition of a resultset:
> 
> 0.  It's caused by a SELECT statement.
> 1.  It begins with a TDS7_RESULT.
> 2.  It has zero or more ROW packets (regular and compute).
> 
> Unfortunately, Microsoft disagrees:
> 
>       "A result is something returned by the data source after a
> statement is executed. ODBC has two types of results: result sets and
> row counts. Row counts are the number of rows affected by an update,
> delete, or insert statement. Batches, described in "Batches of SQL
> Statements" in Chapter 9: Executing Statements, can generate multiple
> results."
> 

Just to complicate things a bit it seems that print or raiserror can
provide a recordset without columns, rows and even row number !!!

> On Windows, my original test succeeds:  
> 
> $ perl E:samples\odbc_rpc.pl -D dbi:ODBC:mpquant [...]
> simdb..IsFreeTdsBroken
> odbc_async_exec is: 1
> Binding parameter #1, the return code
> 
> Executing: "{? = call simdb..IsFreeTdsBroken }" with parameters ''
> execute returned: '-1'
> Result #1:
> [LatestInputsmDate]
> '2006-07-01 00:00:00'
> 
> I.e., "while ( $sth->{Active} )" executes once.  
> 
> Then I made another procedure, like this:
> 
>       CREATE procedure aa 
>       as
>               select * from a 
>               delete a where 0=1
> 
> and on Windows odbc_rpc.pl fails:
> 
> $ perl E:samples\odbc_rpc.pl [...] testdb..aa
> odbc_async_exec is: 1
> Binding parameter #1, the return code
> 
> Executing: "{? = call testdb..aa }" with parameters ''
> execute returned: '0E0'
> Result #1:
> [a]
> '1'
> '2'
> '3'
> '4'
> Result #2:
> ===> state: HY010 msg:  Function sequence error
> DBD::ODBC::st fetchrow failed: (DBD: st_fetch/SQLFetch err=-1) at
> E:\projects\freetds\cvs\freetds\samples\odbc_rpc.pl line 115.
> 
> Then I tried it with FreeTDS.  Here's part of the log:
> 
> === begin ===
> $ grep -E 'marker|done_count_valid|affected' dump \
>       | sed 's/^[0-9:.      ]*//g'
> processing result tokens.  marker is  81(TDS7_RESULT)
> processing result tokens.  marker is  d1(ROW)
> processing row tokens.  marker is  d1(ROW)
> processing row tokens.  marker is  d1(ROW)
> processing row tokens.  marker is  d1(ROW)
> processing row tokens.  marker is  d1(ROW)
> processing row tokens.  marker is  ff(DONEINPROC)
> done_count_valid = 1
> rows_affected = 4
> processing result tokens.  marker is  ff(DONEINPROC)
> done_count_valid = 1
> rows_affected = 0
> processing result tokens.  marker is  79(RETURNSTATUS)
> processing result tokens.  marker is  fe(DONEPROC)
> done_count_valid = 0
> === end ===
> 
> It's not RETURNSTATUS that causes the extra iteration, it's the two
> DONEINPROC tokens (as you said).  
> 
> Here's what Microsoft's ISQL.EXE does:
> 
> 1> aa
> 2> go
>  a
>  -----------
>            1
>            2
>            3
>            4
> 
> (0 rows affected)
> 

Mmm... expected... isql use dblib isn't it?

> db-lib ignores all but the last DONEINPROC.  I think that's 
> what ct-lib
> would do, too.  Here's what Microsoft's OSQL.EXE does:
> 
> 1> exec aa
> 2> go
>  a
>  -----------
>            1
>            2
>            3
>            4
> 
> (4 rows affected)
> (0 rows affected)
> 

??? this is quite strange !!! I would expect result similar to dblib...
I'll try to enable loggings...

> Bah.  It looks like $sth->Active doesn't imply $sth->fetchrow_array.  
> 
> Conclusions:
> 
> 1.  RETURNSTATUS is not a resultset, ever.
> 2.  DONEINPROC is a resultset in ODBC, fsvo "resultset". 

yes and not... it seems at least to collapse multiple doneinproc...

> 3.  db-lib ignores all but the final DONEINPROC.  
> 4.  dbresults returns SUCCEED only when metadata (TDS7_RESULT or
> similar) are present.  

I'm not use, I would try a with a simple delete

> 5.  $sth->Active doesn't imply $sth->fetchrow_array. 
> 
> Tomorrow I'll figure out what to do about DBD::ODBC.  
> 

More on this... I did a test with dblib rpc test adding a printf("row
count %d\n", (int) dbcount(dbproc)); just after dbnextrow loop. Here is
ms dblib results, note row number collapsed



found OBON104.test for test in "../../../PWD"
Start
About to logon
About to open OBON104.test
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
        -----------
row count 0
bound 1 of 1 columns ('type') in result 2.
        type
        -----------
        char
        int
        text
row count 1
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
row count 1
retrieving return status...
42
retrieving output parameters...
param name             type length  data
----- --------------- ----- ------ ------------------------------
1     @null_input        47    255  varchar



2     @first_type        47     64  bigint

DB-LIBRARY error (severity 4, dberr 10016, oserr -1, dberrstr Requested
data-con
version does not exist., oserrstr (null)):
3     @nullout           38      0  bigint

4     @nrows             56      4  3
Good: Got 4 output parameters and 1 return status of 42.
Good: Got 3 resultsets and 1 empty resultset.
Dropping procedure
dblib okay on c:\myprog\freetds\unittests\rpc.c



Here is our results



found OPDN106.test for test in "../../../PWD"
Start
About to logon
About to open OPDN106.test
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
        -----------
row count 0
bound 1 of 1 columns ('type') in result 2.
        type
        -----------
        char
        int
        text
row count 3
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
row count 19
retrieving return status...
42
retrieving output parameters...
param name             type length  data
----- --------------- ----- ------ ------------------------------
1     @null_input        39      7  varchar
2     @first_type        39      6  bigint
3     @nullout           56      0
4     @nrows             56      4  3
Good: Got 4 output parameters and 1 return status of 42.
Good: Got 3 resultsets and 1 empty resultset.
Dropping procedure
dblib okay on rpc.c



Well... it seems that all collapse so... I'll collapse results too :(
I still don't understand why test with osql do not collapse select with
delete... from moreandcount it seems that it collapse select with
update...

freddy77