to_char() function in select statement of JPQL

[Frank Truong]

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:

................
Query q = em.createQuery("select count(o) from Table1 as o where to_char(o.timestamp,'MM/DD/YYYY HH24:MI:SS') LIKE '%' + filter.timestampStr + '%'
return ((Long)q.getSingleResult()).intValue();
................

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]

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().