Re: [Solved] -- Re: RE: How do I pass a collection type to a prepared

Jean Sagi wrote: > Well, I come to a conclusion that Informix 4gl 7.30 could not pass dinamucally a list of strings to a prepared query for use in an IN filter in the WHERE clause... (odd). You have had several solutions to this. You can, you just don't want to. Something like: " where field_code = ?", --dynamic " and field_color in (?, ?, ?, ?, ?, ?, ?, ?)", --dynamic > The caveat is not pass the string of colors at all with USING. Why? How many colours are you likely to need to pass? > Instead I statically construct the query adding the string of colors which is variable ans pass dinamically the other parameters: > > let c_col = "'blue','red','color'" > let c_sel = > " select count(*) ", > " from some_table", > " where field_code = ? ", --dynamic > " and field_color in (", c_col, ") ", --static-dynamic > " into temp tx ", > " with no log; " > > prepare st_sel from c_sel > execute c_sel using 10 Except that you will have to PREPARE this every time you run a new query. It would be better for performance to PREPARE the cursor using one of the solutions mentioned before, then EXECUTE it as many times as you need. > Is very simple... which of course is not what I was trying to do originally but achieves the same result... Sort of. > Anyway I think it is a fault that a collection cannot be passed to preapred statement with Using. I guess there's not a lot of call for it. :-) 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