FreeTDS Development Group

["James K. Lowden" <jklowden AT schemamania.org>]

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