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

#1

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

The caveat is not pass the string of colors at all with USING.
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

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

Anyway I think it is a fault that a collection cannot be passed to preapred statement with Using.

Chucho!

-----Original Message-----
From: vivek.chaudhary (AT) st (DOT) com
To: forum.subscriber (AT) iiug (DOT) org, ids (AT) iiug (DOT) org, John_Carlson (AT) whsmithusa (DOT) com
Date: Thu, 11 Sep 2003 10:55:54 -0700
Subject: RE: How do I pass a collection type to a prepared statement in [1836]

Jean,

I have similar problems by putting the variables in a temp. table, let
us say xyz_temp. Inside your prepared statement, you could say

let c_sel = "select count(*) ",
" from some_table",
" where field_color in (select field_color from xyz_temp) ",
" into temp tx ",
" with no log

Vivek

-----Original Message-----
From: John.Carlson [mailto:John_Carlson (AT) whsmithusa (DOT) com]
Sent: Thursday, September 11, 2003 10:19 AM
To: ids; forum.subscriber
Subject: RE: How do I pass a collection type to a prepared statement in
[1836]

Have you considered the "CONSTRUCT" statement in 4gl?

-----Original Message-----
From: Danny Wright [mailto:dwright (AT) sherwoodfoods (DOT) com]
Sent: Thursday, September 11, 2003 12:14 PM
To: ids (AT) iiug (DOT) org
Subject: Re: How do I pass a collection type to a prepared statement in
[1833]

There may be a better way, but you may have to dynamically build the SQL
statement and not take advantage of the USING for the collection....

It's probably not what you want to do since you have to PREPARE each
time,
but maybe someone has a better idea.

----- Original Message -----
From: "Jean Sagi " <jeansagi (AT) myrealbox (DOT) com>
To: <ids (AT) iiug (DOT) org>
Sent: Wednesday, September 10, 2003 9:56 PM
Subject: [Fwd: How do I pass a collection type to a prepared statement
in
[1829]

Quote:

-------- Original Message --------
Subject: How do I pass a collection type to a prepared statement in
Informix-4gl 7.30?
Date: Fri, 05 Sep 2003 12:46:27 -0500
From: Jean Sagi <jeansagi (AT) myrealbox (DOT) com
To: informix-list (AT) iiug (DOT) org
CC: jeansagi (AT) myrealbox (DOT) com

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 (AT) myrealbox (DOT) com
jeansagi (AT) netscape (DOT) net

--

Atte,

Jesús Antonio Santos Giraldo
jeansagi (AT) myrealbox (DOT) com
jeansagi (AT) netscape (DOT) net

"CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel
Retail. This email message and all attachments may contain legally
privileged and confidential information intended solely for the use of
the
addressee. If you are not the intended recipient, you should immediately
stop reading this message and delete it from the system. Any
unauthorized
reading, distribution, copying, or other use of this message or its
attachments is strictly prohibited. All personal messages express solely
the
sender's views and not those of WHSmith USA Travel Retail. This message
may
not be copied or distributed without this disclaimer."

Jean Sagi
jeansagi (AT) myrealbox (DOT) com
jeansagi (AT) netscape (DOT) net

sending to informix-list

#2

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

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

Quote:

" where field_code = ? ", --dynamic
" and field_color in (", c_col, ") ", --static-dynamic

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

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 ;(

(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)

Thanks for your time.

Chucho!
Jean Sagi
jeansagi (AT) myrealbox (DOT) com
jeansagi (AT) netscape (DOT) net

sending to informix-list

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

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

comp.databases.informix comp.databases.informix

[Solved] -- Re: RE: How do I pass a collection type to a prepared statement in [1836] - 09-11-2003 , 01:04 PM

Re: Re: [Solved] -- Re: RE: How do I pass a collection type to a prepared statement in [1836] - 09-11-2003 , 07:32 PM