Error - Trancount Reset!

Hi,
I am using the T-SQL commands "BEGIN TRANSACTION", "COMMIT TRANSACTION"
and "ROLLBACK TRANSACTION" via the Statement.Execute method instead of
setting the autoCommit feature of JDBC.
We often have objects that contain transactional updates that may be called
by another object that has already initiated a transaction or that may be
used alone, thus we need a form of transaction nesting which the autoCommit
feature does not provide.
However, we have noticed that the @.@.TRANCOUNT will reset to 0 once a
statement object is created on the same connection - for example, we would
expect:
FIRST OBJECT
BEGIN TRANSACTION
... SOME WORK (TRANCOUNT = 1)
SECOND OBJECT
BEGIN TRANSACTION (TRANCOUNT = 2)
.. SOME WORK
COMMIT TRANSACTION (TRANCOUNT = 1)
FIRST OBJECT
COMMIT TRANSACTION (TRANCOUNT = 0)
However, we have noticed that as soon as a Statement object is created in
the inner transaction, the TRANCOUNT is immediately set to 0. Then, when the
COMMIT TRANSACTION statement is issued, an error is raised because there is
no matching BEGIN TRANSACTION.
Has anyone ever encountered this error? Is it a bug?
Thanks,
Mike
MikeF wrote:

> Hi,
> I am using the T-SQL commands "BEGIN TRANSACTION", "COMMIT TRANSACTION"
> and "ROLLBACK TRANSACTION" via the Statement.Execute method instead of
> setting the autoCommit feature of JDBC.
Very dangerous.

> We often have objects that contain transactional updates that may be called
> by another object that has already initiated a transaction or that may be
> used alone, thus we need a form of transaction nesting which the autoCommit
> feature does not provide.
That doesn't explain why the autoCommit() path won't work. You set autoCommit(false)
and call what you want. anything below needn't know whether it's in a tx or not.
Then commit or roll back.

> However, we have noticed that the @.@.TRANCOUNT will reset to 0 once a
> statement object is created on the same connection - for example, we would
> expect:
> FIRST OBJECT
> BEGIN TRANSACTION
> ... SOME WORK (TRANCOUNT = 1)
> SECOND OBJECT
> BEGIN TRANSACTION (TRANCOUNT = 2)
> .. SOME WORK
> COMMIT TRANSACTION (TRANCOUNT = 1)
> FIRST OBJECT
> COMMIT TRANSACTION (TRANCOUNT = 0)
> However, we have noticed that as soon as a Statement object is created in
> the inner transaction, the TRANCOUNT is immediately set to 0. Then, when the
> COMMIT TRANSACTION statement is issued, an error is raised because there is
> no matching BEGIN TRANSACTION.
> Has anyone ever encountered this error? Is it a bug?
You need to add a connection property selectMethod=cursor. Otherwise
the driver will make *new connections* under the covers to implement
concurrent statements, and each statement will be independent of
the connection/statement on which you think you started a transaction.
This would have been prevented/revealed immediately if you had stuck to
JDBC control calls for defining your transactions.
Joe Weinstein at BEA
> Thanks,
> Mike