Re: updating 'LIST' elment from SPL
This is a bug in the UPDATE statement on a collection-derived table.
It works just fine if you change
UPDATE TABLE(p) SET pro_name = pro WHERE CURRENT OF cursor1;
to
LET r.pro_name = pro;
UPDATE TABLE(p) (x) SET x = r WHERE CURRENT OF cursor1;
As this form is rather "under-documented", you might prefer:
LET r.pro_name = pro;
DELETE FROM TABLE(p) WHERE CURRENT OF cursor1;
INSERT INTO TABLE(p) VALUES (r);
Regards,
Doug Lawry
www.douglawry.webhop.org
"Alexey Sonkin" <alexeis@grandvirtual.com> wrote in message
news:btcsjt$gmc$1@terabinaries.xmission.com...
>
> Hi, everybody,
>
> The following example taken from "9.21 SQL TUTORIAL"
> doesn't work neither with 9.21 nor with 9.40; Similar example
> taken from '9.40 tutorial' also doesn't work with 9.40:
>
> ------------------------------
> CREATE TABLE manager
> (
> mgr_name VARCHAR(30),
> department VARCHAR(12),
> dept_no SMALLINT,
> direct_reports SET( VARCHAR(30) NOT NULL ),
> projects LIST( ROW ( pro_name VARCHAR(15),
> pro_members SET( VARCHAR(20) NOT NULL ) ) NOT NULL),
> salary INTEGER
> );>
> INSERT INTO manager(mgr_name, department, direct_reports, projects)
> VALUES
> (
> 'Sayles', 'marketing',
> "SET {'Simonian', 'Waters', 'Adams', 'Davis', 'Jones'}",> "LIST{ " ||
> "ROW ('voyager_project', SET{'Simonian', 'Waters','Adams',
'Davis'}),
> " ||
> "ROW ('horizon_project', SET{'Freeman', 'Jacobs','Walker', 'Smith',
> 'Cannan'}), " ||
> "ROW ('saphire_project', SET{'Villers', 'Reeves','Doyle',
'Strongin'})
> " ||
> "}"
> );
>
> CREATE PROCEDURE update_pro(mgr VARCHAR(30), pro VARCHAR(15))>
> DEFINE p COLLECTION;
> -- In Informix example, the SPL line below simply said "DEFINE r
> ROW;"
> -- that line produced the following syntax error "999: Not
> implemented yet."
> DEFINE r ROW(pro_name VARCHAR(15), pro_members SET(VARCHAR(20) NOT
> NULL));
> LET r = ROW("project", "SET{'member'}");
>
> SELECT projects INTO p FROM manager
> WHERE mgr_name = mgr;
>
> FOREACH cursor1 FOR
> SELECT * INTO r FROM TABLE(p)> IF (r.pro_name == 'horizon_project') THEN
> -- The following operator gives runtime error:
> UPDATE TABLE(p) SET pro_name = pro
> WHERE CURRENT OF cursor1;
> -- Executing this procedure produces error -217:
> -- Column (pro_name) not found in any table in the query (or SLV is
> undefined).
>
> EXIT FOREACH;
> END IF;
> END FOREACH
>
> UPDATE manager SET projects = p
> WHERE mgr_name = mgr;>
> END PROCEDURE;
>
> EXECUTE PROCEDURE update_pro('Sayles', 'my_project');> -----------------------------------------------
>
> I know, that the workaround is to convert list explicitly to a temporary
> table, make an update on that table and convert it back to a list.
>
> Nevertheless, it looks very strange to me that an example from 'tutorial'
> doesn't work. May be, situation can be corrected by minor change in
> the 'update' SQL statement?
>
> -------------
> best regards,
> Alexey