FreeTDS Development Group

["Lowden, James K" <james.k.lowden AT alliancebernstein.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."

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)

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)

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". 
3.  db-lib ignores all but the final DONEINPROC.  
4.  dbresults returns SUCCEED only when metadata (TDS7_RESULT or
similar) are present.  
5.  $sth->Active doesn't imply $sth->fetchrow_array. 

Tomorrow I'll figure out what to do about DBD::ODBC.  

--jkl



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