FreeTDS Development Group

["ZIGLIO Frediano" <Frediano.Ziglio AT vodafone.com>]

> 
> On Fri, 09 Jan 2004, Stefano Coletta <s.coletta AT uni.it> wrote:
> > Lowden, James K wrote:
> > 
> > >>I'm almost sure to have found a bug in the freetds lib (even 
> > >>0.62rc3)
> > >>that occurs only when I try to do an update or delete against 
> > >>Microsoft 
> > >>SQL Server 2000 SP3a while the database is in Merge 
> Replication. The 
> > >>problem arises when I try to read the "rows affected" 
> property which 
> > >>returns always -1 instead of the correct number of affected 
> > >>rows. 
> > >>    
...
> > >
> > Here they are. I've gzipped the files so they are very small.
> 
> Yes, you've found a bug.  Definitely a post-release issue, 
> and a hard one, IMO.  Thank you for the logs.  
> 
> The issue afaik isn't so much that one server is replicated, 
> as one of how it responds to your update statement.  I can't 
> explain what I'm seeing. 
> Let me parse the log for you, and show you what I mean.  
> 
>   ========
> 
> The good one, freetds.log.ok:
> 
> Received packet @ 2004-01-08 20:06:13.782263
> 0000  fd              <= TDS_DONE
>          10 00                <= 0x0010, done count valid 
>              c5 00    <= transaction state ??
>                    01 00 00 00 <= 0x0001 row affected
> 

This is not transaction state but statement executed (0xC5 update 0xC1
select)
                     
> The transaction state should be between 0 and 5, not 0xC5.  
> Microsoft evidently uses different values, which we 
> apparently half-understand; there's a bit of weirdness in 
> tds_process_end(): 
> 
>       /* ignore error calling RAISERROR */
>       if (TDS_IS_MSSQL(tds) && state == 0xf6)
>               tmp &= ~TDS_DONE_ERROR;
> 

Here 0xF6 is raiserror, MS return failure executing this instruction
however raiserrror does not fail, it just raise an error...

> Interesting to note, by the way, that your log reflects an 
> unimplemented ct_options call, one that's related to transactions:
> 
> 2004-01-08 20:06:13.727183 ct_option: UNIMPLEMENTED 7
> 
> >From include/cspublic:
> 
> #define CS_OPT_CHAINXACTS     7
> 
> However, I doubt this matters.  IIRC, ct_options in 0.61.2 
> returned CS_SUCCEED all the time, and chained transactions 
> are the default, so CS_SUCCEED was accurate, even if accidentally.  
> 
> Anyway, the good log shows a normal TDS_DONE message, with 1 
> row affected.
>  
> 
>   ========
> 
> The bad one, freetds.log.debug:
> 
> Received packet @ 2004-01-08 19:52:58.992128
> 0000  ff              <= TDS_DONEINPROC  
>          01 00                <= 0x0010, done count valid 
>              c0 00    <= transaction state 0x00c0 ?? (OK....)
>                    00 00 00 00 <= zero rows affected
>                    

0x01 is just more coming without row. In ODBC I ignore this token (as MS
does)

>       ff              <= TDS_DONEINPROC  
>          11 00                <= 0x0011, done count valid, 
> more coming 
>                c1 00          <= transaction state 0x00c1 ?? (OK....)
>                    01 00 00 00 <= 0x0001 row affected
>  

This is a count inside a procedure, also ignored by ODBC cause there is
no row.

>       ff              <= TDS_DONEINPROC   
>          01 00                <= 0x0010, done count valid  
>              00 00    <= transaction state zero
>                    00 00 00 00  <= zero rows affected
> 

0x00 ?? However there is no rowcount.

>       79              <= TDS_RETURNSTATUS   
>          00 00 00 00          <= procedure returned zero
>        
> 0020  fd              <= TDS_DONE
>          10 00                <= 0x0010, done count valid 
>              c5 00    <= transaction state ??
>                    00 00 00 00   <= zero rows affected       
> 

0xC5 is update, our statement and 0 rows are returned. So 0 would be
returned by ODBC.

>   ========
> 
> Not quite the same, you know?  ;-)
> 

No :) ODBC should return (correctly 0) however I don't know the CTLib
behaviour in this situation... our FreeTDS return even TDS_DONEINPROC
however I don't know the expected behavior (that is the Sybase CTLib
bahavior)...

> First, you tell me how an UPDATE statement results in not one 
> but three "done in proc" responses (when no procedure was 
> invoked).  Then I'll tell you we don't expect that response 
> for an UPDATE, and that's why our code falls down and gives 
> you a -1 rows affected.  We don't manage, it would seem, to 

It return -1 cause it catch TDS_DONEINPROC without row (so rows_affected
got set to TDS_NO_COUNT)

> notice that the second "done in proc" indicates there was one 
> row affected.  And to tell the truth, we don't know enough 
> about how this is supposed to work to get it right. 
> :-(  Say I.  Maybe someone else knows better.  
> 
> Bill Thompson and Frediano have done most of the work lately 
> decoding the stream.  In working with cursors and dynamic 
> SQL, they've seen some oddball responses from the server, and 
> they might well have some light to shed.  Me?  I just fill 
> your screen with words to keep your eyes busy.  
> 

I expect Bill reply, he knows CTLib better than I do.

> This is a hard one to do correctly.  We don't know you sent 
> an UPDATE, we just parse the server's responses.  It's 
> totally unclear to me how we're expected to divine that the 
> second DONEINPROC carries good rows-affected information, and 
> to ignore the last TDS_DONE, which says no rows were.  
> 
> Peter says it might be triggers, which makes very good sense. 
>  IF triggers always react this way, and produce a reliable 
> stream of DONEINPROC messages that carry predictable 
> rows-affected information, then maybe maybe we have a shot at 
> this.  But I know triggers can be nested and chained and even 
> IIRC recursive nowadays.  
> 

...

freddy77