Re: How do I pass a collection type to a prepared statement in Informix-4gl

Thanks to Everett Mills and catfish, who answered, I think I could use both suggestions, parse the list of colors and include them in a temporary table. TIA Chucho! Jean Sagi wrote: > I have some minor problem with a 4gl - Sql statement. > Maybe someone has addreesed it before: > > Let say you have the following query: > > select count(*) > from some_table > where field_code = 10 > and field_color in ( "blue", "red", "color" ) > into temp tx > with no log;> > where table some_table exists and has a fields field_code and field_color > > let say that this query must be inside a 4gl program and you want to prepare it and execute it: > > let c_sel = > " select count(*) ", > " from some_table", > " where field_code = 10 ", > " and field_color in ( 'blue', 'red', 'color' ) ", > " into temp tx ", > " with no log " > > prepare st_sel from c_sel > execute c_sel > > NO problem... > > Now you want the filter "field_code = 10" be dinamyc : > > let c_sel = > " select count(*) ", > " from some_table", > " where field_code = ? ", > " and field_color in ( 'blue', 'red', 'color' ) ", > " into temp tx ", > " with no log " > > prepare st_sel from c_sel > execute c_sel using 10 > > No problem > > !!Now you want the filter "field_color in ( 'blue', 'red', 'color' )" be dynamic > > HOW DO YOU THAT? > > I tried different approaches but none of the worked... > > Ex: > > let c_sel = > " select count(*) ", > " from some_table", > " where field_code = ? ", > " and field_color in ? ", > " into temp tx ", > " with no log " > > prepare st_sel from c_sel > execute c_sel using 10, "('blue', 'red', 'color')" > > abort with the following error: > > Program stopped at "prog_name.4gl", line number xxx. > 4GL run-time error number -9650. > Right hand side of IN expression must be a COLLECTION type. > > BTW: > > #finderr -9650 > Message number -9650 not found. > > What I can see is that the right expression must be of collection type... so > > > How do I pass a collection type to a prepared statement in Informix-4gl? > > > > Chucho! > > PD: > > Operating System version : HP-UX serve-name B.11.00 U 9000/856 > INFORMIX-4GL Version : 7.30.HC7 > > Jean Sagi > jeansagi@myrealbox.com > jeansagi@netscape.net > > > -- Atte, Jes's Antonio Santos Giraldo jeansagi@myrealbox.com jeansagi@netscape.net sending to informix-list