Skip to Content.
Sympa Menu

freetds - Re: [freetds] SQLMoreResults and odbc/unittests/rpc.c

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] SQLMoreResults and odbc/unittests/rpc.c
  • Date: Thu, 13 Jul 2006 10:16:12 +0200

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





Archive powered by MHonArc 2.6.24.

Top of Page