How can I use the standard JPQL IN operator with ObjectDB?
This JPQL query works fine with EclipseLink / postgres but throws an exception in objectDB:
select distinct dv.creationtime from DataValue dv where dv.capabilityId.capabilityId ==> in <== :capabilities and dv.creationtime >= :earliest and dv.creationtime <= :latest order by dv.creationtime ASC
[ObjectDB 2.2.9] javax.persistence.PersistenceException
Unexpected query token 'in' (error 752)
The IN operator is currently not supported but you can use MEMBER OF instead.
See more details and an example in this forum thread.
thanks for suggestion, I modified the queries to read e.g. as follows:
@NamedQuery(name = "DataValue.findDistinctCreationtimesWithLimitDesc", query = " select distinct dv.creationtime from DataValue dv" + " where dv.capabilityId.capabilityId member of :capabilities" + " and dv.creationtime >= :earliest" + " and dv.creationtime <= :latest " + " order by dv.creationtime DESC"),
this seems to work in ObjectDB, but fails with EclipseLink .. I really need a portable solution, would you class that as an EclipseLink bug?
Caused by: Exception [EclipseLink-8025] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.JPQLException Exception Description: Syntax error parsing the query [DataValue.findDistinctCreationtimesWithLimitDesc: select distinct dv.creationtime from DataValue dv where dv.capabilityId.capabilityId member of :capabilities and dv.creationtime >= :earliest and dv.creationtime <= :latest order by dv.creationtime DESC], line 1, column 98: unexpected token [:capabilities].
This is not an EclipseLink bug since JPA implementations are not required to support a parameter on the right side of a MEMBER OF operator (just collection navigation paths).
I will check if a quick fix to support the in operator is possible.
A new build (2.2.9_08) adds support of the IN operator (currently as a synonym of MEMBER OF).
wow, awesome support, many thanks I will give this a spin ASAP ..
great news, 2.2.9_08 now passes all the jUnit tests for my db-lib module,
all static metadata loaded from xml files, so now I can get down to some serious bench marking and volume testing .. thanks again for great support guys ;-)
