1 Reply Latest reply on Aug 20, 2002 12:23 PM by David Jencks

    non-transactional db connection

    John Curley Newbie


      Hi, All:

      Can I set up a non-transactional db connection (maybe via the jboss.jcml file)?

      Here is my dilemma:

      Hi, All:

      I am trying to invoke a stored procedure to obtain the last sequential
      number to auto generate a primary key. Here's the code

      ////////////////////////////

      if(connection != null) {
      try {
      String sqlLastSeqNumber =
      "exec get_lastsequentialnumber Notifications, 1";
      connection.setAutoCommit(true);
      CallableStatement statement =
      connection.prepareCall(sqlLastSeqNumber);
      ResultSet rs = statement.executeQuery();

      ////////////////////////////

      Now when I run this, I get the following Sybase error:

      2002-08-19 08:27:18,488 ERROR
      com.nielsenmedia.ntarget.services.notification.Not
      ificationServiceBean - [NONE]Database error: java.sql.SQLException:
      Cannot set A
      utoCommit for a transactional connection: See JDBC 2.0 Optional
      Package Specific
      ation section 7.1 (p25)

      So, I guess it doesn't like me trying to set the auto commit feature.

      So, I try to change my code (comment out setAutoCommit()):

      ///////////////////////////

      ////////////////////////////

      if(connection != null) {
      try {
      String sqlLastSeqNumber =
      "exec get_lastsequentialnumber Notifications, 1";
      //connection.setAutoCommit(true);
      CallableStatement statement =
      connection.prepareCall(sqlLastSeqNumber);
      ResultSet rs = statement.executeQuery();

      ////////////////////////////

      then I get the following error:

      2002-08-19 08:34:27,894 ERROR
      com.nielsenmedia.ntarget.services.notification.Not
      ificationServiceBean - [NONE]Database error:
      com.sybase.jdbc2.jdbc.SybSQLExcepti
      on: Stored procedure 'get_lastsequentialnumber' may be run only in
      unchained tra
      nsaction mode. The 'SET CHAINED OFF' command will cause the current
      session to u
      se unchained transaction mode.

      So, I guess the errors are trying to tell me that I can't run the
      Sybase stored procedure in a transactional connection. But I can't
      turn on the
      auto-commit.

      Any recommendations?

      Thanks,
      John Curley

      john_curley@tvratings.com

        • 1. Re: non-transactional db connection
          David Jencks Master

          Please always state explicitly the exact jboss version you are using. I'm guessing 2.4.4. I believe you can set up a JDBCDataSourceLoader that will be non-transactional. Sorry, I don't have an example.

          Can't you make your procedure run within a transaction? Relying on transactions to get unique values usually doesn't work, usually using a sequence or generator or special table with auto-increment key works better and can be used in a transaction.