FreeTDS Development Group

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

On Sun, 07 Mar 2004 10:20:37 +0100, Frediano Ziglio <freddyz77 AT tin.it>
wrote:
> >     Create Procedure testinsert 
> >             @value int
> >     As
> >     insert into TestTransaction values ( @value )
> >     select * from TestTransaction
> >     insert into TestTransaction values ( 'b' )
> > 
> > You will see the 0xAA error packet come back from the server, but
> > SQLExecDirect() will not return an error to the caller.  
> > 
> 
> This is not a real error. Sybase do not allow to create such procedure
> (it seems it optimize and analyze statements much better). 

Ah, it's true.  A "feature" of recent Microsoft servers is that they don't
parse procedures when they're created.  But, I think the same thing can be
made to happen on Sybase servers, because, as you saw, the error arrives
after the DONE_INPROC.  It's quite common for stored procedures to
engender errors.  Consider this procedure:

create proc oops 
as 
select type from systypes where type < 10 
select type / (type - type) from systypes

It installs on Microsoft and Sybase servers, and gives these tokens:

  81(TDS7_RESULT)
  ff(DONEINPROC)
  81(TDS7_RESULT)
  aa(ERROR)
  ff(DONEINPROC)
  79(RETURNSTATUS)
  fe(DONEPROC)

I don't have ODBC installed here at home.  How does transaction.c cope
with it?  Same thing, no?  (I don't have ODBC installed at home and can't
test it right now.)

> Using mssql
> server returns
> 
> DONE_INPROC (insert)
> ROWFMT (select)
> ROW (select)
> DONE_INPROC (select)
> ERROR (insert)
> DONE
> 
> SQLExecDirect stop at ROW so it can't see the error and return
> SQL_SUCCESS. However SQLEndTran fails (TODO: I don't know the correct
> behavior... rollback or just nothing?). It seems however that you can't
> read error after SQLEndTran (this is surely a FIXME).

I don't think the ODBC driver needs to send anything to the server to cope
with the error.  I think the important thing -- and it's very important --
is to reflect back to the client any errors transmitted from the server. 
Then the application can deal with the broken transaction, or whatever
other damage there may be.  

Thanks for looking into this, Freddy.  

Regards, 

--jkl