EclipseLink NativeQuery IN parameter with Collection

Question

I'm working with EclipseLink 2.4, running on GlassFish 2.1

I have a NativeQuery:

String SQL = select distinct(ka.id) from KOLCSON ka where ka.fk_kuldo not in (?)

The fk_kuldo foreign key has a VARCHAR type

Creating a query:

List<String> kuldokList = fedKuldoFacade.getKuldok();
Query q = em.createNativeQuery(SQL).setParameter(1, kuldokList);

The getKuldok() method returns with an ArrayList<String>

Unfortunately, I get an exception, caused by:

Caused by: com.ibm.db2.jcc.a.SqlException: Invalid data conversion: 
 Parameter object type is invalid for requested conversion.

Am I missing something, or it is not possible, to set a Collection parameter to a native query?

Answer 1

You can't pass in a collection to a native query without it being passed to the driver as a serializable object. What you are attempting would require the driver take a collection and break it up, which I don't know of any that can, and providers are not supposed to change the SQL you pass in. You will have to add in a parameter for each element in the list, or use JPQL that can handle collection parameters