[freetds] Still done handling... again

James K. Lowden jklowden at schemamania.org
Wed May 4 00:23:24 EDT 2005


ZIGLIO, Frediano, VF-IT wrote:
> Well, as usual I'm doing come tests with ODBC. I discovered that ms odbc
> try to read as mush as done/param/status tokens as it can. As result
> connection is free after last SQLFetch. However it collapse multiple
> done causing on prepared statement like
> 
> select
> update 
> 
> to return a single result set with wrong row number (from update) but
> correct columns (from select)...

When you call a stored procedure, what does "rows affected" really mean? 
Suppose the stored procedure was:

	create proc c as 
	create table #t(t int)
	insert #t values (1)
	insert #t values (2)
	select * from #t
	update #t set t = 5 where t = 2
	delete #t where t > 9

The API returns only 1 rowcount when you "exec c", right?  But we have:

	2 inserted, 
	2 selected, 
	1 updated
	0 deleted
	= =========
	5 total

What should we say?  5? 2? 0?  With current CVS, ct-lib (in sqsh) returns
2, and db-lib (bsqldb) return "not available".  That's with both TDS 5.0
and 7.0.  

I agree Microsoft's choice is very odd.  I have to wonder if it's
intentional.  

Maybe the rule should be:

1.  If the query produced a result set (e.g. packet 81), use that
rowcount.  It's always the DONEINPROC immediately after the ROW packet(s).
 
2.  Else, use the last DONEINPROC rowcount.  

> In FreeTDS the above sequence cause 2 result set, one for select and one
> for update.
> 
> However I discovered a weird situation... raiserror test create a this
> procedure
> 
> CREATE PROCEDURE #tmp1
>     @InParam int,
>     @OutParam int OUTPUT,
>     @OutString varchar(20) OUTPUT
> AS
>      SET @OutParam = @InParam
>      SET @OutString = 'This is bogus!'
>      SELECT 'Here is the first row' AS FirstResult
>      RAISERROR('An error occurred.', @InParam, 1)
>      SELECT 'Here is the last row' AS LastResult WHERE 0=1
>      RETURN (0)
...
> Look at the end !!! After 'LastResult' row there are 2 doneinproc, one
> for select and one for return!!! However these tokens are
> indistinguible!!! Even statement type is the same (0x00C1). 

Bill bumped into this with ct-lib.  TDS 7.0 can carry several DONEINPROC
packets, depending on what happened on the server.  You get them for
triggers, for example.  And replication, too, IIRC.  

> The only way
> to distinguish them is that first "select" has some rows before... Now
> how to fix it? Reproduce strange MS way or detect wrong "select"
> doneinproc tokens??

I must say this is messy.  But not impossible.  The ff after the 81 always
carries the data rowcount.  That's the one we want ISTM.  The others, I
believe, can be ignored.  

Packets are:

token:	81 (Result)
token:	d1 (Data Row) 
token:	ff (Done Inside Process, 1 row)

token:	ab (Non-error Message)
token:	ff (Done Inside Process, no rowcount) 

token:	81 (Result) <= clue
                    <= no data
token:	ff (Done Inside Process, 0 rows) <= data rowcount
token:	ff (Done Inside Process, 1 row)  <= other proc activity
token:	79 (Return Status)
token:	ac (Output Parameter)
token:	ac (Output Parameter) 
token:	fe (Process Done, no rowcount) 

I'm attaching my parsing of your packet, in case it helps.  

Regards, 

--jkl



0000 ff 11 00 c1 00 01 00 00-00 ff 11 00 c1 00 01 00 |........ ........|
0010 00 00 

token:	81 (Result)
	1 col: 		01 00 
	usertype:	00 00 
	flags:		00-00 
	XSYBVARCHAR:a7
	length:		15 00 
	collation:	09 04 d0 00 34 
	namelen:	0b 
	name:		46 00 69 00 72 00-73 00 74 00 52 00 65 00 73 00
75 00 6c 00 74 00

token:	d1 (Data Row) 
	length:		15 00 
	data:		48 65 72 65 20 69 73 20 74 68 65 20 66-69 72 73
74 20 72 6f 77

token:	ff (Done Inside Process)
	flags:		11 00 
	unknown:	c1 00 
	1 row:		01 00 00-00 
	
token:	ab (Non-error Message)
	length:		3e 01	(318 bytes) 
	mgs number:	50 c3 00 00 
	state:		01 
	level:		05 
	msg len: 	12 00 
	text: 		41 00 6e 00-20 00 65 00 72 00 72 00 |....A.n.
.e.r.r.|
0070 6f 00 72 00 20 00 6f 00-63 00 63 00 75 00 72 00 |o.r. .o. c.c.u.r.|
0080 72 00 65 00 64 00 2e 00
	slen:		07 
	server:		4f 00 50 00 44 00 4e 00 31 00 30 00 36 00 
	plen:		80
	process:	     23 00 74 00 6d 00 70 00 |.1.0.6.. #.t.m.p.|
00a0 31 00 5f 00 5f 00 5f 00-5f 00 5f 00 5f 00 5f 00 |1._._._. _._._._.|
00b0 5f 00 5f 00 5f 00 5f 00-5f 00 5f 00 5f 00 5f 00 |_._._._. _._._._.|
00c0 5f 00 5f 00 5f 00 5f 00-5f 00 5f 00 5f 00 5f 00 |_._._._. _._._._.|
00d0 5f 00 5f 00 5f 00 5f 00-5f 00 5f 00 5f 00 5f 00 |_._._._. _._._._.|
00e0 5f 00 5f 00 5f 00 5f 00-5f 00 5f 00 5f 00 5f 00 |_._._._. _._._._.|
00f0 5f 00 5f 00 5f 00 5f 00-5f 00 5f 00 5f 00 5f 00 |_._._._. _._._._.|
0100 5f 00 5f 00 5f 00 5f 00-5f 00 5f 00 5f 00 5f 00 |_._._._. _._._._.|
0110 5f 00 5f 00 5f 00 5f 00-5f 00 5f 00 5f 00 5f 00 |_._._._. _._._._.|
0120 5f 00 5f 00 5f 00 5f 00-5f 00 5f 00 5f 00 5f 00 |_._._._. _._._._.|
0130 5f 00 5f 00 5f 00 5f 00-5f 00 5f 00 5f 00 5f 00 |_._._._. _._._._.|
0140 5f 00 5f 00 5f 00 5f 00-5f 00 5f 00 5f 00 5f 00 |_._._._. _._._._.|
0150 5f 00 5f 00 5f 00 5f 00-5f 00 5f 00 5f 00 5f 00 |_._._._. _._._._.|
0160 5f 00 5f 00 5f 00 5f 00-5f 00 5f 00 5f 00 5f 00 |_._._._. _._._._.|
0170 5f 00 5f 00 5f 00 5f 00-5f 00 5f 00 5f 00 5f 00 |_._._._. _._._._.|
0180 5f 00 5f 00 5f 00 5f 00-30 00 30 00 30 00 30 00 |_._._._. 0.0.0.0.|
0190 30 00 34 00 42 00 44 00-
	line num:	09 00 


token:	ff (Done Inside Process) 
	flags:		01 00  
	unknown:	f6 00  
	0 rows:		00 00 00 00 

token:	81 (Result) 
	1 col: 		01 00  
	usertype:	00 00- 
	flags:		00 00  
	XSYBVARCHAR:a7 
	length:		14 00  
	collation:	09 04 d0 00 34  
	namelen:	0a  
	name:		4c 00 61 00 73-00 74 00 52 00 65 00 73 |L.a.s
.t.R.e.s|
01c0 00 75 00 6c 00 74 00 

token:	ff (Done Inside Process) 
	flags:		11 00 
	unknown:	c1 00   
	0 rows:		00 00 00 00 

token:	ff (Done Inside Process)  
	flags:		11 00  
	unknown:	c1 00  
	1 row:		01 00 00-00 
	
token:	79 (Return Status)
	status:		00 00 00 00 
	
token:	ac (Output Parameter)
	length:		0d 00 
	clen:		00 
	flags		01 
	usertype:	38 00 00 00 
	datatype:	26 (SYBINTN)
	col size:	04 
	size:		04 
	data:		05 00 00 00 

token:	ac (Output Parameter) 
	length:		1e 00 
	clen:		00 
	flags		01 
	usertype:	a7 00 00 00 
	datatype:	a7 (XSYBVARCHAR)
	col size:	14 00
	colation:	09 04 d0 00 34 
	size:		0e 00 
	data:		54 68 69 73 20 69 73-20 62 6f 67 75 73 21 "This
is  bogus!"

token:	fe (Process Done) 
	flags:		00 00 
	unknown:	e0 00 
	1 row:		01 00 00 00



More information about the FreeTDS mailing list