3 Replies Latest reply on Jul 16, 2004 9:45 AM by marcma

    new-connection-sql / check-valid-connection-sql transaction

    marcma Newbie

      Hi All.

      We use a very simple query for new-connection-sql and check-valid-connection-sql. (select 1 or select @@servername).

      We get the following exception all the time:


      com.sybase.jdbc2.jdbc.SybSQLException: SET CHAINED command not allowed within multi-statement transaction.

      at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2884)
      at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:2206)
      at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
      at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:220)
      at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:203)
      at com.sybase.jdbc2.jdbc.SybStatement.updateLoop(SybStatement.java:1702)
      at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:1685)
      at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java:115)
      at com.sybase.jdbc2.tds.Tds.setOption(Tds.java:1206)
      at com.sybase.jdbc2.jdbc.SybConnection.setAutoCommit(SybConnection.java:986)
      at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkTransaction(BaseWrapperManagedConnection.java:425)
      at org.jboss.resource.adapter.jdbc.WrappedConnection.checkTransaction(WrappedConnection.java:757)
      at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:205)


      If we disable new-connection-sql and check-valid-connection-sql
      everything does fine.

      This was no issue with jboss-3.2.3.

      We use:
      JBoss-3.2.5
      Sybase ASE 12.5.2 / JConn2 5.5
      JDK-1.4.2_05

      *-ds.xml looks like this:

      <datasources>
       <local-tx-datasource>
       <jndi-name>jdbc/XYZ</jndi-name>
       <connection-url>jdbc:sybase:Tds:XYZ:5555/xyz</connection-url>
       <driver-class>com.sybase.jdbc2.jdbc.SybDataSource</driver-class>
       <user-name>xyz</user-name>
       <password>xyz</password>
      
       <connection-property name="APPLICATIONNAME">TX_bla</connection-property>
       <connection-property name="HOSTPROC">TX_bla</connection-property>
       <connection-property name="PACKETSIZE">512</connection-property>
       <connection-property name="DYNAMIC_PREPARE">True</connection-property>
      
      
       <min-pool-size>5</min-pool-size>
       <max-pool-size>15</max-pool-size>
       <blocking-timeout-millis>5000</blocking-timeout-millis>>
       <idle-timeout-minutes>3</idle-timeout-minutes>
       <prepared-statement-cache-size>100</prepared-statement-cache-size>
      
       <track-statements>False</track-statements>
      
       <new-connection-sql>select 1</new-connection-sql>
       <check-valid-connection-sql>select 1</check-valid-connection-sql>
       <exception-sorter-class-name>SybaseExceptionSorter</exception-sorter-class-name>
       </local-tx-datasource>
      
      
       <no-tx-datasource>
       <jndi-name>jdbc/XYZ_NOTX</jndi-name>
       <connection-url>jdbc:sybase:Tds:XYZ:5555/xyz</connection-url>
       <driver-class>com.sybase.jdbc2.jdbc.SybDataSource</driver-class>
       <user-name>xyz</user-name>
       <password>xyz</password>
      
       <connection-property name="APPLICATIONNAME">NX_bla</connection-property>
       <connection-property name="HOSTPROC">NX_bla</connection-property>
       <connection-property name="PACKETSIZE">512</connection-property>
       <connection-property name="DYNAMIC_PREPARE">True</connection-property>
      
       <min-pool-size>5</min-pool-size>
       <max-pool-size>15</max-pool-size>
       <blocking-timeout-millis>5000</blocking-timeout-millis>>
       <idle-timeout-minutes>3</idle-timeout-minutes>
       <prepared-statement-cache-size>100</prepared-statement-cache-size>
      
       <track-statements>False</track-statements>
      
       <new-connection-sql>select 1</new-connection-sql>
       <check-valid-connection-sql>select 1</check-valid-connection-sql>
       <exception-sorter-class-name>SybaseExceptionSorter</exception-sorter-class-name>
       </no-tx-datasource>
      </datasources>
      


      Our ExceptionSorter has no effect on the things said above. Neither positive nor negative. We can turn it on or off and the exception occurs either way. If you are interested in the sorter you will get it.

      What could this be? Any suggestions, pointers or solutions?

      For my 2 cents it may have something to do with the pool using the setAutoCommit inappropriate in terms of sybase.

      Cheers



        • 1. Re: new-connection-sql / check-valid-connection-sql transact
          Adrian Brock Master

          This is a known bug with Sybase.

          Use search to find my suggested workaround.
          Unfortunately, the previous poster gave no feedback.

          • 2. Re: new-connection-sql / check-valid-connection-sql transact
            marcma Newbie

            Hi.

            Thanks. But I cannot find any workaround. I searched
            via Search, via Google, via Google-Groups and Sourceforge.

            Where do you want me to search? Even better could you please
            give me a concrete pointer?

            All I have read about this from behalf of sybase is that the jboss pool
            or new-connection and valid-connection stuff should not call setAutocommit(false) twice on the same connection as long as there live an open transaction.

            Cheers
            Marc

            • 3. Re: new-connection-sql / check-valid-connection-sql transact
              marcma Newbie

              Ok.

              Still have not found your workaround, but figured out my own.

              The bug regarding getAutocommit and setAutocmmit in sybase jdbc jconn2 is solved:

              jConnect will no longer begin a transaction in chained mode when
              Connection.getAutoCommit() or Connection.getIsolationLevel() is called.
              This allows clients to use code such as the following:
              
              if (! Connection.getAutoCommit())
              {
               Connection.setAutoCommit(true);
              }
              

              So this cannot be the problem.

              With

              BEGIN TRAN select 1 COMMIT TRAN

              istead of

              select 1

              everything works fine. So in addition to the fact that the exception mentioned in the first posting is only popping up with jboss-3.2.5 and not with jboss-3.2.3 I think there must be a change in jboss. (Maybe a bug)

              Just my 2 cents