FreeTDS Development Group

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

On 10 Oct 2003, John Anderson <ardour AT semiosix.com> wrote:
> On Fri, 2003-10-10 at 05:37, James K. Lowden wrote:
> > On 09 Oct 2003, John Anderson <ardour AT semiosix.com> wrote:
> > > 
> > > Seems to me that from an application point of view it 
> > > would make sense
> > > for a commit attempt to automatically cancel any pending 
> > > result sets?
> > 
> > Philosophically, regardless of what documentation says?  
> > You could take
> > that point of view, or you could say that an out-of-place 
> > commit indicates
> > a logic flaw in the application, because results have to be 
> > fetched or explicitly cancelled.  
> 
> Not wanting to be argumentative about this

I don't think you're being argumentative, John, for the record, and I hope
you don't think I am.  I didn't realize at the outset that we were talking
about the relationship of cursors and transactions.  It's not something I
know much about.  

In the log I saw the commit text being issued before the results had been
fetched, and I saw the succeeding results arrive subsequently, so it was
easy to explain what happened.  Only now are we establishing whether our
ODBC driver is doing the right thing.  

> http://msdn.microsoft.com/library/default.asp?url=/library/en-
> us/odbc/htm/odch14pr_8.asp
...
> Committing or rolling back a transaction has the following effect on
> cursors and access plans:
> 
>       * All cursors are closed, and access plans for prepared 
>         statements on that connection are deleted.
>       * All cursors are closed, and access plans for prepared 
>         statements on that connection remain intact.
>       * All cursors remain open, and access plans for prepared
>         statements on that connection remain intact.

Clarification: that's an OR clause.  It will have one of those effects,
depending I think on the driver's implementation.  

Microsoft has this to say about SQLEndTran in the SQL Server 7.0 Books
Online:

"By default, the Microsoft® SQL Server(tm) ODBC driver closes a
statement's
associated cursor when SQLEndTran commits or rolls back an operation.
Server
cursors remain open after this operation if SQL_COPT_SS_PRESERVE_CURSORS
has
been set to SQL_PC_ON."  

Are we talking about server-side cursors here, and if so, what's the state
of SQL_COPT_SS_PRESERVE_CURSORS?  

> Which to me implies that there's no need to explicitly close a cursor
> before committing or rolling back a transaction, because the call to
> SqlEndTran will do it for me - if that's the option the driver
> implements. 

I'll buy that.  

> And the driver will also give me back the relevant value
> from SqlGetInfo with SQL_CURSOR_COMMIT_BEHAVIOR.

Does the FreeTDS driver return SQL_CB_CLOSE?  

+++

I think I know why M$ chose the behavior they did: there's no other choice
with SQL Server.  If results are in the pipeline (as they will be with
cursors), there's no way to transmit the COMMIT request to a SQL Server
without first fetching/cancelling the results.  Fetching could take time
and
isn't implied by the commit.  Cancelling is faster and obviously
invalidates
the cursor(s), so closing them is the natural choice.  

It looks to me that you've discovered a bug.  We can't blindly pass the
COMMIT request; we have to close any and all cursors first.  To
demonstrate
that clearly, and to facilitate getting it fixed, I'm sure it would help
if
you submitted a unit test with the most minimal illustration of the
behavior.  I myself don't plan any work on the ODBC driver, but I'm sure
Frediano would appreciate a test.  

Oy.  And Brian thought writing an ODBC driver would be easy....

--jkl