Hibernate | JBOSS MSSQL datasource error

I'm getting the below exception when I try to commit to the database

    java.sql.SQLException: You cannot commit with autocommit set!
        at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.jdbcComm
it(BaseWrapperManagedConnection.java:545)
        at org.jboss.resource.adapter.jdbc.WrappedConnection.commit(WrappedConne
ction.java:334)
        at net.sf.hibernate.id.TableGenerator.generate(TableGenerator.java:126)
        at net.sf.hibernate.id.TableHiLoGenerator.generate(TableHiLoGenerator.ja
va:59)

here's my JBOSS (jboss-4.0.5.GA) mssql-ds.xml :-

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
  <local-tx-datasource>
  <jndi-name>TESTDS</jndi-name>
  <connection-url>jdbc:sqlserver://localhost:1433;DatabaseName=TESTDB</connection-url>
  <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
  <user-name>sa</user-name>
  <password>password</password>
  <check-valid-connection-sql>SELECT 1 FROM sysobjects</check-valid-connection-sql>
  <metadata>
      <type-mapping>MS SQLSERVER2000</type-mapping>
  </metadata>
  </local-tx-datasource>
</datasources> 

and here's where my commit operation (with transaction) :-

  public synchronized void commitTransaction() throws TransactionException {
    Transaction tx = (Transaction) localTransaction.get();
    Session session = (Session) localSession.get();
    try {
      tx.commit();
    }
    catch (HibernateException e) {
      log.error("Error closing the persistence when commiting.", e);
      rollbackTransaction();
      throw new TransactionException(e);
    }
    finally {
      try {
        session.close();
      }
      catch (HibernateException e) {
        log.fatal("Session could not be closed !!!", e);
      }
      localSess开发者_Go百科ion.set(null);
      localTransaction.set(null);
    }
    log.info("Commiting transaction with thread : " + Thread.currentThread());
  }

UPDATE: Hibernate configuration file (hibernate.cfg.xml)

<!-- SQLSERVER configuration -->

<property name="dialect">net.sf.hibernate.dialect.SQLServerDialect</property>

<property name="connection.datasource">java:/TESTDS</property>

<property name="connection.pool_size">100</property>
<property name="statement_cache.size">200</property>

    <property name="transaction.factory_class">net.sf.hibernate.transaction.JDBCTransactionFactory</property>

<property name="show_sql">false</property>
<property name="use_outer_join">true</property>

    <!-- Hibernate mapping files configuration -->

           ..............

    <!-- Hibernate mapping files configuration -->

I tried searching all over the net but couldn't find any solution for this issue.

Thanks.

---

You cannot control transaction with the JDBC API when using a Connection from a DataSource managed by a JavaEE container.

By default, the container decides for commit (or rollback) when your business code is over, either it is written as Servlet, MessageDrivenBean onMessage or EJB methods.

If you want to check transaction status and eventually decide to mark it as rollback - as the default container behavior is to commit - you have to get the UserTransaction object from JDNI when in Servlet.

For EJB, you can get that UserTransaction object from the context the instance receive with the corresponding setter. But it requires to set transaction mode as "bean-managed" with the TransactionManagement annotation or in the deployment descriptor.

---

Have you tried setting

hibernate.connection.autocommit = false ?

Taken from the hibernate documentation.

---

Programmatic Transaction management code scares me.

I suggest you try and use Spring Declarative Transaction Management.

You should also set in your hibernate.cfg.xml:

<property name="hibernate.connection.autocommit">false</property>

---

I had the similar problem few years ago, as I remember correctly the scenario was as follows

1) some ejb or web applications grabs a connection from jboss pool
2) it sets autocommit to true does some operations with it and the puts it back to pool
3) your ejb grabs the same connection from jboss pool
4) it's previous state of autocommit is set to true, thus transactions using these connection fail

To overcome this problem you should set autocommit back to it's previous value before you close your connection, in all your custom jdbc code