Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Specifying bound parms in mixed jpa/native query
Specifying bound parms in mixed jpa/native query [message #1716042] Mon, 30 November 2015 19:20 Go to next message
Dave Brosius is currently offline Dave BrosiusFriend
Messages: 34
Registered: July 2009
Member
Greetings I have a JPA query that uses part native SQL('') to do UNIONS. Now both the JPA part and the native part have bound parameters. I'm wondering how to specify them, as it doesn't seem to work. My Query is as follows:

SELECT a FROM ActivityStreamEO a where a.time > ?1 AND a.time < ?2 AND a.activityStreamId IN (SQL('SELECT SYS_ASTREAM_ID FROM SYS_ASTREAM WHERE ACTION_USER_ID = ? UNION SELECT a.SYS_ASTREAM_ID FROM SYS_ASTREAM a, SYS_ASTR_SUBSCRIPTION e, FD_TABLE t WHERE a.FD_TABLE_ID = t.FD_TABLE_ID AND e.USER_ID = ? AND a.OBJECT_ID = e.OBJECT_ID AND ((e.STREAM_TYPE = ? AND t.TABLE_NAME = ?) OR (e.STREAM_TYPE = ? AND t.TABLE_NAME = ?))')) ORDER BY a.time DESC


When executing the query i specify all 8 (numbered) parameters.

However on running, i receive this error:

java.lang.IllegalArgumentException: You have attempted to set a parameter at position 3 which does not exist in this query string SELECT a FROM ActivityStreamEO a where a.time > ?1 AND a.time < ?2 AND a.activityStreamId IN (SQL('SELECT SYS_ASTREAM_ID FROM SYS_ASTREAM WHERE ACTION_USER_ID = ? UNION SELECT a.SYS_ASTREAM_ID FROM SYS_ASTREAM a, SYS_ASTR_SUBSCRIPTION e, FD_TABLE t WHERE a.FD_TABLE_ID = t.FD_TABLE_ID AND e.USER_ID = ? AND a.OBJECT_ID = e.OBJECT_ID AND ((e.STREAM_TYPE = ? AND t.TABLE_NAME = ?) OR (e.STREAM_TYPE = ? AND t.TABLE_NAME = ?))')) ORDER BY a.time DESC.


I tried specifying numbered params in the native, but that had the same effect.

I need the native because i need an order by attached to this query, and the eclipselink JPA apparently doesn't allow you to both UNION and ORDER BY.


Any idea how this is done?

Report message to a moderator

Re: Specifying bound parms in mixed jpa/native query [message #1716062 is a reply to message #1716042] Mon, 30 November 2015 22:18 Go to previous message
Dave Brosius is currently offline Dave BrosiusFriend
Messages: 34
Registered: July 2009
Member
Ah! i got it to work with named parms

SELECT a FROM ActivityStreamEO a where a.time > :leastRecentTime AND a.time < :mostRecentTime AND a.activityStreamId IN (SQL('SELECT SYS_ASTREAM_ID FROM SYS_ASTREAM WHERE ACTION_USER_ID = ? UNION SELECT a.SYS_ASTREAM_ID FROM SYS_ASTREAM a, SYS_ASTR_SUBSCRIPTION e, FD_TABLE t WHERE a.FD_TABLE_ID = t.FD_TABLE_ID AND e.USER_ID = ? AND a.OBJECT_ID = e.OBJECT_ID AND ((e.STREAM_TYPE = ? AND t.TABLE_NAME = ?) OR (e.STREAM_TYPE = ? AND t.TABLE_NAME = ?))', :userId, :userId, :userStreamSubscriptionType, :userTableName, :projectStreamSubscriptionType, :projectTableName)) ORDER BY a.time DESC

Report message to a moderator

Previous Topic:EclipseLink Constraint Violation Issue
Next Topic:Unable to unwrap connection with eclipselink.jdbc.exclusive-connection.mode=Always
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 23 13:23:32 GMT 2024

Powered by FUDForum. Page generated in 0.03066 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top

Sign up to our Newsletter

A fresh new issue delivered monthly