Results 1 to 11 of 11
  1. #1
    Join Date
    May 2013
    Posts
    6

    Unanswered: Get RETURN value from SP call in SP with DB2

    I am having problems finding the RETURN value from a stored procedure call in DB2. I DO NOT want to add an OUT parameter, I want to use the RETURN statements. I have been trying to find a solution for the past couple of hours and the last thing I found was that I need to use the GET DIAGNOSTICS and SQLERRD(1). However, DB2 does not know what SQLERRD is. The last time I was told to use SQLERRD() to get the number of rows affected by a calll I actually had to use the ROW_COUNT variable.

    Is there an equivalent for use here?

    Code:
    CREATE PROCEDURE IS_ROOM_UNIQUE (
        IN "P_CUSTOMER_NM"	VARCHAR(50),
        IN "P_ROOM_NM"	VARCHAR(32) )
      READS SQL DATA
    BEGIN
    
    DECLARE SQLSTATE CHAR(5);
    DECLARE FOUND_IT INTEGER;
    
    CALL IS_ROOM_IN_INVENTORY(P_CUSTOMER_NM, P_ROOM_NM);
    GET DIAGNOSTICS FOUND_IT = ???;
    
    IF FOUND_IT > 0 THEN
      RETURN FOUND_IT;
    END IF;
    
    CALL IS_ROOM_IN_ACTIVE_ORDERS(P_CUSTOMER_NM, P_ROOM_NM);
    GET DIAGNOSTICS FOUND_IT = ???;
    
    IF FOUND_IT > 0 THEN
      RETURN FOUND_IT * 10;
    END IF;
    
    RETURN 0;
    END;

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by mperemsky View Post
    I DO NOT want to add an OUT parameter, I want to use the RETURN statements.
    In that case you may also want to familiarize yourself with the concept of a user-defined function.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2013
    Posts
    6

    functions

    yeah, thought about that too. However, as the RETURN statement was identified to be used for returning error codes and I also need to know how to use that for other functions that I am developing I would prefer to learn how to retrieve that for now. Then move on to functions after I have learned this.

    I am new to DB2 and am trying to get things one step at a time. This step is the RETURN statement. :-)

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check the SQL reference for your DB2 platform and version, specifically the RETURN statement description. It will tell you if, and how, you can pass the procedure return code to the caller.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by mperemsky View Post
    I am new to DB2 and am trying to get things one step at a time. This step is the RETURN statement. :-)
    Great step! :-)
    Next step should be catching this value by:
    Code:
    GET DIAGNOSTICS FOUND_IT = DB2_RETURN_STATUS;
    Regards,
    Mark.

  6. #6
    Join Date
    May 2013
    Posts
    6

    Awesome

    Awesome!

    Thanks for that. Now to finish testing this procedure. Much appreciated. If this works, I will start looking into error handling soon. :-)

  7. #7
    Join Date
    May 2013
    Posts
    6

    Well, not quite there yet!

    Ok, the SP works (in the sense that it executed in the DB). When trying to call it from my JAVA code it fails to find the procedure, telling me that it cannot find a procedure with a matching signature.

    I think the part that I may have wrong here is how I am constructing the string to call the SP. The string that I was using before to call the SP without the RETURN was as follows, with parameters specified at 1 and 2:

    Code:
    call IsRoomUnique(?,?)
    Should the new string be something like:

    Code:
    ? = call IsRoomUnique(?,?)
    Registering an out integer at 1 the my inputs now at 2 and 3?

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Are you calling the SP in java from the same USER as the one who created it? You should qualify (schema name) all calls.

    Andy

  9. #9
    Join Date
    May 2013
    Posts
    6

    Schema is there

    I have the schema name included on all calls. Just left it off in the example. It was working until I added the RETURN in the SP. Now I am trying to get the RETURN value in the Java code.

  10. #10
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by mperemsky View Post
    Should the new string be something like:

    Code:
    ? = call IsRoomUnique(?,?)
    Registering an out integer at 1 the my inputs now at 2 and 3?
    Yes.
    Should be:
    Code:
    cs = con.prepareCall("? = call IsRoomUnique(?,?)");
    cs.registerOutParameter(1, java.sql.Types.INTEGER);
    cs.setXXX(2, ...);
    cs.setYYY(3, ...);
    cs.executeUpdate();
    rc = cs.getInt(1);
    Regards,
    Mark.

  11. #11
    Join Date
    May 2013
    Posts
    6

    It works!

    Thanks. I tried the same thing after I posted yesterday and got it working. Was going to repost this morning with the solution.

    Thanks for your answer too. I appreciate the help.

Posting Permissions

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