IUS-SPL: Collections / Foreach Problem
Hi.
In Unviversal Server (oops: Informix Dynamic Server with Universal
Data Option) I try to use collections in a stored procedure.
(It's an A depends on B scenario. When a new fact is inserted, say B depends
on C, I'd like to insert the assertion A depends on C. Thus I'd
like to retrieve first everything that depends on B for example.)
I thought I did it like it is described in IUS-Guide to SQL: Tutorial
pages 14-43ff. Yet, IUS complains that it needs a cast from integer
to set(integer not null).
It seems that I don't understand the 'select a into set_a' part.
Can anybody help?
Below an example.
-- create sample database
--
-- close database;
-- drop database test_collections;
creat database test_collections;
database test_collections;
-- create some tables
--
--drop table numbers; commit work;
create table numbers (
a integer not null,
b integer,
primary key (a,b)
);commit work;
-- drop table numbers2; commit work;
create table numbers2 (
a integer,
b integer,
primary key (a,b)
);commit work;
-- insert some test data
--
insert into numbers values ( 1,2);
insert into numbers values ( 2,2);
insert into numbers values ( 3,4);
insert into numbers values ( 1,4);commit work;
-- show tables
select * from numbers; commit work;
select * from numbers2; commit work;
--
-- this is the procedure I can't get to work!
--
--drop procedure proc_numbers; commit work;
create procedure proc_numbers ( na integer, nb integer)
define contents_a set ( integer NOT NULL ); define no_a set ( integer not null );
define no integer;
select a into contents_a from numbers where a=na;
foreach cursor1 for select * into no from table ( contents_a )
insert into numbers2 values ( no, 7 ); end foreach;
end procedure;
commit work;
-- execute procedure
-- results in error
execute procedure proc_numbers ( 2,2); commit work;
-- if it had run here's the result
select * from numbers2;commit work;