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

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

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