Community
Participate
Working Groups
Build Identifier: Support for cast in JPQL is missing. It is available in HQL from Hibernate : cast(... as ...), where the second argument is the name of a Hibernate type, and extract(... from ...) if ANSI cast() and extract() is supported by the underlying database (source : http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html) Could this be added as an extension? I tried with with EclipseLink JPA 2.1.0-SNAPSHOT Reproducible: Always
How does the cast functionality compare to what is described here: https://bugs.eclipse.org/bugs/show_bug.cgi?id=259266 http://wiki.eclipse.org/EclipseLink/Development/2.1/AdvancedJPA_Queries/DownCast
Down cast of entity aliases is something else. The cast support I am referring to is support for literal conversion : byte -> int int -> string string -> int int -> date etc.
Here is the cast documentation from MySQL : http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast
Resolved in 2.1 - http://wiki.eclipse.org/EclipseLink/Release/2.1.0/JPAQueryEnhancements#Query_Casting *** This bug has been marked as a duplicate of bug 259266 ***
Could I please have a JPQL example query for my case. In the presented link is only the following : select e from Employee e join TREAT(e.projects AS LargeProject) lp where lp.budget = value Can TREAT be used for literal casts? e.g. select treat(e.name as int) from Employee e or select e.name from Employee e where treat(e.name as int) > 0 As the issue has been resolved I assume that this is possible now. If not then I'd like to see this issue reopened.
I guess I jumped the gun closing your request. our TREAT...AS support allows you to cast with a mapped inheritance hierarchy. It does not do the primitive conversions you are looking for. sorry. I also tried using the new FUNC support added in 2.1 that allows you to call database operations such as: SELECT FUNC('TO_NUMBER', e.firstName) FROM Employee e or select e.name from Employee e where FUNC('TO_NUMBER', e.name) > 0 Unfortunately the current FUNC support does not appear to address the SQL structure of CAST( AS ). It uses a comma to separate the args. I am re-openening and will discuss with the developer of FUNC how we might call CAST.
Any progress on the issue?
I believe that are many other function usage cases that cannot be expressed with the func support. Both CAST and CONVERT from MySQL cannot be used : CAST(expr AS type) CONVERT(expr, type) With CAST, the AS and type causes problems and with CONVERT the type. If I try for example func('CONVERT',columnX, date) then the JPQL treats date as a SQL column reference and doesn't treat it as a type symbol. Maybe some template system would work better? e.g. FUNC('CONVERT($0, date)', columnX)
So what could be the workaround in the mean time if I need to convert a varchar to a decimal?
I'm trying to build a Criteria API query that would group its results by the truncated date part of a SQL Server 2008 datetime2 datatype, i.e. in the effect of the SQL query: select cast(created as Date), count(*) from table group by cast(created as Date) AFAIK there is no other way of doing this in EclipseLink Criteria API except to use the CriteriaBuilder's function method, except that it doesn't support the CAST(.. AS ..) syntax. (Another SQL server function that could be used is "dateadd(dd, datediff(dd,0, getDate()), 0)", but last time I checked it didn't work either with EclipseLink because of the constant "dd". An oldish thread about the subject: http://www.eclipse.org/forums/index.php/m/676855/)
See patch in bug#305187 This adds support for the SQL JPQL operator (see sql() in Expressions and JpaCriteriaBuilder.fromExpression() for Criteria API). i.e. Select SQL("? as int", e.name) from Employee e It allows for CAST and CONVERT and almost anything else to be done in JPQL and Criteria. However, I am still looking at adding CAST, CONVERT and EXTRACT to JPQL as first class functions.
Added support for CAST and EXTRACT on Hermes.
Created attachment 213670 [details] adds support fo CAST and EXTRACT
Fixed in 2.4
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
