Scenario : In one of our scenarios, we were calling a Sybase stored procedure from JDBC Adapter using a verion of JDBC Driver provided by Sybase. During testing we ran into wierd issues of the missing records in sybase. It was as if the stored procedure never completed all the steps it was supposed to do. When we looked at the XI logs we saw no anamolies as to what was going on.We added additional logging in the stored procedure just to trace the call execution and mysteriously when the call was made from XI, the stored procedure would sporadically exit out with out finishing all the steps. Issue : After a lot of investigation and working with both SAP and Sybase we found out that sybase was issuing intermediate done events as it finished exection of each of the steps in the stored procedure. These intermediate done events (DONE_IN_PROC) were interpreted by the JDBC adapter as completed events and hence the same JDBC statement was used for executing the next stored procedure call.
As a result JDBC Adapter would issue an execute statement even before the previous execute statement finsihed its execution because of the done events.
What is doneinproc ??
A doneinproc token is a small packet of information, returned through the network back to the client (e.g. isql) when a statement inside a stored procedure or trigger has been executed. When this statement is a 'select', the doneinproc token contains the number of rows returned (rows affected). The client will show this rowcount unless "set nocount on" has been specified. Each token is put into a separate network packet. Although the token itself is just a few bytes in size, the network packet will be a multiple of 512 bytes, depending on the connection parameters specified when a client connects to ASE. Doneinproc tokens are sent for select statements and for some (not all) other statements. With the configuration option "send doneinproc tokens" the transmission of tokens send for statements other than "select" can be suppressed. It's a myth that when you specify "set nocount on" the doneinproc token will not be sent. The primary reasons these are used is to show intermediate results in a stored procedure, specifically when rowcount needs to be determined. It provides an extra layer of granularity not usually available in most databases without additional coding in the stored procedure.
Solution
1. Ignore Intermediate events like DONE_IN_PROCS, this can be achived by setting the following property in the connection string (for sybase)
jdbc:sybase:Tds:server:port/database?IGNORE_DONE_IN_PROC=true
2. Enhanced verison of JDBC Adapter is now available.Apply the SNote 920777. Enhanced JDBC Adapter is avialable for SP13, SP14 and SP15.
Conclusion :
As specified by SAP in the SNote, not all databases generate the intermediate events. If they generate the intermediate events, the new enhanced JDBC adapter will be able to handle them. But if you dont want to update the JDBC adapter you can choose to ignore the intermediate events. Each database vendor has their own way of ignoring the intermediate events. In this weblog i have detailed on how to do it for Sybase.
