Explicit commit on connection failing

[vijay_kirti]

Hi,

I am using Spring along with Hibernate. Txn boundry demaractions are done declartively, but for this specific case I am setting the txn propogation PROPOGATION_NEVER as I have to commit connection due to performance constraint. JNDI datasource is setup with no-txn-datasource.

I am getting java.sql.SQLException: You cannot commit with autocommit set! when I do commit on connection.

Can anybody point me what I am doing wrong.

Thanks in Advance.
Vijay

[weston.price]

Using the no-tx-datasource means just that, no transactions at the driver level.

To do what you want you need to get the connection, set autocommit to false, do your JDBC stuff, and then commit.

Note, your Spring/Hib transaction configuration are JTA transactions, these are different than JDBC level transactions.

[weston.price]

Note, I should have mentioned that you don't have to do what I described above. You can very well not modify the autocommit of the underlying connection. However, what this means is that every statement you run will be a seperate JDBC transaction; this is what autocommit means.

[vijay_kirti]

Thanks for replying,

Does this mean if I have to do batch commit then I have to grab connection and set its auto commit to false. Do I need to set the connection autocommit back to yes once finished?

For WebSphere and WebLogic, connection obtained from DataSource seems to have autocommit false. Is there any way to get the same behaviour for JBoss without code changes?

Thanks,
Vijay

[weston.price]

Does this mean if I have to do batch commit then I have to grab connection and set its auto commit to false. Do I need to set the connection autocommit back to yes once finished?

No, you will want to take a look at this:

http://wiki.jboss.org/wiki/Wiki.jsp?page=JBossJCALazyAutoCommit

For WebSphere and WebLogic, connection obtained from DataSource seems to have autocommit false. Is there any way to get the same behaviour for JBoss without code changes?

WebSpere and Weblogic, AFAIK, do not have the notion of a NoTx DataSource. Local transactions (ie AutoCommit == false) is on all the time. You can use a LocalTx datasource but you will be required to remove the setAutoCommit(), commit(), rollback() logic from your code as this role would be given over to JBoss.