[Fwd: How do I pass a collection type to a prepared statement in
-------- 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
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
>
>
>
Danny's (W)right. You can supply values via placeholders; you can't supply
whole chunks of syntax. It depends on how intensive your work is going to
be. Unless it is unusually intensive, it is probably not worth worrying
about the difference between multiple prepares and the alternatives. If
you are extra extremely under-worked personally, and the application is
incredibly critical on performance, you could consider creating a temp
table with the appropriate column type, and then modify the main statement
to use the contents of that temp table to control the list of values.
Don't forget to run UPDATE STATISTICS on the temp table (so the system
knows it has some data in it). You might get different performance out of
a join compared with a IN (SELECT * FROM temp_table). And depending on the
number of values in the temp table, you might find that the preferred
notation varies; at least, back in the days of OnLine 5.00 (it didn't have
triggers - it wasn't 5.01) this sort of skulduggery gave the best
performance. Designing the correct SQL on the fly, based on the exact data
for the query that was to be asked, gave huge performance benefits.
--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Data Management
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"
PS: Apologies for the awful pun.
|---------+---------------------------->
| | "Danny Wright" |
| | <dwright@sherwood|
| | foods.com> |
| | Sent by: |
| | forum.subscriber@|
| | iiug.org |
| | |
| | |
| | 09/11/2003 09:14 |
| | AM |
|---------+---------------------------->
>-------------------------------------------------------------------------------
--------------------------------------------------------------|
| |
| To: ids@iiug.org |
| cc: |
| 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
>
>
>