9 Replies Latest reply on Feb 24, 2003 6:32 PM by Tom

    Sybase: SET CHAINED command not allowed within multi-stateme

    Paul Newbie

      Hi,

      I am attempting to migrate our application from Jboss 2.4.4 to 3.0.x and have been receiving the following exception while using 3.0.4 or 3.0.5RC1 with Sybase 12.0:

      com.sybase.jdbc2.jdbc.SybSQLException: SET CHAINED command not allowed within multi-statement transaction.
      at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2636)
      at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1996)
      at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
      at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:204)
      at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:187)
      at com.sybase.jdbc2.jdbc.SybStatement.updateLoop(SybStatement.java:1615)
      at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:1598)
      at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java:89)
      at com.sybase.jdbc2.tds.Tds.setOption(Tds.java:1111)
      at com.sybase.jdbc2.jdbc.SybConnection.setAutoCommit(SybConnection.java:634)
      at org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.checkTransaction(LocalManagedConnection.java:423)
      at org.jboss.resource.adapter.jdbc.local.LocalConnection.checkTransaction(LocalConnection.java:756)
      at org.jboss.resource.adapter.jdbc.local.LocalStatement.checkTransaction(LocalStatement.java:771)
      at org.jboss.resource.adapter.jdbc.local.LocalPreparedStatement.executeQuery(LocalPreparedStatement.java:286)


      I have noticed that some other people have experienced the same problem with earlier versions of 3.0.x and that it may have been fixed in a later version. Could someone please let me know which version has this fix, or whether it is particular to our application.

      Access to the database is via session beans with container managed transactions. The exception has never occurred with 2.4.4.

      Thanks in advance for any help.

      Cheers,

      Paul.

        • 1. Re: Sybase: SET CHAINED command not allowed within multi-sta
          Paul Newbie

          Hi,

          Does anyone have an idea about this problem? Is it a problem with our code or Jboss? I will attempt to develop a simple bean to reproduce the problem.

          Cheers,

          Paul.

          • 2. Re: Sybase: SET CHAINED command not allowed within multi-sta
            Paul Newbie

            Hi,

            I have now generated a test case to reproduce this exception. The problem is encountered when one of the EJBs has a trans-attribute of NotSupported while another has Required or RequiresNew.

            I have generated two EJBs (both using the same code base) that do a simple query on a table with 1 row. One EJB is defined to have transaction Required while the other is NotSupported. After the first call to the bean with the NotSupported setting, the exception in question is produced.

            Example output from server:

            18:40:14,515 INFO [STDOUT] ejbCreate() start...
            18:40:14,515 INFO [STDOUT] ejbCreate() returned.
            18:40:14,515 INFO [STDOUT] executeTest() start...
            18:40:14,915 INFO [STDOUT] Output: 1
            18:40:14,915 INFO [STDOUT] executeTest() returned.
            18:40:14,955 INFO [STDOUT] ejbCreate() start...
            18:40:14,955 INFO [STDOUT] ejbCreate() returned.
            18:40:14,955 INFO [STDOUT] executeTest() start...
            18:40:14,965 ERROR [STDERR] com.sybase.jdbc2.jdbc.SybSQLException: SET CHAINED command not allowed within multi-statement transacti
            18:40:14,965 ERROR [STDERR] at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2636)
            18:40:14,965 ERROR [STDERR] at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1996)
            18:40:14,965 ERROR [STDERR] at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
            18:40:14,965 ERROR [STDERR] at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:204)
            18:40:14,965 ERROR [STDERR] at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:187)
            18:40:14,965 ERROR [STDERR] at com.sybase.jdbc2.jdbc.SybStatement.updateLoop(SybStatement.java:1615)
            18:40:14,965 ERROR [STDERR] at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:1598)
            18:40:14,965 ERROR [STDERR] at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java:89)
            18:40:14,965 ERROR [STDERR] at com.sybase.jdbc2.tds.Tds.setOption(Tds.java:1111)
            18:40:14,965 ERROR [STDERR] at com.sybase.jdbc2.jdbc.SybConnection.setAutoCommit(SybConnection.java:634)
            18:40:14,965 ERROR [STDERR] at org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.checkTransaction(LocalManagedConnec
            18:40:14,965 ERROR [STDERR] at org.jboss.resource.adapter.jdbc.local.LocalConnection.checkTransaction(LocalConnection.java:756)
            18:40:14,965 ERROR [STDERR] at org.jboss.resource.adapter.jdbc.local.LocalStatement.checkTransaction(LocalStatement.java:771)
            18:40:14,965 ERROR [STDERR] at org.jboss.resource.adapter.jdbc.local.LocalStatement.executeQuery(LocalStatement.java:208)
            18:40:14,965 ERROR [STDERR] at bean.SybaseDSTestBean.executeTest(SybaseDSTestBean.java:56)
            18:40:14,965 ERROR [STDERR] at java.lang.reflect.Method.invoke(Native Method)
            18:40:14,965 ERROR [STDERR] at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.ja
            18:40:14,965 ERROR [STDERR] at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionIntercep
            18:40:14,965 ERROR [STDERR] at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterce
            18:40:14,965 ERROR [STDERR] at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:107)
            18:40:14,965 ERROR [STDERR] at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:151)
            18:40:14,965 ERROR [STDERR] at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:60)
            18:40:14,965 ERROR [STDERR] at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:130)
            18:40:14,965 ERROR [STDERR] at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:204)
            18:40:14,965 ERROR [STDERR] at org.jboss.ejb.StatelessSessionContainer.invoke(StatelessSessionContainer.java:313)
            18:40:14,975 ERROR [STDERR] at org.jboss.ejb.Container.invoke(Container.java:712)
            18:40:14,975 ERROR [STDERR] at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
            18:40:14,975 ERROR [STDERR] at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:382)
            18:40:14,975 ERROR [STDERR] at java.lang.reflect.Method.invoke(Native Method)
            18:40:14,975 ERROR [STDERR] at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:241)
            18:40:14,975 ERROR [STDERR] at sun.rmi.transport.Transport$1.run(Transport.java:152)
            18:40:14,975 ERROR [STDERR] at java.security.AccessController.doPrivileged(Native Method)
            18:40:14,975 ERROR [STDERR] at sun.rmi.transport.Transport.serviceCall(Transport.java:148)
            18:40:14,975 ERROR [STDERR] at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:465)
            18:40:14,975 ERROR [STDERR] at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:706)
            18:40:14,975 ERROR [STDERR] at java.lang.Thread.run(Thread.java:484)

            I have noted that when I run the client a second time, the error occurs on the second repitition and on the third and all subsequent attempts there are no failures. When Jboss is restarted then the problem is re-introduced.

            I have attached the code that I have used to reproduce this problem with the hope that someone else may have encountered this problem and can shed some light.

            Cheers,

            Paul.

            • 3. Re: Sybase: SET CHAINED command not allowed within multi-sta
              Paul Newbie

              Sorry, the files did not seem to attach. I am trying again.

              Cheers,
              Paul.

              • 4. Re: Sybase: SET CHAINED command not allowed within multi-sta
                nick mills Newbie

                This problem is with the jConnect driver thinking it's in a
                transaction when it really isn't - the exception gets
                thrown when the jboss LocalManagedConnection tries to
                setAutoCommit.
                I patched my LocalManagedConnection class to try
                setAutoCommit and catch the SQLException then call commit()
                to finish the (unneccessary) transaction - then call
                setAutocommit again. This works well for me.
                see
                http://www.objectweb.org/wws/arc/jonas/1999-09/msg00123.html
                for background on the same problem in Jonas.

                According to the document below this was fixed in JBoss_3_0_1RC1
                http://216.239.33.100/search?q=cache:Ry9d6r4MmBsC:www.stanford.edu/~nataliew/AllDocsSubscription/ChangeNotes/301_300_changes.html+localmanagedconnection+jar&hl=en&ie=UTF-8
                (search for the string "con.getAutoCommit()")

                However I have experienced the problem in 3.0.4,3.0.5, and 3.0.6 :-(

                Regards,
                nick

                • 5. Re: Sybase: SET CHAINED command not allowed within multi-sta
                  Jay Newbie

                  I ran into this problem today.

                  Our stateless EJB was deployed in Weblogic and running well. I was just trying to see if I can deploy it in JBoss.

                  Weblogic 6.1 sp3 has the same problem. It has to do with setAutoCommit(). Later they fix it with a patch. Apparently JBoss 3.0.4 does not fix the problem.

                  Interestingly, I can call the same method when calling the second time. However, it locks two rows in two tables and does not seem to release the locks.

                  • 6. Re: Sybase: SET CHAINED command not allowed within multi-sta
                    Tom Newbie

                    Hi. We are running into this issue as well upgrading from 2.4.7 to 3.0.x. I have tried JBoss 3.0.3 and JBoss 3.0.6. Same problem. Has this been reported as a bug? Is this a JBoss issue or a Sybase issue? Is anyone else out there running JBoss against ASE 12.5?

                    Thanks in advance for any help you can provide...

                    • 7. Re: Sybase: SET CHAINED command not allowed within multi-sta
                      David Jencks Master

                      I don't see why this is happening, but following the advice I wrote a modified version. I need some testing before I can commit it. I've (hopefully) attached the modified BaseWrapperManagedConnection.jpp. It should work with cvs versions of jb 3, 3.2, and 4.

                      • 8. Re: Sybase: SET CHAINED command not allowed within multi-sta
                        Tom Newbie

                        Hi. Thanks for the reply. I tried to download your attachment... but I seem to be having some problems with it. (The other files in this thread download ok, though.)
                        I did some searching on the Sybase newsgroup for JConnect and found this (Seems like someone using Borland was having the same issue):

                        This is taken from a response from the Sybase JConnect newsgroup:

                        [
                        setAutoCommit (true or false) issues "SET CHAINED ON" (or "OFF").

                        If you initally connect to server and issue setAutoCommit(false) - SET
                        CHAINED ON is issued.
                        This means that ASE will now implicitly BEGIN TRAN on any SELECT or Mod
                        statement sent to ASE.
                        (I assume you are using ASE).

                        If you do a bunch of SQL in the app, and do not Commit or Rollback, OR
                        if there is something
                        else that issues some SELECT Prior to the setAutoCommit(I assume to
                        TRUE), then that "SET CHAINED OFF"
                        occurs in the boundaries of a transaction. Sybase ASE will not allow
                        you to set the CHAINED
                        option at this point until that Transaction is closed.

                        I would suggest that :

                        1. In your code you make sure you have COMMITted or ROLLBACK any open
                        transaciton prior
                        to the setAutoCommit() command , before it goes back to the pool - if
                        Borland controls this
                        part of the code then they really need to contact Sybase (us) so that we
                        can work with them and
                        pinpoint the problem and come up with some resolution.

                        2. Some folks feel that something like "SELECT @@TRANCHAINED" should
                        not start of transaction
                        if CHAINED mode is ON. This is something to investigate, but again if a
                        customer reports this
                        to Sybase it has some chance of getting looked at - it would require a
                        change on ASE, and this
                        type of change might be good - or it might be bad if other developers
                        are aware of this behavior and have
                        already worked around it.
                        ]

                        Hope this will help some...

                        - Tom

                        • 9. Re: Sybase: SET CHAINED command not allowed within multi-sta
                          Tom Newbie

                          I found the following tidbit from the Sybase JConnect Release Bulletin for JConnect v5.5:

                          Issue number: 193381
                          Behavior with chained mode,Connection.commit(), DatbaseMetadata.getcolumns
                          When making a connection, jConnect executes sp_mda before the connection object is returned. This
                          enables jConnect to implement methods on DatabaseMetadata and sets chained transaction mode to
                          “off,” per the JDBC 1.x specification. This changes the default behavior of the database, where the
                          default configuration usually sets chained transaction mode to “on.”
                          Workaround: Use Connection.setAutoCommit(false); which issues the command to reset chained
                          transaction mode to “on.”

                          Does this help?

                          - Tom