How to provide values dynamically in "using" in Informix ESQL/C?

Our Informix ESQL/C (.ec) program uses the query:

select count(*) from <table> where col >= val1 and col <= val2 

Inside the program we use:

select count(*) from <table> where col >= ? and col <= ? 

Now, the new requirement is we need to search based on a list of values. The list is dynamically generated and we are not sure of the size of the list. If the list contains 2 columns the query will be like:

select count(*) from <table> where ((col >= ? and col <= ?) OR (col >= ? and col <= ?))

We are able to frame the query, but we are not sure how to execute this.

The current execution is as follows:

exec sql execute :select_prepare using :val1, :val2

We have all the values in an integer array. Since we are not sure about the number of parameters, we got stuck in preparing the execute statement.

The command below didn't work

exec sql execute :select_prepare using :val_array 

Could anyone provide any solution for this?

That seems very awkward indeed. Ah, Jonathan has arrived with the definitive answer. However, my suggestion is a left-field alternative.

One way you could solve this would be to do something along these lines: NB: untested, conceptual code follows

CREATE TEMP TABLE ranges (
    lo INT,
    hi INT
);

for(..){
    INSERT INTO ranges (?,?) USING :var1, :var2;
}

SELECT COUNT(DISTINCT id) FROM table, ranges WHERE col BETWEEN lo AND hi

/* later.. */

DROP TABLE ranges;

You will need to create a descriptor - either an sqlda descriptor (see DESCRIBE and sqlda.h) or an SQL DESCRIPTOR. My inclination was to say "it is easier to use an sqlda descriptor", but (on second thoughts) I'm not sure that's really true.

With SQL descriptors, you'll be using:

ALLOCATE DESCRIPTOR
DEALLOCATE DESCRIPTOR
GET DESCRIPTOR
SET DESCRIPTOR

With sqlda, you can use DESCRIBE to get the information, or you can manufacture the descriptors yourself.

Anyway, the net result will be that you write:

EXEC SQL EXECUTE :select_prepare USING SQL DESCRIPTOR :name;

or

EXEC SQL EXECUTE :select_prepare USING DESCRIPTOR sqlda_info;

Note the syntactic oddity that you do not include a colon before sqlda_info.

If you need code that illustrates this stuff, then:

• DBD::Informix (Perl module, primarily source file dbdimp.ec) uses SQL DESCRIPTOR.
• SQLCMD (the original one, not Microsoft's johnny-come-lately version) uses sqlda.

In both cases, searching for DESCRIPTOR (upper-case) gives you strong pointers in the right direction.

(I note that you are still going to need to dynamically prepare the SQL; unless you use the temporary table suggested by @RET, the text of the SQL will vary depending on the number of ranges you need to test.)