Skip to Content.
Sympa Menu

freetds - Re: [freetds] Still done handling... again

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT schemamania.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Still done handling... again
  • Date: Wed, 4 May 2005 00:23:24 -0400

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




Archive powered by MHonArc 2.6.24.

Top of Page