I've already put this up on the wiki (feel free to attach code to the page).
Clinton On Sat, 1 Jan 2005 12:38:27 -0700, Brandon Goodin <[EMAIL PROTECTED]> wrote: > actually, you could attache it to a support request in JIRA and ask > that it be added to the "contibutor" section of the ibatis source. > > On Sat, 1 Jan 2005 12:36:25 -0700, Brandon Goodin > <[EMAIL PROTECTED]> wrote: > > I'll start a thread on this on the developers list and see what we can > > get setup. > > > > Brandon > > > > > > On Tue, 28 Dec 2004 14:58:17 -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 open source the code. > > > > > > So, where should I post the code? Anyone want to host it? > > > > > > For the time being, anyone in desperate need can email me at > > > [EMAIL PROTECTED] > > > > > > --Scott Severtson > > > > > > 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 Dao/SqlMaps, 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 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. > > > > > >
