IN() Operator with Host Array
Date: 14 Aug 2002 07:38:35 -0700
Message-ID: <948f0720.0208140638.60bde782_at_posting.google.com>
[Quoted] Is it possible to use a host array to give the set for the IN() operator from Pro*C? It works with OCI, but with Pro*C it only seems to use the first element of the array. I can't decide from wording of the the manual whether it's allowed in Pro*C or not.
I have:
EXEC SQL BEGIN DECLARE SECTION;
static int Channels[10];
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE ListCursor CURSOR FOR
SELECT MyItem FROM MyTable WHERE Channel IN(:Channels);
Channels[0] = etc.;
EXEC SQL OPEN ListCursor;
for (;;)
{
EXEC SQL Fetch ListCursor INTO :MyItem;
...
}
A look at the C code produced doesn't seem to show any point where the size of the Channels array is passed, which is probably why it's treated as a set of one.
As would be expected, it works only if I make the IN clause IN(:Channels[0],:Channels[1],...). I tried making Channels a string, which works with just one item but it fails "invalid number" if the string contains a comma-separated list.
Also, the size of my set is variable. I would like to do IN(:Channels FOR :COUNT), but this gives a syntax error.
My workaround is to do it by dynamic SQL, but I can't help feeling you should be able to pass the set as an array.
Chris Received on Wed Aug 14 2002 - 16:38:35 CEST