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?

Improve this question
5
  • Is 'table' a big table? Do you have CREATE and DROP privileges? If so, try a temporary table. Commented Jan 11, 2012 at 19:13
  • 1
    @DanielNWerner How is it relevant to the question? Commented Jan 11, 2012 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. Commented Jan 19, 2012 at 23:22
  • @DanielNWerner And how would using a temporary table won't be the same? Commented Jan 20, 2012 at 1: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. Commented Jan 20, 2012 at 13:23

2 Answers 2

Reset to default
2
+50

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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.
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.
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.
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.
For me it's the same issue. I may not have millions but I could have thousands and having to reparse is a problem.
-1

Check the in_list and in_list_v operators

Improve this answer

1 Comment

Could you provide some details?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.