StoredProcedure and output parameter (Sybase)

[joaovic]

Hi,
I'm trying to read the value of a output parameter for a Sybase stored procedure. What should i do to have this job done? See below the example for such stored procedure:

create procedure sp_example
   @sp_in_parm  varchar(20)  ,
   @sp_out_parm varchar(255) output
as
select @sp_out_parm = "This is the message to return! <sp_example>"
return -1;

The value of the return code (-1) i already know how to do using SqlOutParameter, but what should i do to get the value of @sp_out_parm parameter?

Thanks for any help.

[amkush]

Hi,

You could use the SqlOutParameter for the OUT parameters to your stored procedures too.

Took your stored procedure and compiled it in Oracle (sorry!),

CREATE PROCEDURE SP_EXAMPLE ( SP_IN_PARAM VARCHAR, SP_OUT_PARAM OUT VARCHAR, SP_IN_OUT_PARAM IN OUT VARCHAR)
AS
BEGIN
  SP_OUT_PARAM := 'SetByProc';
  SP_IN_OUT_PARAM := SP_IN_OUT_PARAM || ' ' || SP_IN_PARAM;
END;

Also added an IN OUT parameter.

This piece of code calls the the procedure, passing in the IN parameters, executing the stored procedure and getting back the OUT parameters as results:

        class SpExample extends StoredProcedure {
        	public SpExample(JdbcTemplate template, String sql) {
        		setJdbcTemplate(template);
        		setSql(sql);
    		    declareParameter(new SqlParameter("inParam", Types.VARCHAR));
    		    declareParameter(new SqlOutParameter("outParam", Types.VARCHAR));
    		    declareParameter(new SqlOutParameter("inOutParam", Types.VARCHAR));
        		compile();
        	}
        }

    	Map params = new HashMap();
    	params.put("inParam", "IN ");
    	params.put("inOutParam", "INOUT");
        
        StoredProcedure spExample = new SpExample(template, "sp_example");
        
        Map outParams = spExample.execute(params);
        
        System.out.println("Out param is: "+outParams.get("outParam"));
        System.out.println("In out param is: "+outParams.get("inOutParam"));

Result:

Out param is: SetByProc
In out param is: INOUT IN

Regards,

Amit

[joaovic]

Thanks for your help.

It did work, but i realized that the real problem was a bug in my old Sybase JDBC driver (jtds-0.9.jar). It was fixed in the 0.9.1 version. Now i'm using a newer version, 1.0.1, and everything it's ok.

See below the change log of the jDTS driver:

====================================
12/08/2004 - jTDS 0.9.1 released
====================================
Changes from jTDS 0.9
=====================
10/04/2004 - Alin Sinpalean
Corrected a bug in TdsCore causing "java.sql.SQLException: Output parameters have not yet been processed. Call getMoreResults()." to be thrown with Sybase.

[jwu]

Has anyone tried with SQL Server. It appears that output parameters always returns the original input value.

Thanks,