Issues with Cursor in informix procedure

pbangalore · #1 (**permalink**) 11-25-09, 10:26

I have a simple procedure with a cursor and am running into syntax error at the declaration of cursor line.

Sample code is below

CREATE PROCEDURE test()

BEGIN
DEFINE ent_type_id INTEGER;

CREATE TEMP Table entities(name varchar(64));

DECLARE ent_types CURSOR FOR
Select ent_type_id
from ent_type_def
for read only;

Open ent_types;
FETCH ent_types into :ent_type_id;

WHILE SQLCODE == 0
INSERT INTO entities
select name
from ent_type_def
where ent_type_id = :ent_type_id;

FETCH ent_types into :ent_type_id;
END WHILE;

Select name
from entities;

END;
END PROCEDURE;

ceinma · #2 (**permalink**) 11-26-09, 09:38

1) What IDS version?
2) You are written an ESQL/C style over SPL ... never will work...

If you work with IDS before 11.50 , you must use FOREACH can't use PREPARE+DECLARE+FETCH .

If you use IDS 11.50 this is the right syntax, this command already allowed, check the manual: Usage.

your code with corrections...

Code:

#Using DECLARE (works only in IDS 11.50)
#tested in IDS 11.50 UC5

create temp table ent_type_def (name char(10), ent_type_id int);
Temporary table created.                                        

CREATE PROCEDURE test()

BEGIN
DEFINE v_ent_type_id INTEGER;;

CREATE TEMP Table entities(name varchar(64));;

PREPARE s_ent_types FROM "Select ent_type_id from ent_type_def for read only" ;;
DECLARE c_ent_types CURSOR FOR s_ent_types;;

Open c_ent_types;;
FETCH c_ent_types into v_ent_type_id;;

WHILE SQLCODE == 0
INSERT INTO entities
select name
from ent_type_def
where ent_type_id = v_ent_type_id;;

FETCH c_ent_types into v_ent_type_id;;
END WHILE;;

--For what is this SELECT???
--Select name from entities;

END;;
END PROCEDURE;
Routine created.

;

Database closed.

pbangalore · #3 (**permalink**) 11-26-09, 14:53

Thanks for the quick reply. we are using IDS 10 and probably that is the reason i am having problem. And also the last select is the one returning back from the procedure.

So i guess i will have to use FOREACH since we are using IDS 10, or i will have to follow your logic right?

Thanks again.