[hibernate-dev] How to deal with a constant in CriteriaQuery

[Christian Beikov]

The JPA spec actually says the function invocation syntax can be used 
for database functions in 4.6.17.3.

By "supported" you mean something like a note that says JPA providers 
may allow the invocation of JPA provider specific functions via that 
syntax? I'd love to go even further and standardize the definition of 
functions, but I guess that's not going to happen, so this seems to be a 
good compromise/first step.


Mit freundlichen Grüßen,
------------------------------------------------------------------------
*Christian Beikov*
Am 24.04.2018 um 15:21 schrieb Gail Badner:
> Yes, that should work with CriteriaQuery as well. It's a reasonable 
> workaround.
>
> If JPA doesn't support this now, is it something that should be 
> supported in the future?
>
> On Mon, Apr 23, 2018 at 11:45 PM, Christian Beikov 
> <christian.beikov at gmail.com <mailto:christian.beikov at gmail.com>> wrote:
>
>     Hey Gail,
>
>     I usually register a dedicated SQLFunction for every time value in
>     the
>     Dialect and use these functions instead. So in your case, I'd have a
>     "day" function and use it like
>
>     final Expression<Integer> diff = cb.function("DAY", Integer.class,
>     ... ).as(Integer.class);
>
>     The fuction will then render the SQL like it is supposed to be
>
>     "select datediff( day, ... ) from ..."
>
>     Although I'm not 100% sure that CriteriaBuilder.function properly
>     resolves the function since I never used custom function with the JPA
>     Criteria API directly, I'd at least expect it.
>
>     Mit freundlichen Grüßen,
>     ------------------------------------------------------------------------
>     *Christian Beikov*
>     Am 24.04.2018 um 07:29 schrieb Gail Badner:
>     > SQL Server defines a function:
>     >
>     > DATEDIFF ( datepart , startdate , enddate )  [1]
>     >
>     > (This method still needs to be added to SQLServer2012Dialect.)
>     >
>     > datepart can be one of a variety of time values, e.g., day,
>     week, year, etc.
>     >
>     > On SQL Server (at least) the value for datepart cannot be
>     treated as a
>     > literal (enclosed in quotes) or bound as a parameter.
>     >
>     > This causes problems when using DATEDIFF in a CriteriaQuery.
>     >
>     > final Expression<Integer> diff = cb.function("DATEDIFF",
>     Integer.class,
>     > cb.literal("day"), ... ).as(Integer.class);
>     >
>     > SQL Server throws:
>     > com.microsoft.sqlserver.jdbc.SQLServerException: Invalid parameter 1
>     > specified for datediff.
>     >
>     > There is no problem using the function in a query, as long as
>     datepart is
>     > not enclosed in quotes or bound to the query:
>     >
>     > "select datediff( day, ... ) from ..."
>     >
>     > Is there some way to set a literal value using a CriteriaQuery
>     that will
>     > not ultimately be enclosed in quotes or bound to a query?
>     >
>     > I've also been trying to find an integer constant that would be
>     equivalent
>     > to 'day', but haven't had any luck.
>     >
>     > Anyone have an idea how this could be done with CriteriaQuery?
>     >
>     > Thanks,
>     > Gail
>     >
>     > [1]
>     >
>     https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017
>     <https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017>
>     > _______________________________________________
>     > hibernate-dev mailing list
>     > hibernate-dev at lists.jboss.org <mailto:hibernate-dev at lists.jboss.org>
>     > https://lists.jboss.org/mailman/listinfo/hibernate-dev
>     <https://lists.jboss.org/mailman/listinfo/hibernate-dev>
>
>     _______________________________________________
>     hibernate-dev mailing list
>     hibernate-dev at lists.jboss.org <mailto:hibernate-dev at lists.jboss.org>
>     https://lists.jboss.org/mailman/listinfo/hibernate-dev
>     <https://lists.jboss.org/mailman/listinfo/hibernate-dev>
>
>