Hello,
We are in the process of migrating an existing 5x broker integration which uses a 4.2 Sybase Intelligent adapter to IS v6.5 which uses the JDBC 6.0.3 adapters. This integration calls a stored procedure which inserts and updates various tables within the Sybase database. The procedure does contain multiple Begin and End transaction blocks, however, no changes have been made to the procedure recently and it has been working successfully when called by the Enterprise Intelligent Sybase adapter. When valid data is passed to the stored procedure, we are not seeing consistent results. Sometimes the data is populated correctly, other times the data does not appear in the correct tables. When the data is not there, no errors are generated within the Sybase database or from the JDBC adapter. Since no errors are generated, the webMethods flow continues on as if everything was successful.
We are connecting to a version 12.5.3 Sybase database, using the JConnect 6.0.5 JDBC thin drivers provided by Sybase. The webMethods JDBC adapter is version 6.0.3 with FP 1 applied along with FP1_Fix11. The adapter transaction type is set to “NO_TRANSACTION”. Within the Sybase database we have set the procedure’s transaction mode to “anymode” with no change in the behavior.
Are there any known issues or fixes available that deal with interaction with Sybase databases and specifically stored procedures?
Any assistance is greatly appreciated. At this point this issue does not directly impact our production environment, but it is part of our on-going migration project which has a production go-live date schedule for the middle of June. If we cannot find a resolution or work-around to this issue, that date will be in serious jeopardy.
thanks,
Michael
Hi Michael,
We used webMethods JDBC adapter version 6.0.3 to connect to a Sybase database version 12.5.3, using the JConnect 5.5 JDBC drivers and didn’t have such problem.
If you have the access to the stored procedure code or DBA, can you add the debugging stuff there to see what is going inside that code?
Regards,
Bhawesh.
Hi Bhawesh,
Thanks for the quick reply. In your Sybase interaction, did you call any stored procedures? If so, what transaction parameter was your JDBC adapter set to, NO_TRANSACTION or LOCAL_TRANSACTION? Did the stored procedure have any transactional logic (Begin, End, Commit, or Rollback) inside it’s coding?
We’ve tried adding debugging “inserts” in the procedure, but while we can see which insert statement it appears to stop at, the statment sql and data look good. Also, when the procedure is called from the database utility (DBArtisan) directly to try and elimated wM from the equation, there are no issues.
thanks,
Michael
Hi Michael,
In our Sybase interaction, there were no stored procedures? Just SQL calls and so cannot emulate your situation.
I have called stored procedures in past but with ORACLE not Sybase.
Let us see if somebody has exact situation like yours and certainly he/she will chime in.
Regards,
Bhawesh.
We discovered a work-around that has been successful so far, but we are still working with webMethods support to try and “official” identify the causing issue.
The stored procedure we were calling in Sybase did not return back any result set or individual information. So we initially did not define any result set parameters in the adapter service. However, when our Sybase admin ran a trace on the database side, she noticed that webMethods was sending an “abort”/cancel command to Sybase before Sybase was finished communicating back to webMethods. Talking to Sybase support revealed that any code that interacts with it’s JDBC drivers needs to be examining the result set output of a stored procedure. webMethods send us a code snipet of the JDBC adapter which indicated that if a result set was identified in the adapter service, the adapter code waited for the procedure to complete, however if no result set was defined in the adapter service, the code that interacted with the result set was bypassed.
I then added a “dummy” result set and result set column to the adapter service and we have had no issues since. I don’t like that I have to reference something that not’s defined in the stored procedure itself, but it’s better than the alternative.