5 Replies Latest reply on Jun 18, 2003 5:45 PM by cromantm

    Autocommit SQLException

    cromantm

      I get the following exception when performing a DB save:
      -----------------------------------------

      11:07:22,140 ERROR [Task Persistence] DBTaskDAO.insertTaskSessionEvent - Exception getting task session:java.sql.SQLException: You cannot set autocommit during a managed transaction!
      11:07:22,156 DEBUG java.sql.SQLException java.sql.SQLException: You cannot set autocommit during a managed transaction!
      at org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.setJdbcAutoCommitLocalManagedConnection.java:456)
      at org.jboss.resource.adapter.jdbc.local.LocalConnection.setAutoCommit(LocalConnection.java:426)
      --------------------------------
      JBoss Version : 3.0.6


      Using Local Connection Management for the JDBC JBoss RAR.

      Any ideas what may be wrong?

      Should I be using the JDBC RAR with XA connection management support instead?

      For XA the example provided config file mssql-xa-service.xml defines a property "XADataSourceProperties" and has ServerName as one of it's props. Not clear if server name should be just the name of the machine (with port?) or the jdbc string to the server containing the server name and port e.g. "jdbc:microsoft:sqlserver://foo:1433;"

        • 1. Re: Autocommit SQLException
          lee_yuki2000

          Are you using EJB? If yes, are you using CMP or BMP? Where did u set your auto commit?

          • 2. Re: Autocommit SQLException
            cromantm

            Yes, I'm using EJB with CMP.
            The autocommit is set within the Bean code.

            A class within the bean package makes the call to
            org.jboss.resource.adapter.jdbc.local.LocalConnection.setAutoCommit(false)

            The method is as follows:
            Note: The Connection is object from a DataSource object.

            static void insertObject(Connection conn, String objectId, Object obj) throws Exception {
            boolean autocommit = conn.getAutoCommit();
            Log.debug("Saving Blob for objectID:"+objectId);
            conn.setAutoCommit(false); // this is where it breaks
            try {
            insertBlob(conn,objectId,obj);
            conn.commit();
            } catch(Exception e) {
            conn.rollback();
            throw(e);
            } finally {
            conn.setAutoCommit(autocommit);
            }
            }

            • 3. Re: Autocommit SQLException
              cromantm

              A bit more info....

              ejb-jar.xml
              -----------------------------------
              <?xml version="1.0" encoding="UTF-8"?>
              <!DOCTYPE ejb-jar PUBLIC "-//Sun Microsystems, Inc.//DTD Enterprise JavaBeans 2.0//EN" "http://java.sun.com/dtd/ejb-jar_2_0.dtd">

              <ejb-jar id="ejb-jar_ID">
              <enterprise-beans>

              <ejb-name>TaskPersistenceEJB</ejb-name>
              com.ejb.TaskPersistenceHome
              com.ejb.TaskPersistence
              <ejb-class>com.ejb.TaskPersistenceBean</ejb-class>
              <session-type>Stateless</session-type>
              <transaction-type>Container</transaction-type>
              <env-entry id="EnvEntry_1">
              Flag to use db persistence
              <env-entry-name>USE_DB_PERSISTENCE</env-entry-name>
              <env-entry-type>java.lang.String</env-entry-type>
              <env-entry-value>0</env-entry-value>
              </env-entry>

              </enterprise-beans>
              <assembly-descriptor>
              <container-transaction>
              Transaction attributes for 'TaskPersistenceEJB' methods

              <ejb-name>TaskPersistenceEJB</ejb-name>
              <method-name>*</method-name>

              <trans-attribute>Required</trans-attribute>
              </container-transaction>
              </assembly-descriptor>
              </ejb-jar>
              ------------------------------------------

              jboss.xml
              ------------------------------------
              <?xml version="1.0" encoding="UTF-8"?>

              <enterprise-beans>

              <ejb-name>TaskPersistenceEJB</ejb-name>
              <jndi-name>task/persist</jndi-name>

              </enterprise-beans>

              • 4. Re: Autocommit SQLException
                lee_yuki2000

                If you are manually setting the auto commit to false, you should use BMP instead of CMP. If you want to use CMP, the auto commit is automatically set to true. This mean when you are using CMP, you don't need to execute commit statement.

                I have the same error like yours and when i modify to BMP, everything works fine. In your ejb-jar.xml, change to bean.

                <transaction-type>Bean</transaction-type>

                • 5. Re: Autocommit SQLException
                  cromantm


                  Found another solution as well. Decided to use the NoTxConnectionManager for the DataSource configured instead of LocalTxConnectionManager. This allowed me to have control over the transaction.

                  I will try your approach as well. It may be best to change to BMP.
                  Thanks