Announcement Announcement Module
Collapse
No announcement yet.
IBM Db2 - Issue with finder add with like search Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • IBM Db2 - Issue with finder add with like search

    Hi,

    I am using the finder addons in Roo. I added a search for like. I am getting the below error message. In researching this issue, DB2 does not understand this sql and the code needs to be altered as shown below. What is the best way to override the code/sql?

    == code needs to be altered as follows ===
    SELECT o FROM T4DdaMonetaryExtTxn AS o WHERE LOWER(o.extTxnDesc) LIKE :extTxnDesc

    q.setParameter("extTxnDesc", extTxnDesc.toUpperCase());

    === I added the finder by doing the following: ====
    finder add --finderName findT4DdaMonetaryExtTxnsByExtTxnDescLike

    === Error I am getting using the above finder =====
    Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
    Caused by: com.ibm.db2.jcc.am.mo: DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610, SQLERRMC=null, DRIVER=4.7.85

    ==== this is the code that roo generated. It is causing the error above ====

    Code:
    public static TypedQuery<T4DdaMonetaryExtTxn> T4DdaMonetaryExtTxn.findT4DdaMonetaryExtTxnsByExtTxnDescLike(String extTxnDesc) {
            if (extTxnDesc == null || extTxnDesc.length() == 0) throw new IllegalArgumentException("The extTxnDesc argument is required");
            extTxnDesc = extTxnDesc.replace('*', '%');
            if (extTxnDesc.charAt(0) != '%') {
                extTxnDesc = "%" + extTxnDesc;
            }
            if (extTxnDesc.charAt(extTxnDesc.length() - 1) != '%') {
                extTxnDesc = extTxnDesc + "%";
            }
            EntityManager em = T4DdaMonetaryExtTxn.entityManager();
            TypedQuery<T4DdaMonetaryExtTxn> q = em.createQuery("SELECT o FROM T4DdaMonetaryExtTxn AS o WHERE LOWER(o.extTxnDesc) LIKE LOWER(:extTxnDesc)", T4DdaMonetaryExtTxn.class);
            q.setParameter("extTxnDesc", extTxnDesc);
            return q;
        }

  • #2
    I found the answer to this question. No need for response. Thanks

    Comment


    • #3
      Originally posted by greenek View Post
      I found the answer to this question. No need for response. Thanks
      What is the answer?

      Even more, what was the problem? The IBM DB2's SQLCODE -418 means "Use of parameter marker not valid."

      Comment


      • #4
        Originally posted by greenek View Post
        I found the answer to this question. No need for response. Thanks
        Hi Greenek,
        Could you tell me, please, how you fix that? I'm having the same problem with DB2 and roo finders with search using 'LIKE'.
        Thanks in advance.

        Comment


        • #5
          DB2 doesn't support parameter markers in some scalar functions.

          Roo generates the finder method with restrictions like "WHERE LOWER(o.name) LIKE LOWER(:name)", so that you can do case insensitive search.

          But the problem is that LOWER(:name) is not allowed in DB2. You'll have to remove the second LOWER function in the where clause, and convert :name to lower case in your java code.

          Comment

          Working...
          X