FreeTDS Development Group

[John Anderson <ardour AT semiosix.com>]

On Mon, 2003-10-13 at 06:19, James K. Lowden wrote:
> > 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 don't think that. I believe it's better to err on the side of
politeness when emailing people I don't know ;-)

> 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.  

I should've been more clear that the log was produced through an ODBC
driver.

> > 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.  

That's how I interpret it too.

> 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?  

Ummm, that's not a constant I'm familiar with. I'm looking at it from an
ODBC point of view.

> > 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?  

To confirm what Peter said - odbc.c:3902

case SQL_CURSOR_COMMIT_BEHAVIOR:
        /* currently cursors are not supported however sql server close 
automaticly cursors on commit */
        USIVAL = SQL_CB_CLOSE;
        break;
case SQL_CURSOR_ROLLBACK_BEHAVIOR:
        USIVAL = SQL_CB_CLOSE;
        break;

> +++
> 
> 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. 

Makes sense to me.

> 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.  

I'll have to haul the relevant code out of a set of c++ wrapper classes.
I'll try to get something to you in the next few days.

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

Well, thank goodness for that. If he hadn't my life would be
considerably more difficult.

bye
John