[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] Problem using SqlResultsetMapping and nativeQuery
|
The problem is
>= TIMESTAMP ?startDate
You cannot add a bind variable in this way, it should be either,
>= ?startDate
where startDate is an instanceof java.sql.Timestamp (or Calendar)
or possibly,
>= TIMESTAMP(?startDate)
if startDate is a String.
NBW wrote:
>
> 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
>
>
-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland
http://www.eclipse.org/eclipselink/
EclipseLink , http://www.oracle.com/technology/products/ias/toplink/
TopLink
Wiki: http://wiki.eclipse.org/EclipseLink EclipseLink ,
http://wiki.oracle.com/page/TopLink TopLink
Forums: http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink ,
http://www.nabble.com/EclipseLink-f26430.html EclipseLink
Book: http://en.wikibooks.org/wiki/Java_Persistence Java Persistence
Blog: http://java-persistence-performance.blogspot.com/ Java Persistence
Performance
--
View this message in context: http://old.nabble.com/Problem-using-SqlResultsetMapping-and-nativeQuery-tp31327579p31388442.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.
