IN() Operator with Host Array

From: Chris Doran <chris_doran_at_postmaster.co.uk>
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

Original text of this message