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

#1

Jean Sagi wrote:

Quote:

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

Quote:

The caveat is not pass the string of colors at all with USING.

Why? How many colours are you likely to need to pass?

Quote:

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.

Quote:

Is very simple... which of course is not what I was trying to do originally but achieves the same result...

Sort of.

Quote:

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.

+----------------------------------------------------------+-----------+

Quote:

Mark D. Stock mailto:mdstock (AT) MydasSolutions (DOT) 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

#2

Jean Sagi wrote:

Quote:

" and field_color in (?, ?, ?, ?, ?, ?, ?, ?)",

I dind't like this approach 'cause if the list of colors grows I have to
modify the query... I prefer the temporary table solution some mentioned

instead...

Thanks.

Quote:

Why? How many colours are you likely to need to pass?

It's user configurable... so some time could be 3, other times 7... etc,
the user decides...

Yes, but they can only enter a finite number. That's the number of question
marks you need.

Quote:

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.

You're rigth, but this time I have the luck on my side... the program
I'm doing is a deamon (it never finish), and when it starts to execute the

list of colors is fixed; so I managed to do what you describe... prepare it
once one executing many times... wich have improved the response time

Oh good.

Quote:

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. :-)

Yeah, it would be nicer... that's my problem with 4gl: Sometimes it
doesn't do what I'm telling to do, even when it is supposed that it is

somethig that work ;(

Quote:

(For example didn't you know that you can´t use the concatenation
operator in a select inside a 4gl program... it is really funny it works on

dbaccess but not in 4gl 7.30... luckyly i managed to workarround this)

Well if you PREPARE all your SQL you shouldn't run into problems like that. :-)

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+

Quote:

Mark D. Stock mailto:mdstock (AT) MydasSolutions (DOT) 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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

comp.databases.informix comp.databases.informix

Re: [Solved] -- Re: RE: How do I pass a collection type to a prepared - 09-11-2003 , 03:27 PM

Re: [Solved] -- Re: RE: How do I pass a collection type to a prepared - 09-12-2003 , 06:03 AM