Section 4.6.17.3 unfortunately also very strictly limits the sources of values for the arguments that can be passed to the functions using its `function(name, ...)` syntax. And unfortunately SQL keywords is not one of those.
On Tue, Apr 24, 2018 at 9:12 AM Steve Ebersole <st...@hibernate.org> wrote: > When you say "standardize the definition of functions", are you speaking > about the which functions are defined as "built-in functions" or a > mechanism in JPA to define `SQLFunction` overrides/extras? > > JPA already has quite a number of built-in functions defined: MIN, TRIM, > LOWER, etc. I think we'd have a decent chance to get specific additions to > the spec - especially EXTRACT. DATEDIFF would probably be a harder sell > since it is not an ANSI SQL defined function - in ANSI SQL you'd accomplish > this using date arithmetic and EXTRACT, something like > > extract( DAY, enddate - startdate ) > > Technically TIMESTAMP minus TIMESTAMP is supposed to return an INTERVAL > (as opposed to an INTEGER) which itself can be an argument to EXTRACT. > > While DATEDIFF is not standardized in ANSI SQL, it is widely implemented > across different databases. So honestly not sure how well various > databases support DATEDIFF versus EXTRACT-from-INTERVAL > > > We also try to standardize support for additional functions beyond JPA in > Hibernate - so we have most Dialects implement YEAR, MONTH, DAY, HOUR, > MINUTE, SECOND functions, e.g.: > > select year(appt.startTime) from Appointment appt > > > > > > On Tue, Apr 24, 2018 at 8:42 AM Christian Beikov < > christian.bei...@gmail.com> wrote: > >> 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.bei...@gmail.com <mailto:christian.bei...@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@lists.jboss.org <mailto: >> hibernate-dev@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@lists.jboss.org <mailto:hibernate-dev@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@lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/hibernate-dev > > _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
