Is there a bug with “set chained” in setAutoCommit

问题:

I am having some confusion with set chained statement in setAutoCommit() method in net.sourceforge.jtds.jdbc.Driver

The source code says:

2161  if (serverType == Driver.SYBASE) {
2162            if (autoCommit) {
2163                sql.append("SET CHAINED OFF");
2164            } else {
2165                sql.append("SET CHAINED ON");
2166            }

However, shouldn't it be backwards, and chaining should be OFF for autoCommit==false?

---

The reason I ran into this is as follows:

I am writing a Java app which needs to do some complicated SQL and roll back all of it if any of it fails:

• Open Sybase connection using net.sourceforge.jtds.jdbc.Driver

• Call setAutoCommit(false)

• Do SQL1

• Call stored proc 'MySP1'

  • Stored proc MySP1' is NOT under my control

  • It has EXEC sp_procxmode 'dbo.MySP1','unchained'

• Do SQL2

• If SQL2 fails, roll back everything (including SQL1), otherwise commit.

Having done that, I get the following error from MySP1:

 Stored procedure 'MySP1' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.

回答1:

I had almost the same problem and solved it by running following SQL. hopefully it can help you.

sp_procxmode your_stored_Procedure, 'anymode'

in your case your_stored_Procedure = MySP1 then you should run following code:

 sp_procxmode MySP1, 'anymode'