Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2007
    Posts
    10

    Unanswered: Error Calling SP

    Hi All,

    I am having an error calling the stored Procedure.This SP works fine when I execute the call from the Admin Client.It gives an error "Number of host variables ...." when it is run from the DBVisualiser or any third Pary tool or a runtime client.How can this be fixed.Please suggest.

    CREATE PROCEDURE Samp.execute_immediate (IN in_stmt VARCHAR(1000) ,OUT out_sqlcode INTEGER)
    LANGUAGE SQL
    MODIFIES SQL DATA
    BEGIN
    DECLARE sqlcode INTEGER;
    DECLARE EXIT HANDLER FOR sqlexception
    SET out_sqlcode = sqlcode;
    EXECUTE IMMEDIATE in_stmt INTO sqlcode;
    SET out_sqlcode = sqlcode;
    RETURN;
    END

    CALL Samp.execute_immediate (' UPDATE Samp.Employee SET STATUS = ''' || 'N'' ; ',?);

    Thanks,

  2. #2
    Join Date
    May 2003
    Posts
    113
    without further information, this is more like a guess:
    did you provide the value for the parameter marker? the error msg seems complain that DB2 didn't receive a value for the parameter marker(the 2nd argument of your stored proc).

  3. #3
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    You might be having a problem with the embedded terminator character, ';'. Try concatenating the hex value for the terminator character. I think it's x'59'.

  4. #4
    Join Date
    Sep 2007
    Posts
    10
    I have provided the complete Information.I just created the SP as it is shown above and executed the call.I get the following error
    [CALL - 0 row(s), 0.031 secs] [Error Code: -313, SQL State : 07004] The number of host variables in the EXECUTE or OPEN statement is not equal to the number of values required.

    ... 1 statement(s) executed, 0 row(s) affected, database exec time 0.031 sec [0 successful, 0 warnings, 1 errors]

    I did not provide any value for the parameter marker as it is an output paramter for the SQLCODE which is returned .SET out_sqlcode = sqlcode.
    I executed and as I said it works on the DB2 Administration Client without the error.It gives the error with runtime clietns.

Posting Permissions

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