2 Replies Latest reply on Aug 1, 2002 12:57 AM by David Jencks

    Sybase close problem

    seanxu Newbie

      I am using JBoss 3.0 with Tomcat 403. Here is snippet of my servlet code:
      DataSource aSource = (DataSource) aJNDIContext.lookup( "java:/Sybase
      DS") ;
      // Get JDBC Connection, create statement and get the result to retur
      n
      Connection aConnection = aSource.getConnection();
      Statement aStatement = aConnection.createStatement();
      String aSql = "SELECT contact from download";
      out.println( "<p><h5>Sql Statement: " + aSql+"</h5></p>");
      ResultSet aResult = aStatement.executeQuery( aSql );
      while( aResult.next() )
      {
      out.println("<li>contact: "+ aResult.getString(1) + "</li>\n");
      }
      aStatement.close();
      aConnection.close();


      When my servlet tries to close a connection obtained from DataSource, I get the following error message in the log.
      Can someone shed some light?
      thanks,

      15:51:04,044 ERROR [LocalTxConnectionManager$LocalConnectionEventListener] Resou
      rceException while closing connection handle!
      javax.resource.ResourceException: Could not cleanup: com.sybase.jdbc2.jdbc.SybSQ
      LException: SET CHAINED command not allowed within multi-statement transaction.

      at org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.cleanup(
      LocalManagedConnection.java:198)
      at org.jboss.resource.connectionmanager.BaseConnectionManager2.unregiste
      rAssociation(BaseConnectionManager2.java:666)
      at org.jboss.resource.connectionmanager.LocalTxConnectionManager$LocalCo
      nnectionEventListener.connectionClosed(LocalTxConnectionManager.java:381)
      at org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.closeHan
      dle(LocalManagedConnection.java:338)
      at org.jboss.resource.adapter.jdbc.local.LocalConnection.close(LocalConn
      ection.java:97)
      at testsybase.servi
      ce(testsybase.java:64)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

        • 1. Re: Sybase close problem
          seanxu Newbie

          I did a quick search on Internet and found something regarding this message. It is with a different product but it may apply to JBoss too.

          In fact, the way Expresso deals with the autocommit mode is not compliant
          with Sybase :

          when it releases a connection, DBConnectionPool first tests the
          autocommit and then put it backs to true if it is false. This doesn't
          work because with Sybase CHAINED mode, calling getAutommitMode starts a
          new transaction. So you cannot call setAutoCommit(true) without
          rollbacking/comitting before (it throws a SQLException with the message
          "SET CHAINED command not allowed within multi-statement transaction").
          This only way to work properly with Sybase is to call rollback (to be
          sure there is no more transaction) and then setAutocommit(true). This is
          what Poolman and Jakarta DBCP do and it is OK,
          there is an other problem : DBObject delete method implicitly tries to
          start a new transaction before deleting detail record. This cannot work
          if you are already inside an explicit transaction. The same SQL
          Exception is thrown ...

          Are these problem solve by Expresso new release ?

          • 2. Re: Sybase close problem
            David Jencks Master

            This is fixed in more recent versions of jboss. I'm not sure about 3.0.1 but both cvs versions for sure. I recommend 3.1, ds config is much simpler.