-3

How can I use LIKE operator for the Graphic strings in OpenJPA for DB2?

I used as the following in the JPQL and failed.

select u from User u where u.userName like :userName

userName = VARGRAPHIC(20)

Then I use as the following and failed

   select u from User u where u.userName like CAST(:userName AS VARGRAPHIC(20))

Then I use as the following and failed

select u from User u where LOCATE(:userName,u.userName) > 0

I tried switching from deferPrepare to true to false but still failed

edited

with LOCATE

Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=LOCATE;FUNCTION, DRIVER=3.63.75

with LIKE

  DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=LIKE;FUNCTION, DRIVER=3.63.75

Any kind help is appreciated.

edited

I concat the parameter with %%. The problem is seemed to be LIKE doesn't accept in-balanced operands. Both must be a graphic string. So I set So I set

RequiresCastForComparisons=true

, then the query result containing an escape(Which is using LIKE ESCAPE '\'). So I set

RequiresSearchStringEscapeForLike=false

.

After those setting. When I was searching the string 花火大会 the with wild card paramter eg. %花% return empty records.

I will update the question with generated SQL tomorrow.

Thanks in advance

zawhtut
  • 8,335
  • 5
  • 52
  • 76
  • 1
    Failed how? Did your computer crash? – mustaccio Dec 10 '14 at 14:51
  • @mustaccio Sorry for not replying instantly. I updated my question. Thanks in advance. – zawhtut Dec 11 '14 at 07:27
  • First, `LIKE` without any wildcards is equivalent to `=` (and presumably `LOCATE(...)` would ignore wildcards). The error is complaining that it can't find `LIKE`/`LOCATE` as functions, which is really strange, to say the least. What do the statements actually run look like (not the JPQL - what actually gets run)? – Clockwork-Muse Dec 11 '14 at 11:02

1 Answers1

0

I had to use the wildcard character as doublebyte "％"　instead of normal "%" Hope that this will help someone.

zawhtut
  • 8,335
  • 5
  • 52
  • 76