Announcement Announcement Module
Collapse
No announcement yet.
StoredProcedure and output parameter (Sybase) Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • StoredProcedure and output parameter (Sybase)

    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:

    Code:
    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.

  • #2
    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!),

    Code:
    CREATE PROCEDURE SP_EXAMPLE &#40; SP_IN_PARAM VARCHAR, SP_OUT_PARAM OUT VARCHAR, SP_IN_OUT_PARAM IN OUT VARCHAR&#41;
    AS
    BEGIN
      SP_OUT_PARAM &#58;= 'SetByProc';
      SP_IN_OUT_PARAM &#58;= 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:

    Code:
            class SpExample extends StoredProcedure &#123;
            	public SpExample&#40;JdbcTemplate template, String sql&#41; &#123;
            		setJdbcTemplate&#40;template&#41;;
            		setSql&#40;sql&#41;;
        		    declareParameter&#40;new SqlParameter&#40;"inParam", Types.VARCHAR&#41;&#41;;
        		    declareParameter&#40;new SqlOutParameter&#40;"outParam", Types.VARCHAR&#41;&#41;;
        		    declareParameter&#40;new SqlOutParameter&#40;"inOutParam", Types.VARCHAR&#41;&#41;;
            		compile&#40;&#41;;
            	&#125;
            &#125;
    
        	Map params = new HashMap&#40;&#41;;
        	params.put&#40;"inParam", "IN "&#41;;
        	params.put&#40;"inOutParam", "INOUT"&#41;;
            
            StoredProcedure spExample = new SpExample&#40;template, "sp_example"&#41;;
            
            Map outParams = spExample.execute&#40;params&#41;;
            
            System.out.println&#40;"Out param is&#58; "+outParams.get&#40;"outParam"&#41;&#41;;
            System.out.println&#40;"In out param is&#58; "+outParams.get&#40;"inOutParam"&#41;&#41;;
    Result:

    Code:
    Out param is&#58; SetByProc
    In out param is&#58; INOUT IN
    Regards,

    Amit

    Comment


    • #3
      It did work!

      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:
      Code:
      ====================================
      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&#58; Output parameters have not yet been processed. Call getMoreResults&#40;&#41;." to be thrown with Sybase.

      Comment


      • #4
        StoredProcedure and output parameter

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

        Thanks,

        Comment

        Working...
        X