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

One way is to construct the string for prepare dynamically - ( the c_sel text ), but I think it will defeat the purpose of using prepared statements. Second, include a temp table in the query and use a subquery on temp table after inserting the vals of blue etc in temp table. Third- if you know the max number of values in IN clause - use let c_txt = " sel ................................ where .............. in (?,?,?,?,?,?,?)" and execute using "blue","red","dummy","dummy",dummy" etc ( you can use an arry initialized to all dummy values and just populate the values you have - arr[1]=blue , arr[2]=red and then execute using this array Hope this helps Rgds Preetinder 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 > > sending to informix-list