JDBC Adapter executing stored procedure not getting result set values

, ,
1

I am using JDBC adapter to call an existing stored procedure in Sybase 12.5. The stored procedure returns a value as well as result set back. I can get the return value (sucess or failure) but can not get to the OUTPUT parameter from stored procedure. I created a result set and identified the column name in the ResultSet tab but no value is passed back. any ideas as to what I am missing?

Below is a snippet of the stored procedure:

CREATE PROCEDURE sp_mrtg_load_sts_sv
@id_mrtg_load_sts Numeric(8,0) = NULL OUTPUT
,@code_load Char(3) = NULL
,@cnt_procss Int = NULL
,@nbr_ssr Char(6) = NULL
,@cnt_updt Int = NULL
,@cnt_err Int = NULL
,@dt_load DateTime = NULL
,@dt_mrtg_load_beg DateTime = NULL
,@dt_mrtg_load_end DateTime = NULL
,@nbr_deal Char(6) = NULL
,@desc_load_key Char(50) = NULL
…

IF (@@rowcount = 1) 
BEGIN 
    SELECT @id_mrtg_load_sts = @@IDENTITY 
    RETURN 0 
END 
ELSE 
BEGIN 
    Print 'Problem with INSERT in sp_mrtg_load_sts_sv!' 
    RETURN 1 
END

From the above procedure I am trying to retrieve id_mrtg_load_sts

2

Is the SP working as expected when you execute using client tool(Toad or sql*plus etc…)?so that we can bet problem is withe JDBC Adapter(SP template)

Regards,

3

Yes, the sp is working as expected. I have verified this by using the sp with another SQL tool (Rapid SQL) and is used by other applications using various interfaces (This includes JDBC connections via WebLogic.)

4

Not sure if it will solve your problem, but you could try to add the following line to your extended settings:

watt.server.jdbc.moreResults=true

I believe IS needs to be restarted.