FreeTDS Development Group

[Brett Knights <knights AT tanner.com>]

in reply to  Craig Spannring
> 
> Brett Knights writes:
> 
>  >  
>  > But not correct:
>  > Statements created from the same connection object 
> shouldn't block each
>  > other. They are all part of the same transaction and 
> should have the
>  > same view of the database. 
> 
> With the TDS protocol you can have only ONE outstanding result per
> connection.  Since most people want multiple 
> com.java.sql.Statement per 
> com.java.sql.Connection

Why? In 13 years of database development I have never had a need for
this. A couple of times a desire for it maybe but on reflection I was
always able to get around this and probably had a better performing
system as a result. 

> the driver is pretty much forced to use a
> separate connections for each statement. 
> 
>  > I don't know it that is per spec but
>  > that is
> 
> You should read the JDBC spec.  
Wow - that seems rather bizarre. I guess if MS put's a feature on a
checklist others must follow suit. 
> 
>  > how all systems I have ever used work. You should be able 
> to begin a
>  > transaction on a connection , create and close any number 
> of statements
>  > and then commit or roll-back the entire transaction. 
> 
> The way you suggest implementing the com.java.sql.Statement interface
> would not permit you to create and use more than one simultaneous
> statement per connection.

I agree but your implementation breaks connection commit/rollback logic.
Of the two I'd rate consistent transaction logic as a higher design
goal. 
>From the API docs - 
"If a connection is in auto-commit mode, then all its SQL statements
will be executed and committed as individual transactions. Otherwise,
its SQL statements are grouped into transactions that are terminated by
a call to either the method commit or the method rollback. "

I see your point about the requirement for multiple open statements but
at least the spec gives a nod to reality by stating that you can "hack"
the requirement for multiple open statements by serializing them (or
something) when the underlying database doesn't easily support the
concept.

> 
>  > The way freetdsJDBC is currently implemented your 
> statements get rolled
>  > back if you close them without committing. Essentially the 
> user has to
>  > treat a statement in freetdsJDBC as they would a 
> connection using any
>  > other driver. In my mind this nulls any thread safe 
> benefit you might
>  > have by the strategy that has been adopted so far.
> 
> The strategy adopted "so far" has nothing to do with thread 
> safeness.  
Sorry, thread safety considerations seemed to be implied by your earlier
response. As it turns out JDBC objects are required to be thread safe
anyway.

> It is simply to allow the user to have multiple statements open per
> connection.  Thread safeness is an important concern, but it did not
> drive this descision.
>