IDS Forum

RE: How do I pass a collection type to a prepared statement in

Posted By: vivek.chaudhary@st.com
Date:

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@whsmithusa.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@sherwoodfoods.com]
Sent: Thursday, September 11, 2003 12:14 PM
To: ids@iiug.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@myrealbox.com>
To: <ids@iiug.org>
Sent: Wednesday, September 10, 2003 9:56 PM
Subject: [Fwd: How do I pass a collection type to a prepared statement
in
[1829]


>
>
> -------- 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@myrealbox.com>
> To: informix-list@iiug.org
> CC: jeansagi@myrealbox.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@myrealbox.com
> jeansagi@netscape.net
>
>
>
>
> --
>
>
> Atte,
>
>
> Jesús Antonio Santos Giraldo
> jeansagi@myrealbox.com
> jeansagi@netscape.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."

Messages In This Thread

• RE: How do I pass a collection type to a prepared statement in
  John Carlson -- Thursday, 11 September 2003, at 1:19 p.m.
  • RE: How do I pass a collection type to a prepared statement in
    vivek.chaudhary@st.com -- Thursday, 11 September 2003, at 1:59 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.