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

I'd go with the temp table - but otherwise you should be able to pass in as many '?' as needed Something like : let c_sel = " select count(*) ", " from some_table", " where field_code = ? ", " and field_color in (?,?,?)", " into temp tx ", " with no log " execute c_sel using 10, 'blue', 'red', 'color' Now the difficult bit is that the number of colors may well change - so you'll probably need to generate the ?,?,? bit in a function, and have lots of executes with all the possible numbers of values in it.. So you may well end up with something like : case num_colours when 0 let lv_fc="" when 1 let lv_fc="and field_color in (?)" when 2 let lv_fc="and field_color in (?,?)" .. let c_sel = " select count(*) ", " from some_table", " where field_code = ? ", lv_fc, " into temp tx ", " with no log " case num_colours when 0 execute c_sel using 10 when 1 execute c_sel using 10,col[1] when 2 execute c_sel using 10,col[1],col[2] . . end case HTH 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 > > > sending to informix-list