Oninit cdi / Archive
Talk to an expert →

Re: procedure stored

Posted in 2004

Topics: Stored Procedures & SPL, Versions, Editions & End-of-Life, Jobs, Consulting & Announcements

Araujo Guntin — — source: Usenet: comp.databases.informix

Thank you, but when I use: > > create procedure sp_selectin(incoditicu char(30000)).... > > where coditicu = incoditicu ... It's work successfully. How could I know my IDS version? I use Informix SE 7.25 >From: Jonathan Leffler <jleffler@earthlink.net> >Reply-To: Jonathan Leffler <jleffler@earthlink.net> >To: informix-list@iiug.org >Subject: Re: procedure stored >Date: Wed, 05 May 2004 05:44:53 GMT > >Araujo wrote: > > I created the following procedure stored: > > > > create procedure sp_selectin(incoditicu char(30000)) > > returning integer, char(50) ;> > define p_coditicu integer ; > > define p_nombticu char(50); > > foreach > > select coditicu, nombticu > > into p_coditicu, p_nombticu > > from tticu > > where coditicu in (incoditicu) > > return p_coditicu, p_nombticu with resume ;> > end foreach; > > end procedure; > > > > incoditicu -> is sequence of number that I'll use in 'where coditicu in > > (incoditicu)' sentences. > > > > But, the problems is when I call to sp_selectin procedure an string like >... > > > > String coditicu = "2,4,5"; > > > > {call sp_coditicu(" + coditicu + ")} > > > > The procedure understand it how different arguments. > > > > What can I do? > >What you're trying to do is called Dynamic SQL, and SPL (Stored >Procedure Language) does not support it. You could get yourself the >Dynamic SQL datablade - assuming you have IDS 9.30 or 9.40 (well, 9.x, >but you shouldn't be using an x < 30). Failing that, you can play >clever tricks with default arguments. > >-- >Jonathan Leffler #include <disclaimer.h> >Email: jleffler@earthlink.net, jleffler@us.ibm.com >Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ > _________________________________________________________________ MSN Amor: busca tu ' naranja http://latam.msn.com/amor/ sending to informix-list

↪ replying to Araujo Guntin
Richard Harnden — — source: Usenet: comp.databases.informix

[ please don't top post ] "Araujo Guntin" <araujo_guntin@hotmail.com> wrote in message news:c7ag97$9g5$1@terabinaries.xmission.com... >Jonathan Leffler wrote in message <pb%lc.7647$V97.1199@newsread1.news.pas.earthlink.net> > >Araujo wrote: > > > I created the following procedure stored: > > > > > > create procedure sp_selectin(incoditicu char(30000)) > > > returning integer, char(50) ;> > > define p_coditicu integer ; > > > define p_nombticu char(50); > > > foreach > > > select coditicu, nombticu > > > into p_coditicu, p_nombticu > > > from tticu > > > where coditicu in (incoditicu) > > > return p_coditicu, p_nombticu with resume ;> > > end foreach; > > > end procedure; > > > > > > incoditicu -> is sequence of number that I'll use in 'where coditicu in > > > (incoditicu)' sentences. > > > > > > But, the problems is when I call to sp_selectin procedure an string like > >... > > > > > > String coditicu = "2,4,5"; > > > > > > {call sp_coditicu(" + coditicu + ")} > > > > > > The procedure understand it how different arguments. > > > > > > What can I do? > > > >What you're trying to do is called Dynamic SQL, and SPL (Stored > >Procedure Language) does not support it. You could get yourself the > >Dynamic SQL datablade - assuming you have IDS 9.30 or 9.40 (well, 9.x, > >but you shouldn't be using an x < 30). Failing that, you can play > >clever tricks with default arguments. > > > > Thank you, but when I use: > > > create procedure sp_selectin(incoditicu char(30000))> .... > > > where coditicu = incoditicu > ... > It's work successfully. Are you sure ? For sp_selectin("2), then it's okay; for sp_selectin("2,4,5"), then it isn't. Remember that: create procedure sp_selectin(incoditicu char(30000))... where coditicu in (incoditicu) and, execute procedure sp_selectin("2,4,5") results in: where coditicu in ("2,4,5") and not: where coditicu in (2,4,5) -- which is what you really want. > > How could I know my IDS version? I use Informix SE 7.25 > If you're using SE then you don't have an IDS version since they're different products. (you should've said you were using SE in the first place) For SE you'll have to populate a temporary table, something like: create procedure sp_selectin()... where coditicu in (select coditicu from coditicu_tmp) and create temp table coditicu_tmp ( coditicu integer ) with no log; insert into coditicu_tmp values (2); insert into coditicu_tmp values (4); insert into coditicu_tmp values (5); execute procedure sp_selectin(); -- rh

Glossary