CollectionOfElements in query

sajohns4 · **Joined:** Tue May 18, 2010 1:56 pm **Posts:** 1

I'm blown away by how much time I've wasted on this and I'm not any closer to a solution...I know I'm doing something really stupid. Here is my query:

SELECT carrier FROM Carrier carrier WHERE carrier.owningEnterprise.HID IN (?1) AND carrier.services IN (?2)

carrier.services is a CollectionOfElements (String), and I'm binding an ArrayList<String> to the 2nd parameter. I end up blowing up with the following sql error msg:

WARN - SQL Error: 17041, SQLState: null
ERROR - Missing IN or OUT parameter at index:: 2

All I'm trying to do is query a carrier object that has any (an 'or' search) of the services the user selects. What am I missing? Thanks for the help!

sanne.grinovero · **Posted:** Tue May 18, 2010 5:11 pm

the syntax you use to define parameters is wrong:
use either

Code:

SELECT carrier FROM Carrier carrier WHERE carrier.owningEnterprise.HID IN (?) AND carrier.services IN (?)

or

Code:

SELECT carrier FROM Carrier carrier WHERE carrier.owningEnterprise.HID IN (:first) AND carrier.services IN (:second)

BTW you might want to use Hibernate Search to search on String collections, way better performance and flexible matching.