2 Replies Latest reply on Dec 4, 2002 7:02 AM by Mauro Ramon

    Problem with UserTransaction.

    Mauro Ramon Newbie

      Hello to all.
      I am having a problem with the use of UserTransaction to delimit one transaccion where several updates are made.
      My environment is jBoss 3.0.0+Jetty and SQL2000 with MS JDBC driver.
      The problem that I have is that when commit() the transaction the tables updated are blocked for reading,
      and when I stop JBOSS, this does rollback of all the updates made by transaction.
      It seems that commit() is not executed in fact.
      Somebody can say to me if I am making something in erronea form?
      Mi code is:
      public void create(Object object) throws SaveException {
      JdbcPersistenceManager pm = (JdbcPersistenceManager)this.getPersistenceManager();
      Connection conn = pm.getConnectionProvider().getConnection();
      UserTransaction tx = null;
      try {
      tx = (UserTransaction)ObjectLocator.getInstance().lookup("java:comp/UserTransaction");
      tx.begin();
      XmlHandler handler = new XmlHandler((String)object);
      String whenSpecID = "";
      String sql = this.getSQLforWhenSpec(handler);
      Statement stInsert = conn.createStatement();
      stInsert.executeUpdate(sql);
      Statement stQuery = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
      ResultSet rs = stQuery.executeQuery("SELECT @@identity whenSpecID");
      if (rs.next()) {
      whenSpecID = rs.getString("whenSpecID");
      }
      rs.close();
      sql = this.getSQLforTriggers(handler, whenSpecID);
      stInsert.executeUpdate(sql);
      tx.commit();
      } catch (Exception e) {
      try {
      if (tx != null) {
      tx.rollback();
      }
      } catch (Exception se) {
      throw new SaveException("TriggerSaveStrategy:create()",e);
      }
      throw new SaveException("TriggerSaveStrategy:create()",e);
      } finally {
      try {
      if (conn != null) {
      conn.close();
      }
      } catch (Exception e) {
      e.printStackTrace();
      }
      }
      }

      PS: if i use JDBC transaction (ie. conn.setAutocommit(false);....conn.commit()...conn.setAutocommit(true))
      everything run fine.

        • 1. Re: Problem with UserTransaction.
          Vicky Kak Master

          Hi,
          Please refer to this code as this has been working



          DB TEST FOR JBOSS


          <%@ page import="javax.naming.*,javax.sql.*,java.sql.*,project.session.login.*,javax.transaction.*" %>
          <%
          Context ocontext=new InitialContext();

          UserTransaction trans=(UserTransaction)ocontext.lookup("UserTransaction");
          //trans.setTransactionTimeout(20);
          trans.begin();

          DataSource ods=(DataSource)ocontext.lookup("java:/OracleDSI");
          Connection ocon=ods.getConnection();
          System.out.println("First");
          PreparedStatement ostmt=ocon.prepareStatement("update gangsterejb set name=? where id = ? ");
          ostmt.setString(1,request.getParameter("name"));
          ostmt.setInt(2,3);
          ostmt.executeUpdate();
          System.out.println("Second");
          ocon.close();

          /*
          DataSource ods=(DataSource)ocontext.lookup("java:/XAOracleDS");
          System.out.println("After getting DataSource");
          Connection ocon=ods.getConnection();
          System.out.println("After getting Connection"+ocon.getAutoCommit());
          PreparedStatement ostmt=ocon.prepareStatement("update gangsterejb set name=? where id = ? ");
          ostmt.setString(1,"VK1");
          ostmt.setInt(2,3);
          //ostmt.executeUpdate();
          System.out.println("After update");
          System.out.println("Before Starting Commit");
          */
          if(request.getParameter("rollback")==null)
          trans.commit();
          else
          trans.rollback();
          System.out.println("After Committing");
          out.println(trans+"");

          %>
          <%
          ods=(DataSource)ocontext.lookup("java:/DB2DS");
          Connection ocon1=ods.getConnection();
          //out.println("DB2 "+ocon);
          %>

          <%--
          ResultSet ors=ostmt.executeQuery("select * from tab");
          while(ors.next())
          {
          %>
          <%= ors.getString(1) %>
          <%
          }
          --%>

          <%-- Try this Sundar--%>
          <%--
          LoginHome ohome=(LoginHome)ocontext.lookup("Login");
          Login oremote=ohome.create();
          out.println(oremote.login("Vicky","Comsoft"));
          //RolesHome orhome=(RolesHome)ocontext.lookup("Roles");
          //Roles orremote=orhome.create(new Integer(5));
          //out.println(oremote);
          --%>



          You are getting the connectionn from persistence manager,you dont have to do.Refer to the specification of JTS you will notice there are ResourceManager,TransactionManager and Applicaiton Component.So you should try to get the connection from the TransactionManager which I am doing not from the PersistenceManager.
          Regards
          Vicky

          • 2. Re: Problem with UserTransaction.
            Mauro Ramon Newbie

            Hi Vicky, thanks for your reply.
            PersistenceManager is a plain java object from a persistence framework developed for us.
            The problem was in the order of the sentences.
            Reading your code, the get the Connection are after the begin() of UserTransaction. I have changed this in my code, and now all work fine.
            Thanks a lot.