Re: [eclipselink-users] Problem using SqlResultsetMapping and nativeQuer

[NBW <emailnbw@xxxxxxxxx>]

Hi Tom & James -

So I deconstructed my query down to a literal one that I proved out works when executed in the Oracle SQL web interface. I inserted it back into my code sans any params like so:

 Query query = entityManager.createNativeQuery("SELECT sh.id, (SELECT COUNT(1) FROM Foo sh, Bar z, Baz tl WHERE sh.ingestTimestamp >= TIMESTAMP '1969-12-31 19:00:00.0' AND sh.ingestTimestamp<= TIMESTAMP '2011-04-06 17:57:08.737' AND z.buzz=sh.buzz AND tl.zoneid=z.zoneid AND tl.textValue LIKE '%ext%') as TOTALCOUNT FROM Foo sh, Bar z, Baz tl WHERE sh.ingestTimestamp >= TIMESTAMP '1969-12-31 19:00:00.0' AND sh.ingestTimestamp<= TIMESTAMP '2011-04-06 17:57:08.737' AND z.buzz=sh.buzz AND tl.zoneid=z.zoneid AND tl.textValue LIKE '%ext%' GROUP BY sh.id", "fooAndCountMapping");

This works, I get a List<Object[]> with Object[0] being a Foo entity (with only the id filled in) and Object[1] being the value 10 indicating there were a total of ten rows returned. 

OK, so working backwards I started adding params. The first one was the Like _expression_ -

 Query query = entityManager.createNativeQuery("SELECT sh.id, (SELECT
COUNT(1) FROM Foo sh, Bar z, Baz tl WHERE sh.ingestTimestamp >=
TIMESTAMP '1969-12-31 19:00:00.0' AND sh.ingestTimestamp<= TIMESTAMP
'2011-04-06 17:57:08.737' AND z.buzz=sh.buzz AND tl.zoneid=z.zoneid AND
tl.textValue LIKE ?likeExpr) as TOTALCOUNT FROM Foo sh, Bar z, Baz tl
WHERE sh.ingestTimestamp >= TIMESTAMP '1969-12-31 19:00:00.0' AND
sh.ingestTimestamp<= TIMESTAMP '2011-04-06 17:57:08.737' AND
z.buzz=sh.buzz AND tl.zoneid=z.zoneid AND tl.textValue LIKE ?likeExpr
GROUP BY sh.id", "fooAndCountMapping");
query.setParameter("likeExpr", "%" + searchTerms + "%");

Executing that also worked. Then I added in the 'startTime' -

 Query query = entityManager.createNativeQuery("SELECT sh.id, (SELECT
COUNT(1) FROM Foo sh, Bar z, Baz tl WHERE sh.ingestTimestamp >=
TIMESTAMP ?startDate AND sh.ingestTimestamp<= TIMESTAMP
'2011-04-06 17:57:08.737' AND z.buzz=sh.buzz AND tl.zoneid=z.zoneid AND
tl.textValue LIKE ?likeExpr) as TOTALCOUNT FROM Foo sh, Bar z, Baz tl
WHERE sh.ingestTimestamp >= TIMESTAMP ?startDate AND
sh.ingestTimestamp<= TIMESTAMP '2011-04-06 17:57:08.737' AND
z.buzz=sh.buzz AND tl.zoneid=z.zoneid AND tl.textValue LIKE ?likeExpr
GROUP BY sh.id", "fooAndCountMapping");

query.setParameter("startDate", startTimestamp);
query.setParameter("likeExpr", "%" + searchTerms + "%");

That's when things fell apart. I am now getting, oddly enough:

Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis

Error Code: 907
Call: SELECT sh.id, (SELECT COUNT(1) FROM Foo sh, Bar z, Baz tl WHERE sh.ingestTimestamp >= TIMESTAMP ? AND sh.ingestTimestamp<= TIMESTAMP '2011-04-06 17:57:08.737' AND z.buzz=sh.buzz AND tl.zoneid=z.zoneid AND tl.textValue LIKE ?) as TOTALCOUNT FROM Foo sh, Bar z, Baz tl WHERE sh.ingestTimestamp >= TIMESTAMP ? AND sh.ingestTimestamp<= TIMESTAMP '2011-04-06 17:57:08.737' AND z.buzz=sh.buzz AND tl.zoneid=z.zoneid AND tl.textValue LIKE ? GROUP BY sh.id
    bind => ['1969-12-31 19:00:00.0', %his%, '1969-12-31 19:00:00.0', %his%]
Query: ResultSetMappingQuery(sql="SELECT sh.id, (SELECT COUNT(1) FROM Foo sh, Bar z, Baz tl WHERE sh.ingestTimestamp >= TIMESTAMP ? AND sh.ingestTimestamp<= TIMESTAMP '2011-04-06 17:57:08.737' AND z.buzz=sh.buzz AND tl.zoneid=z.zoneid AND tl.textValue LIKE ?) as TOTALCOUNT FROM Foo sh, Bar z, Baz tl WHERE sh.ingestTimestamp >= TIMESTAMP ? AND sh.ingestTimestamp<= TIMESTAMP '2011-04-06 17:57:08.737' AND z.buzz=sh.buzz AND tl.zoneid=z.zoneid AND tl.textValue LIKE ? GROUP BY sh.id")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:684)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:526)
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1729)
    at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:234)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:264)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelect(DatasourceCallQueryMechanism.java:246)
    at org.eclipse.persistence.queries.ResultSetMappingQuery.executeDatabaseQuery(ResultSetMappingQuery.java:212)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:808)
    at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:711)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2842)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1521)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1503)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1477)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:484)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getResultList(EJBQueryImpl.java:741)

I am failing to understand why it thinks there is a missing right parenthesis! Thoughts? Thanks,

-Noah