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

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? I don't know, I'd have to read the manual first..., but it's too much effort. If you know how many values there are going to be then you could do something like: LET c_sel = "SELECT COUNT(*)", " FROM some_table", " WHERE field_code = ?", " AND field_color IN (?, ?, ?)", " INTO TEMP tx WITH NO LOG" Then you could do: PREPARE st_sel FROM c_sel EXECUTE c_sel USING 10, "blue", "red", "color" If you DON'T know how many values there are going to be then you could populate another TEMP table first with the values for your list. Then you could do something like: LET c_sel = "SELECT COUNT(*)", " FROM some_table", " WHERE field_code = ?", " AND field_color IN", " (SELECT valid_color FROM my_temp_colors)", " INTO TEMP tx WITH NO LOG" Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+ sending to informix-list