Sybase Stored procedure in a transaction
sgopinathan Sep 30, 2003 3:51 PMI 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