LIST data type

Posted in 2005

Topics: Storage & Space Management, Stored Procedures & SPL, Server Administration, Security, Permissions & Auditing, Data Types & Schema Design, Transactions, Locking & Isolation, Platform-Specific Issues, Versions, Editions & End-of-Life, Jobs, Consulting & Announcements

Bill Dare — Thu, 24 Feb 2005 08:04:25 -0500 — source: Usenet: comp.databases.informix

Aix 5.2 IDS 9.40.FC6 I've got some developers here who are using the LIST data type as input parameter to a stored procedure and then using that parameter in a WHERE IN condition. Is this a valid use of the LIST type? I question it because of the error returned in the example below. If I fetch only the first row and RETURN the procedure executes successfully. If I RETURN WITH RESUME I get the error below. If I run this same test on 9.40.FC3 all rows are returned with no errors. Case #425892 has been opened with tech support. Bill create table "informix".tab1 ( col1 serial not null , col2 integer, col3 char(8), col4 date, col5 datetime year to second ) extent size 32 next size 32 lock mode row; revoke all on "informix".tab1 from "public"; create unique index "informix".idx1 on "informix".tab1 (col1) using btree in rootdbs ; create index "informix".idx2 on "informix".tab1 (col2) using btree in rootdbs ; create index "informix".idx3 on "informix".tab1 (col3) using btree in rootdbs ; create index "informix".idx4 on "informix".tab1 (col4) using btree in rootdbs ; create index "informix".idx5 on "informix".tab1 (col5) using btree in rootdbs ; alter table "informix".tab1 add constraint primary key (col1) ; testdb:[/usr/informixtst/work/425892]> echo 'select * from tab1' | dbaccess test Database selected. col1 col2 col3 col4 col5 1 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 2 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 3 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 4 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 5 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 6 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 7 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 8 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 9 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 10 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 10 row(s) retrieved. Database closed. testdb:[/usr/informixtst/work/425892]> CREATE PROCEDURE "informix".test_list(pv_col3 LIST(CHAR(8) NOT NULL)) RETURNING INT ; define lv_col1 int; DEFINE GLOBAL sql_err, isam_err INT DEFAULT 0; DEFINE GLOBAL err_text VARCHAR(30) DEFAULT NULL; ON EXCEPTION SET sql_err, isam_err, err_text RAISE EXCEPTION sql_err, isam_err, err_text; END EXCEPTION; SET ISOLATION TO DIRTY READ; FOREACH SELECT col1 INTO lv_col1 FROM tab1 WHERE col3 in pv_col3 RETURN lv_col1 WITH RESUME; --RETURN lv_col1 ; END FOREACH; END PROCEDURE; testdb:[/usr/informixtst/work/425892]> echo 'execute procedure test_list(LIST{"abcdefgh"})' | dbaccess test Database selected. (expression) 9602: Illegal attempt to convert a collection type into another type. Error in line 1Near character position 45 Database closed. testdb:[/usr/informixtst/work/425892]> CREATE PROCEDURE "informix".test_list(pv_col3 LIST(CHAR(8) NOT NULL)) RETURNING INT ; define lv_col1 int; DEFINE GLOBAL sql_err, isam_err INT DEFAULT 0; DEFINE GLOBAL err_text VARCHAR(30) DEFAULT NULL; ON EXCEPTION SET sql_err, isam_err, err_text RAISE EXCEPTION sql_err, isam_err, err_text; END EXCEPTION; SET ISOLATION TO DIRTY READ; FOREACH SELECT col1 INTO lv_col1 FROM tab1 WHERE col3 in pv_col3 --RETURN lv_col1 WITH RESUME; RETURN lv_col1 ; END FOREACH; END PROCEDURE; testdb:[/usr/informixtst/work/425892]> echo 'execute procedure test_list(LIST{"abcdefgh"})' | dbaccess test Database selected. (expression) 1 1 row(s) retrieved. Database closed. testdb:[/usr/informixtst/work/425892]> sending to informix-list

↪ replying to Bill Dare

scottishpoet — 25 Feb 2005 05:51:22 -0800 — source: Usenet: comp.databases.informix

don't think a stored procedure can handle a list datatype you probably need to write a user defined function to do this rather than using SPL.

↪ replying to scottishpoet

Jonathan Leffler — Fri, 25 Feb 2005 15:27:48 GMT — source: Usenet: comp.databases.informix

scottishpoet wrote: > don't think a stored procedure can handle a list datatype > > you probably need to write a user defined function to do this rather > than using SPL. Do think you need to RTFM to see how SPL handles LIST types. :-) -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

↪ replying to Bill Dare

Jonathan Leffler — Fri, 25 Feb 2005 15:34:08 GMT — source: Usenet: comp.databases.informix

Bill Dare wrote: > Aix 5.2 > IDS 9.40.FC6 > > I've got some developers here who are using the LIST data type as input > parameter to a stored procedure and then using that parameter in a WHERE > IN condition. Is this a valid use of the LIST type? Apparently - yes. And it would solve someone else's problem, too - the person who was wanting to pass a random list of values to an IN clause in their SELECT statement. Well, it might help them... > I question it because of the error returned in the example below. If I > fetch only the first row and RETURN the procedure executes successfully. > If I RETURN WITH RESUME I get the error below. If I run this same test > on 9.40.FC3 all rows are returned with no errors. Case #425892 has been > opened with tech support. It worked in FC3; it broke in FC6. It sounds very like a bug to me. If it was previously erroneous behaviour, you'd have gotten an error message, not bad behaviour. Besides, the presence or absence of WITH RESUME should have zero effect on conversions, etc. > Bill > > > create table "informix".tab1 > ( > col1 serial not null , > col2 integer, > col3 char(8), > col4 date, > col5 datetime year to second > ) extent size 32 next size 32 lock mode row; > revoke all on "informix".tab1 from "public";> > > > create unique index "informix".idx1 on "informix".tab1 (col1) > using btree in rootdbs ; > create index "informix".idx2 on "informix".tab1 (col2) using btree > in rootdbs ; > create index "informix".idx3 on "informix".tab1 (col3) using btree > in rootdbs ; > create index "informix".idx4 on "informix".tab1 (col4) using btree > in rootdbs ; > create index "informix".idx5 on "informix".tab1 (col5) using btree > in rootdbs ; > alter table "informix".tab1 add constraint primary key (col1) > ; > > testdb:[/usr/informixtst/work/425892]> echo 'select * from tab1' | > dbaccess test > > Database selected. > > > > col1 col2 col3 col4 col5 > > 1 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 > 2 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 > 3 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 > 4 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 > 5 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 > 6 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 > 7 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 > 8 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 > 9 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 > 10 666 abcdefgh 01/02/2002 2002-01-02 00:00:00 > > 10 row(s) retrieved. > > > > Database closed. > > testdb:[/usr/informixtst/work/425892]> > > > CREATE PROCEDURE "informix".test_list(pv_col3 LIST(CHAR(8) NOT NULL)) > RETURNING INT ; > > define lv_col1 int; > > DEFINE GLOBAL sql_err, isam_err INT DEFAULT 0; > DEFINE GLOBAL err_text VARCHAR(30) DEFAULT NULL; > > ON EXCEPTION SET sql_err, isam_err, err_text > RAISE EXCEPTION sql_err, isam_err, err_text; > END EXCEPTION; > > SET ISOLATION TO DIRTY READ;> > FOREACH SELECT col1 > INTO lv_col1 > FROM tab1 > WHERE col3 in pv_col3 > > RETURN lv_col1 WITH RESUME; > --RETURN lv_col1 ; > END FOREACH; > > END PROCEDURE; > > testdb:[/usr/informixtst/work/425892]> echo 'execute procedure > test_list(LIST{"abcdefgh"})' | dbaccess test > > Database selected. > > > > (expression) > > > 9602: Illegal attempt to convert a collection type into another type. > Error in line 1> Near character position 45 > > > Database closed. > > testdb:[/usr/informixtst/work/425892]> > > CREATE PROCEDURE "informix".test_list(pv_col3 LIST(CHAR(8) NOT NULL)) > RETURNING INT ; > > define lv_col1 int; > > DEFINE GLOBAL sql_err, isam_err INT DEFAULT 0; > DEFINE GLOBAL err_text VARCHAR(30) DEFAULT NULL; > > ON EXCEPTION SET sql_err, isam_err, err_text > RAISE EXCEPTION sql_err, isam_err, err_text; > END EXCEPTION; > > SET ISOLATION TO DIRTY READ;> > FOREACH SELECT col1 > INTO lv_col1 > FROM tab1 > WHERE col3 in pv_col3 > > --RETURN lv_col1 WITH RESUME; > RETURN lv_col1 ; > END FOREACH; > > END PROCEDURE; > > testdb:[/usr/informixtst/work/425892]> echo 'execute procedure > test_list(LIST{"abcdefgh"})' | dbaccess test > > Database selected. > > > > (expression) > > 1 > > 1 row(s) retrieved. > > > > Database closed. > > testdb:[/usr/informixtst/work/425892]> > > > > > sending to informix-list -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

↪ replying to Bill Dare

Prasad — 7 Mar 2005 18:48:49 -0800 — source: Usenet: comp.databases.informix

As a workaournd, you can cast the argument to the parameter definition. For example, the following works in 9.40.FC6 execute procedure test_list_resume(LIST{"abcdefgh"}::LIST(char(8) notnull)); thanx Prasad

LIST data type

Glossary

Your privacy choices