Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2006
    Posts
    3

    Red face Unanswered: Problems in calling 1 procedure from another

    Hi,

    I am absolutely new to DB2 and am trying to write a DB2 sql procedure. The code is :

    CREATE PROCEDURE COUNTRY1 (IN i_perinc VARCHAR(2))
    LANGUAGE SQL
    Begin

    DECLARE c1 CURSOR WITH RETURN FOR
    SELECT country_code, country_name FROM country where country_code = i_perinc;
    OPEN c1;
    END

    Now I am trying to call this SP from another SP given below:

    CREATE PROCEDURE Test1 ( IN Name CHAR(4) )
    LANGUAGE SQL
    DYNAMIC RESULT SETS 1

    P1: BEGIN
    -- Declare cursor
    Declare v1 VARCHAR(2);
    DECLARE cursor1 CURSOR FOR
    SELECT AIRPORT_CODE FROM AIRPORT AS AIRPORT WHERE AIRPORT.AIRPORT_CODE = Name;
    OPEN cursor1;
    fetch cursor1 into v1;
    call country1(v1);

    END P1


    When I call the SP from command editor by giving the command :
    call Test1('22')

    I end up with the following error :

    SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.
    SQLSTATE=24501

    SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.

    Explanation:

    The program attempted to either: (1) FETCH using a cursor, or (2)
    CLOSE a cursor at a time when the specified cursor was not
    open.

    The statement cannot be processed.

    User Response:

    Check for a previous message (SQLCODE) that may have closed the
    cursor. Note that after the cursor is closed, any fetches or
    close cursor statements receive SQLCODE -501.

    If no previous SQLCODEs have been issued, correct the
    application program to ensure that the cursor is open when the
    FETCH or CLOSE statement is executed.

    sqlcode : -501
    sqlstate : 24501

    I am unable to fix the error, pls help.
    Last edited by ps_deep; 10-04-06 at 06:00.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What is your intent for SP test1? If it is to return the result set from the Country1 SP, then you need to change the cursor in Country1 from "WITH RETURN" to "WITH RETURN TO CLIENT".

    Andy

Posting Permissions

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