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

    Sybase Stored procedure in a transaction

    sgopinathan Newbie

      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.

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


      Assumptions:
      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.

      Note:
      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>
      <jndi-name>jdbc/GenericXADS</jndi-name>
      <connection-url>jdbc:sybase:Tds:host:port/db?CHARSET=iso_1</connection-url>
      <xa-datasource-class>com.sybase.jdbc2.jdbc.SybXADataSource</xa-datasource-class>
      <xa-datasource-property name="User">u</xa-datasource-property>
      <xa-datasource-property name="Password">p</xa-datasource-property>

      <!--pooling parameters-->
      <min-pool-size>5</min-pool-size>
      <max-pool-size>20</max-pool-size>
      <blocking-timeout-millis>5000</blocking-timeout-millis>
      <idle-timeout-minutes>15</idle-timeout-minutes>


      </xa-datasource>



      <local-tx-datasource>
      <jndi-name>jdbc/CommonDB</jndi-name>
      <!-- Sybase jConnect URL for the database.
      -->
      <connection-url>jdbc:sybase:Tds:host:port/db?CHARSET=iso_1</connection-url>
      <driver-class>com.sybase.jdbc2.jdbc.SybDataSource</driver-class>
      <user-name>u</user-name>
      p
      </local-tx-datasource>





      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.

      Thanks
      SG