title: How to call db2 database functions with literal arguments using the JPA Criteria Api?
body: <p>I have a DB2 database function declared as follows:</p>
  
  <pre><code>CREATE FUNCTION MYDB.FN_ISNEWSCOVERAGE(NEWS_ID INTEGER, USERID VARCHAR(50))
  RETURNS INTEGER
  ...
  </code></pre>
  
  <p>which contains some complex SQL logic and returns 1 or 0 (to indicate true/false).</p>
  
  <p>I'm trying to use the function to filter results within a query constructed using the JPA Criteria API (over Spring 3/Hibernate), as follows:</p>
  
  <pre><code>EntityManager em = ...
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery&lt;News&gt; cq = cb.createQuery(News.class);
  Root&lt;News&gt; news = cq.from(News.class);
  Predicate criteria = cb.conjunction();
  ...
  String userid = ...
  criteria = cb.and( criteria, 
                     cb.equal( cb.function( "MYDB.FN_ISNEWSCOVERAGE", 
                                            Long.class, 
                                            news.get("id"), 
                                            cb.&lt;String&gt;literal( userid ) ), 
                               1 )
                   );
  ...
  TypedQuery&lt;News&gt; tq = em.createQuery(cq);
  List&lt;News&gt; results = tq.getResultList();
  ...
  </code></pre>
  
  <p>The problem is that when the resulting query is run, db2 throws an error:</p>
  
  <pre><code>Caused by: com.ibm.db2.jcc.b.eo: DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610, SQLERRMC=null, DRIVER=3.53.95
  </code></pre>
  
  <p>which is described <a href="http://www.sqlerror.de/db2_sql_error_-418_sqlstate_42610.html" rel="nofollow">here</a>. It seems the problem is that the generated SQL contains a '?' positional parameter for one of the MYDB.FN_ISNEWSCOVERAGE function arguments - here is the relevant section of the generated query string:</p>
  
  <pre><code>SELECT ... from MYDB.NEWS news0_ where 1=1 and news0_.ACTIVE=? and MYDB.FN_ISNEWSCOVERAGE(news0_.NEWS_ID, ?)=1
  </code></pre>
  
  <p>If I copy the generated query into an sql client and replace the positional parameters with literal values, the query runs fine.</p>
  
  <p>Is there a way I can avoid this error when calling the database function from the JPA Criteria API? </p>
  
  <p><strong>Update</strong></p>
  
  <p>I've realised I can skirt around the problem by using a subquery to replace the literal function argument with a path expression, as follows:</p>
  
  <pre><code>Subquery&lt;News&gt; sq = cq.subquery(News.class);
  Root&lt;News&gt; sqNews = sq.correlate(news);
  Root&lt;User&gt; sqUser = sq.from(User.class);
  sq.select(news)
    .where(
       cb.equal(sqUser.get("id"), userid), 
       cb.equal(cb.function("MYDB.FN_ISNEWSCOVERAGE", 
                            Long.class, 
                            sqNews.get("id"), 
                            sqUser.get("id")),
                1)
    );
  criteria = cb.and(cb.exists(sq));
  </code></pre>
  
  <p>However this workaround might not be applicable in other instances - is there a better solution?</p>