Hi All,

I have created so many queries for DB2 and successfully executed it.
I am facing problem in executing query which uses IN keyword.

I have a text box on page where user can enter any number of comma separated values. I need to query DB using this parameter as

SELECT * FROM STATE WHERE STATE_ABBREVIATION IN (:stateAbb);

Here, stateAbb value I will pass from my java program.
Lets say user has entered in text box two values - DC, AL.

In my java program I am taking these values one by one and making it as 'DC' and 'AL' and again appending these as 'DC', 'AL' because in query it must be in single quotes.
So, my query became (printed in console):
SELECT * FROM STATE WHERE STATE_ABBREVIATION IN ('DC', 'AL');


Now, when I send it and execute this, I get the following error :
nested exception is java.sql.SQLException: An undefined column name was detected

The same query when I run directly on sql page ..it runs successfully.

I posted the same problem in Java forums, in one reply I got that JDBC will not accept 'DC','AL' because you are sending it as a string.

I do not know why so?

If anybody have any idea or solution, please let me know.

Thanks in advance.

Your problem is that you are trying to pass multiple items in one host variable. This cannot work. You are going to have to dynamically build the query, then execute it. You cannot use the host variable method to do this.

Andy