Clinton, The biggest help you could give the Sybase code would be to expose the StandardDaoManager.isExplicitTransaction method through the DaoManager interface, and to expose a similar method through the SqlMapClient interface (looks to be a little more difficult). Knowing the explicit/implicit transaction state is the key to avoiding BEGIN TRANSACTION statements, which prevents DDL execution.
Right now, I'm proxying the DaoManager and SqlMapClient interfaces in order to capture explicit/implicit transaction state, a less-than-optimal solution. ---- Scott Severtson Centare Group, LLC > -----Original Message----- > From: Clinton Begin [mailto:[EMAIL PROTECTED] > Sent: Thursday, December 30, 2004 12:12 PM > To: [email protected] > Subject: Re: Sybase CHAINED v. UNCHAINED solution > > Hi Scott, > > Thanks for this. We have two or three possibilities for hosting this. > None are currently set up fully, but we're getting there. > > 1) CVS: We do have a contributor section in CVS right now. > Unfortunately, that's about as far as it goes. We'll have to find a > way to manage releases of contributed code. > > 2) Wiki: A wiki was recently set up, and I've just suggested that we > have a contributor section where we can post stuff like this for you. > > 3) Website: I'd like to eventually see a 3rd party resource section > of the website that's kept up-to-date. We've already started > something like that on the draft Apache homepage. > > One way or another, we'll get this up! > > Clinton > > > On Wed, 29 Dec 2004 12:09:07 -0600, Severtson, Scott (Associate) > <[EMAIL PROTECTED]> wrote: > > All, > > Many months ago, moonpool posted a question regarding > Sybase's lack of > > support for DDL (including temporary tables) when inside a > transaction > > within iBATIS > (http://sourceforge.net/forum/message.php?msg_id=2507161). > > I solved this problem for a client transitioning to iBATIS, > and posted a > > whitepaper about the solution, pasted below. I have finally received > > written permission from the client to release the code to the public > > domain. > > > > However, they don't want to host it; any suggestions on > where to post > > the code? > > > > For the time being, anyone in desperate need can email me at > > [EMAIL PROTECTED] > > > > ---- > > Scott Severtson > > Centare Group, LLC > > > > Note: > > As described in the white paper below, we must detect if > iBATIS is using > > implicit or explicit transactions; three different > detection methods are > > included for reference, supporting the DaoManager, the > SqlMapClient, and > > one that supports both DaoManager/SqlMapClient, but only > without a JIT > > compilier (included because it requires no changes to *your code* to > > test, just changing the transaction manager; the other > solutions require > > wrapping a proxy around the DaoManager/SqlMapClient). None of these > > solutions are optimal; however, they work without changing > any iBATIS > > code, which was critical for the client. The active detector > > implementation can be changed by editing > > com.qg.open.sybase.ibatis.TransactionTypeDetector.Factory's > code to hold > > an instance of another detector, and usage details are > included in the > > javadocs. A much better solution would be for iBATIS to expose > > implicit/explicit transaction state at the DaoManager/SqlMapClient > > level. > > > > Unchained and Chained Transaction Modes > > ----------------------------------------- > > Sybase supports two transaction modes: Chained and Unchained. > > * Unchained mode is specified by JDBC's > Connection.setAutoCommit(true): > > insert/update/delete statements are automatically committed > on execute. > > Sybase extends standard AutoCommit with multi-statement transaction > > support if BEGIN TRANSACTION is explicitly called. This mode is the > > default. > > * Chained mode is specified by JDBC's > Connection.setAutoCommit(false), > > and conforms to the SQL 92 behavior of implicit transactions: a > > transaction automatically begins with the first statement, > and no work > > is committed until COMMIT TRANSACTION is explicitly called. > > > > Unchained and Chained modes are (mostly) mutually > exclusive. Unless a > > stored procedure is written with care to support both modes, it will > > generally only work in one mode. > > > > JDBC's transactional support requires > Connection.setAutoCommit(false) to > > be set, which forces Sybase into Chained mode. However, nearly all > > existing stored procedures are written exclusively for > Unchained mode. > > > > Temporary Tables and Transactions > > ----------------------------------------- > > Sybase by default does not allow CREATE/DROP TABLE statements within > > transactions, including creating tables on the temporary > database. While > > this can be enabled, Sybase strongly recommends against it: > "doing so > > can slow performance to a halt". See http://tinyurl.com/5clrf for > > further information. > > > > iBATIS expects Connection.setAutoCommit(false) mode: all > calls exist in > > an implicit, automatic transaction, and must be > committed/rolled back. > > However, two modes are supported, implicit and explicit > transactions: > > > > // ***Implicit transaction*** > > // A Transaction object is automatically retrieved prior to calling > > getUser > > User user = userDao.getUser("fflintstone"); > > // Transaction.commit() is automatically called after > calling getUser > > > > // ***Explicit transaction*** > > // A Transaction object is explicitly requested > > daoManager.startTransaction(); > > userDao.updateUser(user); > > someOtherDao.updateSomethingElse(user); > > // Transaction.commit() is explicitly called > > daoManager.commitTransaction(); > > > > Many existing procedures make use of temporary tables; > these procedures > > cannot be run inside a transaction. Therefore, we must > prevent iBATIS's > > implicit transaction support from actually > beginning/committing/rolling > > back transactions. > > > > Solutions > > ======= > > ConnectionProxy/StatementProxy/ProxyUtil > > ----------------------------------------- > > JDBC's Connection.setAutoCommit(false) behavior can be simulated in > > Unchained mode. > > 1. Call BEGIN TRANSACTION prior to executing any SQL on a > connection. > > 2. Call COMMIT/ROLLBACK TRANSACTION in place of > > Connection.commit/rollback() > > However, these manual calls would force knowledge of Sybase's > > limitations into the data access code, and would not work > with iBATIS's > > transaction support. > > The Proxy pattern from Design Patterns by Gamma et. al. (page 207) > > allows us to wrap the real Connection object with our own > > implementation, which makes the desired > BEGIN/COMMIT/ROLLBACK calls on > > our behalf. The proxy's setAutoCommit method enables/disables this > > behavior > > > > TransactionTypeDetector > > ------------------------ > > Detecting implicit and explicit transactions is necessary, > as implicit > > transactions must not actually call BEGIN TRANSACTION. The detector > > examines the stack trace to determine if startTransaction() was > > explicitly called, or if iBATIS is making the call on the > user's behalf. > > > > SybaseTransaction/SybaseTransactionConfig > > ------------------------------------------- > > iBATIS defines Transaction and TransactionConfig > interfaces, allowing > > third-party developers to build in their own transactional support. > > > > public interface Transaction { > > public Connection getConnection() throws ...; > > public void commit() throws ...; > > public void rollback() throws ...; > > public void close() throws ...; > > } > > public interface TransactionConfig { > > public Transaction newTransaction() throws ...; > > // ...Other methods... > > } > > Transaction initializes connections and manages transactions, and > > TransactionConfig builds Transaction objects. The SybaseTransaction > > implementation automatically wraps a Sybase connection in a > > ConnectionProxy, and SybaseTransactionConfig builds > SybaseTransaction > > instances. Additionally, SybaseTransactionConfig uses > > TransactionTypeDetector to specify the setAutoCommit behavior of the > > ConnectionProxy. > > > > Comments > > ======== > > This solution adequately solves the Unchained and Chained > Modes problem; > > any code that uses the ConnectionProxy can use setAutoCommit as JDBC > > specifies, while the underlying Sybase Connection remains in > > setAutoCommit(true)/Unchained mode. Stored procedures written for > > Unchained mode can be can be called from JDBC seamlessly as > part of a > > transaction. > > > > This solution does not completely solve the Temporary Tables and > > Transactions problem. Stored procedures that use temporary > tables must > > not be called in the ConnectionProxy's setAutoCommit(false) > mode, such > > as inside an explicit iBATIS transaction. This limitation > is entirely > > within the RDBMS, and as such, cannot be resolved by Java code. > > > > Finally, the implementation could have been simplified had > > ConnectionProxy's transactional support code been placed inside > > SybaseTransaction. However, the Connection.close() method > is not handled > > through the Transaction interface; cleanup requires us to listen for > > this call. Additionally, the ConnectionProxy implementation has no > > dependency on iBATIS, and can be used with other > persistence frameworks > > or straight JDBC calls. > > >
