How do I pass a collection type to a prepared statement in Informix-4gl

[preetinder dhaliwal]

One way is to construct the string for prepare dynamically - ( the c_sel
text ), but I think it will defeat the purpose of using prepared statements.

Second, include a temp table in the query and use a subquery on temp
table after inserting the vals of blue etc in temp table.

Third- if you know the max number of values in IN clause -

use let c_txt = " sel ................................ where
............... in (?,?,?,?,?,?,?)"

and execute using "blue","red","dummy","dummy",dummy" etc ( you can use
an arry initialized to all dummy values and just populate the values you
have -
arr[1]=blue , arr[2]=red and then execute using this array

Hope this helps

Rgds
Preetinder

Jean Sagi wrote:

>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
>
>
>sending to informix-list
>
>

sending to informix-list

[Mark D. Stock]

Jean Sagi wrote:

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

I don't know, I'd have to read the manual first..., but it's too much effort.

If you know how many values there are going to be then you could do
something like:

LET c_sel = "SELECT COUNT(*)",
" FROM some_table",
" WHERE field_code = ?",
" AND field_color IN (?, ?, ?)",
" INTO TEMP tx WITH NO LOG"

Then you could do:

PREPARE st_sel FROM c_sel
EXECUTE c_sel USING 10, "blue", "red", "color"

If you DON'T know how many values there are going to be then you could
populate another TEMP table first with the values for your list. Then you
could do something like:

LET c_sel = "SELECT COUNT(*)",
" FROM some_table",
" WHERE field_code = ?",
" AND field_color IN",
" (SELECT valid_color FROM my_temp_colors)",
" INTO TEMP tx WITH NO LOG"

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /|
| Mydas Solutions Ltd [url]http://MydasSolutions.com[/url] |///// / //|
| +-----------------------------------+//// / ///|
| |We value your comments, which have |/// / ////|
| |been recorded and automatically |// / /////|
| |emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+

sending to informix-list

[Jean Sagi]

Thanks to Everett Mills and catfish, who answered,

I think I could use both suggestions, parse the list of colors and
include them in a temporary table.

TIA


Chucho!

Jean Sagi wrote:

> 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
> [email]jeansagi@myrealbox.com[/email]
> [email]jeansagi@netscape.net[/email]
>
>
>

--


Atte,


Jesús Antonio Santos Giraldo
[email]jeansagi@myrealbox.com[/email]
[email]jeansagi@netscape.net[/email]

sending to informix-list