f



Scope of Prepared Statements in SPL

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
0
armsiee
11/1/2016 8:51:40 AM
comp.databases.informix 16083 articles. 0 followers. Post Follow

0 Replies
173 Views

Similar Articles

[PageSpeed] 31

Reply: