Databases
StackOverflow2013
Postsdbo
POHow to call db2 database functions with literal arguments using the JPA Criteria Api?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to call db2 database functions with literal arguments using the JPA Criteria Api?
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USHenry
    1. USHenry
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POHow to call db2 database functions with literal arguments using the JPA Criteria Api?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you specify a SQL casting function around the parameter marker, such as CAST(? AS VARCHAR(50))
      singulars
      1. POHow to call db2 database functions with literal arguments using the JPA Criteria Api?
      1. USFred Sobotka
    2. COI'm not sure how I would do that as I don't have direct control of the SQL generated by the Criteria API queries.
      singulars
      1. POHow to call db2 database functions with literal arguments using the JPA Criteria Api?
      1. USHenry
    3. COdo you want some solution without using the **criteria API**?
      singulars
      1. POHow to call db2 database functions with literal arguments using the JPA Criteria Api?
      1. USManuPK
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload