Results 1 to 3 of 3

Thread: Return values

  1. #1
    Join Date
    Mar 2003
    Location
    India
    Posts
    4

    Question Unanswered: Return values

    Can DB2 stored procedure return values? If yes, will the following work!

    CREATE PROCEDURE SP8 ( )
    READS SQL DATA
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    BEGIN
    DECLARE int1 integer;
    set int1=2;
    return int1;
    END

    The above SP8 return value 2. Using Java, i try to call the stored procedure by preparing CallableStatement object as follows

    //Note: conn is the Connection object
    CallableStatement cstmt = conn.prepareCall("{CALL ?=SP8()}");
    cstmt.registerOutParameter(1, Types.INTEGER);
    csmt.execute();
    Integer returnValue = cstmt.getInteger(1);

    During execution, the following error occurs ...

    java.sql.SQLException: [DataDirect][DB2 JDBC Driver]Object has been closed.
    at com.ddtek.jdbc.base.BaseExceptions.createException (Unknown Source)
    at com.ddtek.jdbc.base.BaseExceptions.getException(Un known Source)
    at com.ddtek.jdbcx.base.BaseStatementWrapper.closedEx ception(Unknown Source)
    at com.ddtek.jdbcx.base.BasePreparedStatementWrapper. execute(Unknown Source)
    at com.ibm.ejs.cm.proxy.StatementProxy.executeCommon( StatementProxy.java:212)
    at com.ibm.ejs.cm.proxy.PreparedStatementProxy.execut e(PreparedStatementProxy.java:39)

    Please let me know where the problem is?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The syntax of your call is incorrect, Try:

    CallableStatement cstmt = conn.prepareCall("{? = CALL SP8()}");

    HTH

    Andy

  3. #3
    Join Date
    Mar 2003
    Location
    India
    Posts
    4

    Can DB2 stored procedure return values?

    Thanks. Sorry, the code was wrong. I tried with

    CallableStatement cstmt = conn.prepareCall("{? = CALL SP8()}");

    But, still it doesnot work

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •