4

I am trying to write Pro*C/C/C++ code to handle a query like this:

SELECT col1, col2, col3, col4 FROM table WHERE param IN(<set of values>);

I can have a C struct to retrieve the result and pass individual parameters but I can't seem to find a way to do this without explicitly specifying every value in the list.

Is there a way to pass the set as an array/vector/list?

Mat
  • 202,337
  • 40
  • 393
  • 406
Karlson
  • 2,958
  • 1
  • 21
  • 48
  • Is 'table' a big table? Do you have CREATE and DROP privileges? If so, try a temporary table. – dwerner Jan 11 '12 at 19:13
  • 1
    @DanielNWerner How is it relevant to the question? – Karlson Jan 11 '12 at 19:53
  • Creating a temporary table to query against, and inserting the data needed there, can be faster than a WHERE IN statement, which must do a table scan with all values provided. Often a SQL engine can optimize a query and make a better 'Query Plan' than this brute force approach. It depends on how many values you are trying to pass in. SQL does not have an array datatype, as TABLEs are the analogous datastructure in that domain. – dwerner Jan 19 '12 at 23:22
  • @DanielNWerner And how would using a temporary table won't be the same? – Karlson Jan 20 '12 at 01:42
  • Adding to that, using a temporary table would definitly increase the number of roundtrips between the client and the server. Very often it is the 1st killer of performance, it definitely is on our system. – Patrick Schlüter Jan 20 '12 at 13:23

2 Answers2

2

I asked that same question at How to specify a variable expression list in a Pro*C query?

I came up myself with a rather clumsy solution that works in my specific case. It might not apply to yours.

Community
  • 1
  • 1
Patrick Schlüter
  • 11,394
  • 1
  • 43
  • 48
  • Great answer but I was hoping to avoid doing things like this since 1 the list size is not fixed and 2 The AskTom link in the answer provides a solution is problematic for me to implement. – Karlson Jan 20 '12 at 20:48
  • My list is fixed to a maximum size, but the variables can be empty, which means that the effective number of values used is not fixed. – Patrick Schlüter Jan 21 '12 at 00:13
  • While that is true the maximum is fixed. In my case it is not. In some cases the number of ids in the list could be several hundred. – Karlson Jan 21 '12 at 01:46
  • Yes, indeed it would not be practical. One of the answers suggests building a statement string and making an EXECUTE STATEMENT. In my case it was not practicable because it would have had negative impacts on other things in the DB (the statement cache), but if you don't have millions of these kind of statements, it is quite a good solution. – Patrick Schlüter Jan 21 '12 at 09:49
  • For me it's the same issue. I may not have millions but I could have thousands and having to reparse is a problem. – Karlson Jan 23 '12 at 20:46
-1

Check the in_list and in_list_v operators

steve
  • 5,870
  • 1
  • 21
  • 22