0 Replies Latest reply on Sep 30, 2003 3:51 PM by sgopinathan

    Sybase Stored procedure in a transaction


      I had created a test scenario that reflect a requirement I have in my existing project. I cannot get away from stored procedure as they have already been coded and can be considered legacy system.

      -Jboss 3.2.1_tomcat 4.x bundle/JVM 1.3.1
      -Sybase 12.x
      -Jconnect 5.5

      1. Container managed transaction.
      2. Stored procedure:SP1 and SP2 are being set in procxmode in "Any Mode"

      I have a stateless ejb that does the following
      1. Instatiates a class A which in turn calls a Stored procedure SP1.
      a. SP1 starts a trasaction and inserts a row in a table.
      b. SP1 calls another stored procedure SP2.
      c. SP2 inserts a record in another table.
      d. SP2 commits the transaction
      e. SP1 commits the transaction
      2. Instantiates a java class B. Class B throws an exception.(intentionally throwing an exception)

      Expected Result:
      The rows inserted by SP1 and SP2 be rolled back.

      Actual Result:
      They are not being rolled back at all. Instead the changes take effect.

      I connect to the database using a abstractDAOSybase object. This objects gets a connection from jboss which inturn was configured using sybase-ds.xml.
      If i use the simple <local-tx-datasource> entries i can connect fine but I have a problem that it does not rollback the changes made by SP1 and SP2. I also tried using <xa-datasource> without much luck(dint know if i was doing it right>

      Here is the sample sybase-ds.xml (i have changed the ip address and port numbers for security)

      <xa-datasource-property name="User">u</xa-datasource-property>
      <xa-datasource-property name="Password">p</xa-datasource-property>

      <!--pooling parameters-->


      <!-- Sybase jConnect URL for the database.

      What am I doing wrong? Is this the right approach? Is there anyone who had experience with using sybase stored procedures within a JBOSS transaction.