Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

to_char() function in select statement of JPQL

 
Frank Truong
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I am a newbie to JPA specifically and O/R in general. I haven't been able to pass this bumper. I need some help here.

Could you tell me whether it is possible to use to_char() function in the native query using Oracle TopLink?
Basically, I have been trying to convert an entity attribute(in java.sql.Date type) to String using to_char() function unsuccessfully. For instance, I have some thing like following:


You can apparently see that I am trying to convert o.timestamp variable from Date object to String type, so I can compare against my filter.timestampStr String parameter using LIKE keyword. That is, records could be counted if filter.timestampStr matches lexicographically.

But Oracle TopLink keeps complaining about the 'to_char' term. It does not recognize it...... to_char unexpected character...

Thanks a lot.
Frank

P.S: Please ignore any typo I might have. I am manually typing that code block.
 
James Sutherland
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
JPQL does not support a to_char function. For a list of JPQL functions see,

http://en.wikibooks.org/wiki/Java_Persistence/JPQL_BNF#Functions

You can use a native SQL query (createNativeQuery()).

If you use EclipseLink 2.1, there is support for a FUNC operator that allow usage of any database function. This was also supported from the JPA 2.0 Criteria API in EclipseLink 2.0.
You can also use an EclipseLink/TopLink Expression query, which does support toChar().
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic