Sybase Stored procedure in a transaction

[sgopinathan]

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