Help!!! I can't get BMT working with Orion and MS SQL Server

[Jag Bains]

Hi,

I have a servlet that posts a message to a stateless session bean, which inturn updates DB. The problem is that it always commits after each insert regardless to whether I have done a UserTransaction.Rollback or commit.

The Session bean is:

 
 public String getTxManagedResponse(String xmlRequest) throws
          RemoteException,
          BadDocumentException, UnableToSetDataException,
          UnknownActionException, UnknownSQLException,
          UnableToPerformActionException {
      cat.debug("Going through Container Managed method");
      String result = null;
 
      try {
          cat.debug("About to begin transaction");
          [B]ut.begin();[/B]
          //result = getResponse(xmlRequest);
 
          Context env = (Context) ic.lookup("java:comp/env");
          String val = (String) env.lookup("DSName");
          cat.debug("Value = " + val);
          // val now equals "jdbc/whatever"
          //With that, i can now
          DataSource ds = (DataSource) ic.lookup(val);
          java.sql.Connection con = ds.getConnection();
          java.sql.PreparedStatement ps = null;
 
          int i = 4;
          cat.debug("About to update table ");
          //con.setAutoCommit(false);
          while (i < 9) {
              ps = con.prepareStatement("INSERT into C_17MENU_ITEM " +
                                        "(MENU_XID, ITEM_XID, LOGICALLY_DELETED, XID_USER) values (?, ?, ?, ?)");
              ps.setInt(1, 4);
              ps.setInt(2, i);
              ps.setInt(3, 0);
              ps.setInt(4, 1);
              ps.executeUpdate();
              ps.close();
              i++;
          }
          cat.debug("About to commit");
          cat.debug("Transaction Status = " + ut.getStatus());
          [B]ut.commit();[/B]
          cat.debug("Transaction Status = " + ut.getStatus());
      } catch (Exception e) {
          try {
              cat.debug("About to rollback");
              [B]ut.rollback();[/B]
          } catch (SystemException syex) {
              throw new EJBException
                      ("Rollback failed: " + syex.getMessage());
          }
          com.jag.utils.LogError.emailError(e);
          //context.getRollbackOnly();
          throw new RemoteException(e.getMessage());
      }
      return "Blah blah";
  }

The UserTransaction and Context are create (call getIC() and getUT()) on calling the ejbCreate and ejbActivate methods.

 
protected static synchronized InitialContext getIC() throws NamingException
   {
      cat.debug(">>In getIC");
      if(ic == null)
      {
         ic = new InitialContext();
      }
      cat.debug("<<Leaving getIC " +ic.toString() );
 
      return ic;
   }
 
   // obtain UserTransaction
   protected static synchronized UserTransaction getUT() throws NamingException
   {
      cat.debug(">>In getUT");
      if(ut == null)
      {
          ut = (UserTransaction) ic.lookup("java:comp/UserTransaction");
      }
      cat.debug("<<Leaving getUT " +ut.toString() );
 
      return ut;
   }

the data-source.xml contains :

  
        <data-source
                class="com.evermind.sql.DriverManagerDataSource"
                name="MSSQLServerDS"
                location="jdbc/msSQLDS"
                pooled-location="jdbc/MSSQLServerDSPooled"
                xa-location="jdbc/xa/MSSQLServerDSXA"
                ejb-location="jdbc/MSSQLServerDS"
                connection-driver="com.microsoft.jdbc.sqlserver.SQLServerDriver"
                username="sa"
                password=""
                schema="database-schemas/ms-sql.xml"
                url="jdbc:microsoft:sqlserver://DEV-SQL2K:1433;DatabaseName=SQF;
selectMethod=cursor"
         />

My ejb-jar.xml contains the following:

  
  <enterprise-beans>
    <session>
      <display-name>Facade stateless session bean</display-name>
      <ejb-name>ejb/PetardsFacade</ejb-name>
      <home>com.jag.sqfbeans.interfaces.SQFFacadeHome</home>
      <remote>com.jag.sqfbeans.interfaces.SQFFacade</remote>
<!--
      <local-home>com.jag.businesslogic.interfaces.FacadeLocalHome</local-home>
      <local>com.jag.businesslogic.interfaces.FacadeLocal</local>
-->
      <ejb-class>com.jag.sqfbeans.ejb.SQFFacadeEJB</ejb-class>
      <session-type>Stateless</session-type>
      <resource-ref>
        <res-ref-name>jdbc/msSQLDS</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Application</res-auth>
      </resource-ref>
      <env-entry>
        <env-entry-name>DSName</env-entry-name>
        <env-entry-type>java.lang.String</env-entry-type>
        <env-entry-value>jdbc/msSQLDS</env-entry-value>
      </env-entry>
      <transaction-type>Bean</transaction-type>
    </session>
  </enterprise-beans>

Can some please show me the errors of my ways.

Thanks.

[Roger Chung-Wee]

Have you created a Tx Data Source?

[Valentin Tanase]

Hi Jag,

Your code and your settings look almost fine to me. I would also expect it to work. One might even suspect that the auto-commit feature is turned on, but that�s not the default with jta. There is however one more thing that looks wrong to me: the fact that your SLSB maintain the reference to the user transaction (ut) as an instance variable. Please notice that stateless means that the session bean does not maintain any state information for a specific client. However, stateless session beans can have non-client specific state like a database connection. Making your transaction object �sharable� I�m not pretty convinced is a good idea and you�d probably like changing the getUT method as follows (not to say that the synchronized keyword here is meaningless):

private UserTransaction getUT() throws NamingException
{
        return (UserTransaction) ic.lookup("java:comp/UserTransaction");
}

Besides I guess you declared ut as static and this makes no sense with ejbs (unless they are final). For the same reason I would advice you to rewrite the getIC() method and make ic non-static as well.
Regards.

[Jag Bains]

How do I create a TX data source?

[Jag Bains]

Hi Valentin,

Understand what you are saying and will change.

However, I not 100% sure this will make any difference. Its almost as if I am missing something obvious when setting up the datasource or connection or UserTransaction.

[Roger Chung-Wee]

Check your EJB server vendor's documentation, there will be typically a GUI or script for configuration.

[Jag Bains]

Hi Roger,

My vender is Orion. Looking at the documentation, it states I should be setting up the datasource as:

3.5 Configuring a vendor XA DataSource for EJB or CMT usage

In order to use a vendor implementation of a XA DataSource for EJB or CMT usage, the DataSource has to be wrapped by the OrionCMTDataSource class in order for Orion to add support for distributed transactions and pooling as necessary.

This setup requires two DataSource configuration entries; one defining the vendor XA DataSource and another defining the wrapping Orion DataSource (OrionCMTDataSource) for EJB or CMT usage.

Below is an example of such a setup using Inet Opta 2000 XDataSource for MS SQL Server 7.0:

<data-sources> <!-- vendor XA DataSource -->
<data-source
name="vendorXA"
location="jdbc/vendorXACoreDS"
class="com.inet.tds.XDataSource"
username="sa" password="" host="localhost" > <property name="databaseName" value="sample"/> </data-source>
<!-- Orion CMT DataSource --> <data-source name="default" location="jdbc/defaultCoreDS" class="com.evermind.sql.OrionCMTDataSource" xa-source-location="jdbc/vendorXACoreDS" schema="database-schemas/ms-sql.xml" /> </data-sources>


Listing 5: A sample XA Datasource for EJB or CMT usage DataSource entry.

The first DataSource configuration entry listed above creates a Inet Opta2000 DataSource and binds it to the JNDI location " jdbc/vendorXACoredDS". The name of the database to use is stated in a parameter name-value pair as this parameter is not part of the normal parameters defined for data-source.xml entries, but the vendor's implementation needs this value.

The second DataSource configuration entry listed above creates a OrionCMTDataSource on top of the first DataSource and binds it to the JNDI location " jdbc/defaultCoreDS". As this DataSource needs to be used on top of a existing XA DataSource, the JNDI location value of the xa-source-location attribute is given a value of " jdbc/vendorXACoredDS", which will point it to the first DataSource (see above).

The second DataSource configuration entry listed above would become the default DataSource for all applications deployed after this DataSource descriptor has been deployed as its the first DataSource listed to use the OrionCMTDataSource.

[Jag Bains]

how do i find the correct class to use for the XA data source?
I'm using MS sql server 2000.

[Valentin Tanase]

Hi Jag,

I believe you�re right and I�m convinced the changes I recommended will not solve your problem. I remember somebody else on this forum having a similar problem and s/he solved it by setting the DataSource. He was using weblogic though and he needed to enable the TX-DataSource in order to honor the global transactions. Unfortunately I don�t know much about the Orion server and I cannot help you there. Check the documentation though.
Regards.

[Roger Chung-Wee]

Perhaps the moderator can move this to the Orion forum. (I can't help you, I'm a WebLogic Server man ...)