CODEWITHSUNDEEP
sql-serverhibernatejboss
Switch
Switch

Reputation: 15453

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);
      }
      localSession.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.

Upvotes: 1

Views: 2340

Answers (4)

Yves Martin
Yves Martin

Reputation: 10361

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.

Upvotes: 1

Troydm
Troydm

Reputation: 2670

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

Upvotes: 0

Eran Harel
Eran Harel

Reputation: 2365

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>

Upvotes: 0

pengtuck
pengtuck

Reputation: 11

Have you tried setting

hibernate.connection.autocommit = false ?

Taken from the hibernate documentation.

Upvotes: 0

Related Questions