1. Forum
  2. Usenet
  3. COMP.DATABASES.INFORMIX

  • Scope of Prepared Statements in SPL

    From armsiee@21:1/5 to All on Tue Nov 1 01:51:40 2016
    IBM Informix Dynamic Server Version 12.10.UC1E

    CREATE PROCEDURE prepCursors()

    ...


    LET vSQL = "SELECT item FROM Plan where planno = ? ";

    PREPARE pr_get_plan FROM vSQL;
    DECLARE cr_get_plan CURSOR WITH HOLD FOR pr_get_plan;

    ....

    EXECUTE FUNCTION getData() INTO vData

    ...


    END PROCEDURE

    CREATE FUNCTION getData() RETURNING CHAR(10)

    OPEN cr_get_Plan USING vPlanno;
    FETCH cr_get_Plan INTO vItem;
    CLOSE cr_get_Plan;

    --- SQL error = -26055 ISAM error = 0 error string = "cr_gePlan"
    exception : handler FOUND

    -26055 Either statement-id or cursor name (%s) is not defined

    This error occurs when either statement-id or a cursor name is not
    defined before attempting to use it. Make sure to either PREPARE
    the statement-id or DECLARE the cursor before using it.

    END FUNCTION


    Above simplified version on the problem I'm having. I'm coming to the conclusion that prepared statements / cursors are only available in the procedure / function that they are defined?

    Please tell me this is not true and what I'm doing wrong in order that i can prepare all my statements upfront and OPEN them at the desired point as I would do in a 4gl program?

    Thanks in advance

    Simon

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)