1

I'm using Netbeans 7.3, Glassfish 3.1.2.2 to develop an aplication that connects to an AS400 using EclipseLink JPA 2.0. All queries work properly, until I get to a particular NamedQuery:

SELECT u FROM Table1 u WHERE u.field1 IN (:field1) 
AND SUBSTRING(u.field2,3,1) IN (:hack) AND 
SUBSTRING(u.field3,2,2) IN (:field3) ORDER BY u.field1

The parameters are set as follows:

Query query = getEntityManager().createNamedQuery("Table1.findAllWithRestrictions").setParameter("hack", "'S','C'").setParameter("field1", start).setParameter("field3", finish);

The query executes but returns with an empty list. Turning on logging gives me the generated SQL:

SELECT FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6 
FROM ABC.DATAB.TABLE1 WHERE (((FIELD1 IN ('00')) AND 
(SUBSTR(FIELD2, 3, 1) IN ('S','C'))) AND 
(SUBSTR(FIELD3, 2, 2) IN ('0S'))) ORDER BY FIELD1 ASC

Running this generated SQL against the same database connection returns several rows. There is no exception thrown in the logs anywhere, only an empty ResultList. What could I be doing wrong?

Zeratul2k
  • 13
  • 5
  • Try removing parts of the AND clauses until you get rows back. – Todd Murray Apr 24 '13 at 17:34
  • @ToddMurray I tried your suggestion, but the query only worked when I removed the whole WHERE clause. Now I'm wondering if it's a parameter problem, updated the question accordingly. – Zeratul2k Apr 24 '13 at 18:12
  • 2
    `setParameter("hacK", ...)` needs a list as the second param. You are passing a literal string. [See here](http://stackoverflow.com/questions/4378824/adding-in-clause-list-to-a-jpa-query) – Todd Murray Apr 24 '13 at 18:34
  • Ok, solved the problem. It was a mix of several factors, and I noticed them only after following @ToddMurray's suggestion. First of all, the parameters for IN clauses have to be in a list, not as a concatenated String. Second and most important, the parameters I was receiving included single quotes as String delimiters, which had to be stripped before passing them to setParameter. After fixing all these little issues I'm finally receiving a ResultList with the expected data. (I tried posting this as an answer to the question, but I don't have enough reputation for that just yet) – Zeratul2k Apr 24 '13 at 19:40

1 Answers1

0

Some implementations of JPA employ caching (such as EclipseLink) for performance reasons. If the data in your database doesn't line up with what you are seeing on the running application, I would try a reboot. Also, you may want to look at having some way of refreshing the cache or disabling it.

Example: Eclipselink JPA caching

Erich
  • 2,743
  • 1
  • 24
  • 28
  • I doubt the problem is cache related, since I've been forced to restart several times already, trying to isolate the cause of this. In any case, I tried your suggestion and refreshed the cache programatically, but the ResultList still comes out empty, despite all other NamedQueries working properly. I'll edit the question to add how the parameters are set. – Zeratul2k Apr 24 '13 at 18:06