I’m using the JDBC driver 6.0.3 to connect to Sybase (12.5.0). I have the connection set up as LOCAL_TRANSACTION. I have an adapter service created to call a stored procedure. From a flow service, I will call upon the stored procedure.
If I use the WmART package to define an explicit transaction (using pub.art.transaction.startTransaction - with a hard coded transaction name, and pub.art.transaction.commitTransaction - with the same transaction name) wrapped around the call to the adapter service, everything appears to operate normally as I do not get any exception errors thrown. When I go to the Sybase database, I find out that the new record was not inserted.
If I remove the calls to make it an implicit transaction, the new record is inserted into the database (and verified in the database outside of webMethods). If I put the explicit transaction calls back in and change the parameters in the adapter service (remove a field), I will get an error message back from Sybase about the missing field.
Apparently, I don’t have problems in communication to Sybase within an explicit transaction, but if I want the data to actually be processed, it only seems to work when done as an implicit transaction.
Eventually, this transaction will be done in conjunction with a stored procedure call to an Oracle database (using an XA_TRANSACTION connection). I’ve also done an explicit transaction that calls the Oracle stored procedure first and then the Sybase stored procedure before committing the transaction. When it runs, no exceptions are thrown. The result is that the record is inserted into Oracle, but nothing is inserted into Sybase.
I’ve even placed the Sybase stored procedure call into it’s own isolated flow service and called the flow from the outer explicit transaction after the call to the Oracle stored procedure. Again, only the Oracle database showed a new record. No exception from Sybase was thrown, so no rollback occurred.
The DBA that manages the Sybase server is looking to get a license so I can try to change the Sybase connection from LOCAL_TRANSACTION to XA_TRANSACTION, but I don’t have any guarantee that I’ll have this opportunity.
Has anyone been able to perform explicit transactions to Sybase successfully?
This work is a result of migrating a process that is currently working in Enterprise 5.0 using proprietary clients to both Oracle and Sybase. We’ve been able to use the JDBC drivers to work with Oracle successfully in other integrations, but this is our first try to use JDBC drivers to work with Sybase.
Any and all help is greatly appreciated!
Paul