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
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)
The rows inserted by SP1 and SP2 be rolled back.
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)
<!-- 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.