7:29 a.m.
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-sq...
8:45 a.m.
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-sq...
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
3:21 p.m.
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(a)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
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
3:36 p.m.
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(a)gmail.com <mailto:christian.beikov@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-sq...
<https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sq...
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev(a)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(a)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>
4:12 p.m.
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.beikov(a)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.beikov(a)gmail.com <mailto:christian.beikov@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-sq...
> <
https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sq...
>
> > _______________________________________________
> > hibernate-dev mailing list
> > hibernate-dev(a)lists.jboss.org <mailto:
hibernate-dev(a)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(a)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(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
4:16 p.m.
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 <steve(a)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.beikov(a)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.beikov(a)gmail.com <mailto:christian.beikov@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-sq...
> > <
>
https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sq...
> >
> > > _______________________________________________
> > > hibernate-dev mailing list
> > > hibernate-dev(a)lists.jboss.org <mailto:
> hibernate-dev(a)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(a)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(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
4:28 p.m.
I'm speaking about a mechanism to define what Hibernate calls
`SQLFunction` in a JPA standard way. Every JPA provider has this
concept. I even implemented a JPA provider abstraction for this in
Blaze-Persistence and it works pretty well.
How does the specification process in the Eclipse Foundation work, or is
it not yet defined? I'd be curious what it takes for an idea or a
feature to be considered.
Mit freundlichen Grüßen,
------------------------------------------------------------------------
*Christian Beikov*
Am 24.04.2018 um 16:12 schrieb Steve Ebersole:
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.beikov(a)gmail.com <mailto:christian.beikov@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.beikov(a)gmail.com <mailto:christian.beikov@gmail.com>
<mailto:christian.beikov@gmail.com
<mailto:christian.beikov@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-sq...
>
<https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017>
> > _______________________________________________
> > hibernate-dev mailing list
> > hibernate-dev(a)lists.jboss.org
<mailto:hibernate-dev@lists.jboss.org>
<mailto: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(a)lists.jboss.org
<mailto:hibernate-dev@lists.jboss.org>
<mailto: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(a)lists.jboss.org <mailto:hibernate-dev@lists.jboss.org>
https://lists.jboss.org/mailman/listinfo/hibernate-dev
4:29 p.m.
On Tue, Apr 24, 2018 at 8:45 AM Gail Badner <gbadner(a)redhat.com> wrote:
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?
The problem with defining support for this in the spec is that it is
relying on Hibernate's "SQL function registry" and its `SQLFunction`
contract. I seriously doubt we'd get all the EG members to agree to some
standardization of anything like a `SQLFunction` contract.
I think proposing to add additional functions to the spec as "built-in" is
probably more likely. I can especially see EXTRACT being likely. Maybe
DATEDIFF. Oracle for sure does not support DATEDIFF, but it does support
the EXTRACT-from-INTERVAL approach. Anyone know offhand other databases
which to not define DATEIDFF?
I personally think having DATEDIFF defined as "built-in" is the best option
as the provider can always map that to EXTRACT-from-INTERVAL for Oracle,
etal - its much harder to do that by mapping EXTRACT-from-INTERVAL to
DATEDIFF.
4:33 p.m.
JPA is technically under the old JCP still AFAIK. So for now the process
would be the same it has always been.
I just do not see how this would ever get agreed upon for a standardized
contract - it is so very dependent upon how the provider models the query
(SQM e.g.) versus the specific mechanism they use to render it (SQL AST).
On Tue, Apr 24, 2018 at 9:29 AM Steve Ebersole <steve(a)hibernate.org> wrote:
On Tue, Apr 24, 2018 at 8:45 AM Gail Badner
<gbadner(a)redhat.com> wrote:
> 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?
The problem with defining support for this in the spec is that it is
relying on Hibernate's "SQL function registry" and its `SQLFunction`
contract. I seriously doubt we'd get all the EG members to agree to some
standardization of anything like a `SQLFunction` contract.
I think proposing to add additional functions to the spec as "built-in" is
probably more likely. I can especially see EXTRACT being likely. Maybe
DATEDIFF. Oracle for sure does not support DATEDIFF, but it does support
the EXTRACT-from-INTERVAL approach. Anyone know offhand other databases
which to not define DATEIDFF?
I personally think having DATEDIFF defined as "built-in" is the best
option as the provider can always map that to EXTRACT-from-INTERVAL for
Oracle, etal - its much harder to do that by mapping EXTRACT-from-INTERVAL
to DATEDIFF.
4:56 p.m.
Maybe we should wait until it transitioned to Eclipse then? Or do you
think it might make sense to start discussions already?
The API could be String based by default but allow to "unwrap" to do
something provider specific. If the providers model requires it, the
String could be parsed by the provider.
Imagine an API like the following
interface SQLFunction {
ExpressionType getType(FunctionContext ctx, List<ExpressionType>
argumentTypes);
Expression render(FunctionContext ctx, List<Expression> arguments);
interface FunctionContext {
ExpressionType getExpressionType(Class<?> javaType);
Expression getExpression(String expressionString);
<T> T unwrap(Class<T> clz);
}
interface ExpressionType {
Class<?> getJavaType();
<T> T unwrap(Class<T> clz);
}
interface Expression {
String getExpressionString();
<T> T unwrap(Class<T> clz);
}
}
That's just a rough idea of how it could look.
Mit freundlichen Grüßen,
------------------------------------------------------------------------
*Christian Beikov*
Am 24.04.2018 um 16:33 schrieb Steve Ebersole:
JPA is technically under the old JCP still AFAIK. So for now the
process would be the same it has always been.
I just do not see how this would ever get agreed upon for a
standardized contract - it is so very dependent upon how the provider
models the query (SQM e.g.) versus the specific mechanism they use to
render it (SQL AST).
On Tue, Apr 24, 2018 at 9:29 AM Steve Ebersole <steve(a)hibernate.org
<mailto:steve@hibernate.org>> wrote:
On Tue, Apr 24, 2018 at 8:45 AM Gail Badner <gbadner(a)redhat.com
<mailto:gbadner@redhat.com>> wrote:
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?
The problem with defining support for this in the spec is that it
is relying on Hibernate's "SQL function registry" and its
`SQLFunction` contract. I seriously doubt we'd get all the EG
members to agree to some standardization of anything like a
`SQLFunction` contract.
I think proposing to add additional functions to the spec as
"built-in" is probably more likely. I can especially see EXTRACT
being likely. Maybe DATEDIFF. Oracle for sure does not support
DATEDIFF, but it does support the EXTRACT-from-INTERVAL approach.
Anyone know offhand other databases which to not define DATEIDFF?
I personally think having DATEDIFF defined as "built-in" is the
best option as the provider can always map that to
EXTRACT-from-INTERVAL for Oracle, etal - its much harder to do
that by mapping EXTRACT-from-INTERVAL to DATEDIFF.
8:18 p.m.
When I asked about whether JPA should support this in the future, I was
thinking along the lines of adding something like the following
to javax.persistence.criteria.CriteriaBuilder:
Keyword keyword(String value); // rendered as a String without quotes
or:
Expression<String> literal(String value, encloseInQuotes);
On Tue, Apr 24, 2018 at 7:56 AM, Christian Beikov <
christian.beikov(a)gmail.com> wrote:
Maybe we should wait until it transitioned to Eclipse then? Or do
you
think it might make sense to start discussions already?
The API could be String based by default but allow to "unwrap" to do
something provider specific. If the providers model requires it, the
String could be parsed by the provider.
Imagine an API like the following
interface SQLFunction {
ExpressionType getType(FunctionContext ctx, List<ExpressionType>
argumentTypes);
Expression render(FunctionContext ctx, List<Expression> arguments);
interface FunctionContext {
ExpressionType getExpressionType(Class<?> javaType);
Expression getExpression(String expressionString);
<T> T unwrap(Class<T> clz);
}
interface ExpressionType {
Class<?> getJavaType();
<T> T unwrap(Class<T> clz);
}
interface Expression {
String getExpressionString();
<T> T unwrap(Class<T> clz);
}
}
That's just a rough idea of how it could look.
Mit freundlichen Grüßen,
------------------------------------------------------------------------
*Christian Beikov*
Am 24.04.2018 um 16:33 schrieb Steve Ebersole:
> JPA is technically under the old JCP still AFAIK. So for now the
> process would be the same it has always been.
>
> I just do not see how this would ever get agreed upon for a
> standardized contract - it is so very dependent upon how the provider
> models the query (SQM e.g.) versus the specific mechanism they use to
> render it (SQL AST).
>
>
> On Tue, Apr 24, 2018 at 9:29 AM Steve Ebersole <steve(a)hibernate.org
> <mailto:steve@hibernate.org>> wrote:
>
> On Tue, Apr 24, 2018 at 8:45 AM Gail Badner <gbadner(a)redhat.com
> <mailto:gbadner@redhat.com>> wrote:
>
> 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?
>
>
> The problem with defining support for this in the spec is that it
> is relying on Hibernate's "SQL function registry" and its
> `SQLFunction` contract. I seriously doubt we'd get all the EG
> members to agree to some standardization of anything like a
> `SQLFunction` contract.
>
> I think proposing to add additional functions to the spec as
> "built-in" is probably more likely. I can especially see EXTRACT
> being likely. Maybe DATEDIFF. Oracle for sure does not support
> DATEDIFF, but it does support the EXTRACT-from-INTERVAL approach.
> Anyone know offhand other databases which to not define DATEIDFF?
>
> I personally think having DATEDIFF defined as "built-in" is the
> best option as the provider can always map that to
> EXTRACT-from-INTERVAL for Oracle, etal - its much harder to do
> that by mapping EXTRACT-from-INTERVAL to DATEDIFF.
>
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
10:43 p.m.
I'd personally not like that approach. I think specific registrations (for
extract and datediff) are better options
On Tue, Apr 24, 2018, 1:18 PM Gail Badner <gbadner(a)redhat.com> wrote:
When I asked about whether JPA should support this in the future, I
was
thinking along the lines of adding something like the following
to javax.persistence.criteria.CriteriaBuilder:
Keyword keyword(String value); // rendered as a String without quotes
or:
Expression<String> literal(String value, encloseInQuotes);
On Tue, Apr 24, 2018 at 7:56 AM, Christian Beikov <
christian.beikov(a)gmail.com> wrote:
> Maybe we should wait until it transitioned to Eclipse then? Or do you
> think it might make sense to start discussions already?
>
> The API could be String based by default but allow to "unwrap" to do
> something provider specific. If the providers model requires it, the
> String could be parsed by the provider.
>
> Imagine an API like the following
>
> interface SQLFunction {
> ExpressionType getType(FunctionContext ctx, List<ExpressionType>
> argumentTypes);
> Expression render(FunctionContext ctx, List<Expression> arguments);
>
> interface FunctionContext {
> ExpressionType getExpressionType(Class<?> javaType);
> Expression getExpression(String expressionString);
> <T> T unwrap(Class<T> clz);
> }
>
> interface ExpressionType {
> Class<?> getJavaType();
> <T> T unwrap(Class<T> clz);
> }
>
> interface Expression {
> String getExpressionString();
> <T> T unwrap(Class<T> clz);
> }
> }
>
> That's just a rough idea of how it could look.
>
> Mit freundlichen Grüßen,
> ------------------------------------------------------------------------
> *Christian Beikov*
> Am 24.04.2018 um 16:33 schrieb Steve Ebersole:
> > JPA is technically under the old JCP still AFAIK. So for now the
> > process would be the same it has always been.
> >
> > I just do not see how this would ever get agreed upon for a
> > standardized contract - it is so very dependent upon how the provider
> > models the query (SQM e.g.) versus the specific mechanism they use to
> > render it (SQL AST).
> >
> >
> > On Tue, Apr 24, 2018 at 9:29 AM Steve Ebersole <steve(a)hibernate.org
> > <mailto:steve@hibernate.org>> wrote:
> >
> > On Tue, Apr 24, 2018 at 8:45 AM Gail Badner <gbadner(a)redhat.com
> > <mailto:gbadner@redhat.com>> wrote:
> >
> > 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?
> >
> >
> > The problem with defining support for this in the spec is that it
> > is relying on Hibernate's "SQL function registry" and its
> > `SQLFunction` contract. I seriously doubt we'd get all the EG
> > members to agree to some standardization of anything like a
> > `SQLFunction` contract.
> >
> > I think proposing to add additional functions to the spec as
> > "built-in" is probably more likely. I can especially see
EXTRACT
> > being likely. Maybe DATEDIFF. Oracle for sure does not support
> > DATEDIFF, but it does support the EXTRACT-from-INTERVAL approach.
> > Anyone know offhand other databases which to not define DATEIDFF?
> >
> > I personally think having DATEDIFF defined as "built-in" is the
> > best option as the provider can always map that to
> > EXTRACT-from-INTERVAL for Oracle, etal - its much harder to do
> > that by mapping EXTRACT-from-INTERVAL to DATEDIFF.
> >
>
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
11:57 p.m.
I just want to make sure that what I'm suggesting is clear.
datediff would be registered as a function:
registerFunction( "datediff",
new StandardSQLFunction( "datediff", StandardBasicTypes.INTEGER )
);
Then, to specify datediff with the keyword, day, as the first parameter
(rendered without quotes):
final Expression<Integer> diff = cb.function("DATEDIFF", Integer.class,
cb.keyword("day"), ... ).as(Integer.class);
On Tue, Apr 24, 2018 at 1:43 PM, Steve Ebersole <steve(a)hibernate.org> wrote:
I'd personally not like that approach. I think specific
registrations
(for extract and datediff) are better options
On Tue, Apr 24, 2018, 1:18 PM Gail Badner <gbadner(a)redhat.com> wrote:
> When I asked about whether JPA should support this in the future, I was
> thinking along the lines of adding something like the following
> to javax.persistence.criteria.CriteriaBuilder:
>
> Keyword keyword(String value); // rendered as a String without quotes
>
> or:
>
> Expression<String> literal(String value, encloseInQuotes);
>
>
> On Tue, Apr 24, 2018 at 7:56 AM, Christian Beikov <
> christian.beikov(a)gmail.com> wrote:
>
> > Maybe we should wait until it transitioned to Eclipse then? Or do you
> > think it might make sense to start discussions already?
> >
> > The API could be String based by default but allow to "unwrap" to do
> > something provider specific. If the providers model requires it, the
> > String could be parsed by the provider.
> >
> > Imagine an API like the following
> >
> > interface SQLFunction {
> > ExpressionType getType(FunctionContext ctx, List<ExpressionType>
> > argumentTypes);
> > Expression render(FunctionContext ctx, List<Expression> arguments);
> >
> > interface FunctionContext {
> > ExpressionType getExpressionType(Class<?> javaType);
> > Expression getExpression(String expressionString);
> > <T> T unwrap(Class<T> clz);
> > }
> >
> > interface ExpressionType {
> > Class<?> getJavaType();
> > <T> T unwrap(Class<T> clz);
> > }
> >
> > interface Expression {
> > String getExpressionString();
> > <T> T unwrap(Class<T> clz);
> > }
> > }
> >
> > That's just a rough idea of how it could look.
> >
> > Mit freundlichen Grüßen,
> > ------------------------------------------------------------
> ------------
> > *Christian Beikov*
> > Am 24.04.2018 um 16:33 schrieb Steve Ebersole:
> > > JPA is technically under the old JCP still AFAIK. So for now the
> > > process would be the same it has always been.
> > >
> > > I just do not see how this would ever get agreed upon for a
> > > standardized contract - it is so very dependent upon how the provider
> > > models the query (SQM e.g.) versus the specific mechanism they use to
> > > render it (SQL AST).
> > >
> > >
> > > On Tue, Apr 24, 2018 at 9:29 AM Steve Ebersole <steve(a)hibernate.org
> > > <mailto:steve@hibernate.org>> wrote:
> > >
> > > On Tue, Apr 24, 2018 at 8:45 AM Gail Badner <gbadner(a)redhat.com
> > > <mailto:gbadner@redhat.com>> wrote:
> > >
> > > 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?
> > >
> > >
> > > The problem with defining support for this in the spec is that it
> > > is relying on Hibernate's "SQL function registry" and
its
> > > `SQLFunction` contract. I seriously doubt we'd get all the EG
> > > members to agree to some standardization of anything like a
> > > `SQLFunction` contract.
> > >
> > > I think proposing to add additional functions to the spec as
> > > "built-in" is probably more likely. I can especially see
EXTRACT
> > > being likely. Maybe DATEDIFF. Oracle for sure does not support
> > > DATEDIFF, but it does support the EXTRACT-from-INTERVAL approach.
> > > Anyone know offhand other databases which to not define DATEIDFF?
> > >
> > > I personally think having DATEDIFF defined as "built-in" is
the
> > > best option as the provider can always map that to
> > > EXTRACT-from-INTERVAL for Oracle, etal - its much harder to do
> > > that by mapping EXTRACT-from-INTERVAL to DATEDIFF.
> > >
> >
> > _______________________________________________
> > hibernate-dev mailing list
> > hibernate-dev(a)lists.jboss.org
> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
> >
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
12:11 a.m.
Yep, it was clear
On Tue, Apr 24, 2018 at 4:57 PM Gail Badner <gbadner(a)redhat.com> wrote:
I just want to make sure that what I'm suggesting is clear.
datediff would be registered as a function:
registerFunction( "datediff",
new StandardSQLFunction( "datediff", StandardBasicTypes.INTEGER )
);
Then, to specify datediff with the keyword, day, as the first parameter
(rendered without quotes):
final Expression<Integer> diff = cb.function("DATEDIFF", Integer.class,
cb.keyword("day"), ... ).as(Integer.class);
On Tue, Apr 24, 2018 at 1:43 PM, Steve Ebersole <steve(a)hibernate.org>
wrote:
> I'd personally not like that approach. I think specific registrations
> (for extract and datediff) are better options
>
>
> On Tue, Apr 24, 2018, 1:18 PM Gail Badner <gbadner(a)redhat.com> wrote:
>
>> When I asked about whether JPA should support this in the future, I was
>> thinking along the lines of adding something like the following
>> to javax.persistence.criteria.CriteriaBuilder:
>>
>> Keyword keyword(String value); // rendered as a String without quotes
>>
>> or:
>>
>> Expression<String> literal(String value, encloseInQuotes);
>>
>>
>> On Tue, Apr 24, 2018 at 7:56 AM, Christian Beikov <
>> christian.beikov(a)gmail.com> wrote:
>>
>> > Maybe we should wait until it transitioned to Eclipse then? Or do you
>> > think it might make sense to start discussions already?
>> >
>> > The API could be String based by default but allow to "unwrap" to
do
>> > something provider specific. If the providers model requires it, the
>> > String could be parsed by the provider.
>> >
>> > Imagine an API like the following
>> >
>> > interface SQLFunction {
>> > ExpressionType getType(FunctionContext ctx, List<ExpressionType>
>> > argumentTypes);
>> > Expression render(FunctionContext ctx, List<Expression>
arguments);
>> >
>> > interface FunctionContext {
>> > ExpressionType getExpressionType(Class<?> javaType);
>> > Expression getExpression(String expressionString);
>> > <T> T unwrap(Class<T> clz);
>> > }
>> >
>> > interface ExpressionType {
>> > Class<?> getJavaType();
>> > <T> T unwrap(Class<T> clz);
>> > }
>> >
>> > interface Expression {
>> > String getExpressionString();
>> > <T> T unwrap(Class<T> clz);
>> > }
>> > }
>> >
>> > That's just a rough idea of how it could look.
>> >
>> > Mit freundlichen Grüßen,
>> >
>> ------------------------------------------------------------------------
>> > *Christian Beikov*
>> > Am 24.04.2018 um 16:33 schrieb Steve Ebersole:
>> > > JPA is technically under the old JCP still AFAIK. So for now the
>> > > process would be the same it has always been.
>> > >
>> > > I just do not see how this would ever get agreed upon for a
>> > > standardized contract - it is so very dependent upon how the provider
>> > > models the query (SQM e.g.) versus the specific mechanism they use to
>> > > render it (SQL AST).
>> > >
>> > >
>> > > On Tue, Apr 24, 2018 at 9:29 AM Steve Ebersole <steve(a)hibernate.org
>> > > <mailto:steve@hibernate.org>> wrote:
>> > >
>> > > On Tue, Apr 24, 2018 at 8:45 AM Gail Badner <gbadner(a)redhat.com
>> > > <mailto:gbadner@redhat.com>> wrote:
>> > >
>> > > 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?
>> > >
>> > >
>> > > The problem with defining support for this in the spec is that it
>> > > is relying on Hibernate's "SQL function registry" and
its
>> > > `SQLFunction` contract. I seriously doubt we'd get all the EG
>> > > members to agree to some standardization of anything like a
>> > > `SQLFunction` contract.
>> > >
>> > > I think proposing to add additional functions to the spec as
>> > > "built-in" is probably more likely. I can especially see
EXTRACT
>> > > being likely. Maybe DATEDIFF. Oracle for sure does not support
>> > > DATEDIFF, but it does support the EXTRACT-from-INTERVAL approach.
>> > > Anyone know offhand other databases which to not define
>> DATEIDFF?
>> > >
>> > > I personally think having DATEDIFF defined as "built-in"
is the
>> > > best option as the provider can always map that to
>> > > EXTRACT-from-INTERVAL for Oracle, etal - its much harder to do
>> > > that by mapping EXTRACT-from-INTERVAL to DATEDIFF.
>> > >
>> >
>> > _______________________________________________
>> > hibernate-dev mailing list
>> > hibernate-dev(a)lists.jboss.org
>> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
>> >
>> _______________________________________________
>> hibernate-dev mailing list
>> hibernate-dev(a)lists.jboss.org
>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
>
12:20 a.m.
I don't like this either. It simply feels wrong as this is a "provider
specific" keyword i.e. some DBMS might not even support DATEDIFF. IMO
when we decide to abstract over the DBMS specifics by introducing a HQL
DATEDIFF or specifying a JPQL DATEDIFF, we could just as well introduce
separate functions which would ultimately look a lot cleaner.
Mit freundlichen Grüßen,
------------------------------------------------------------------------
*Christian Beikov*
Am 25.04.2018 um 00:11 schrieb Steve Ebersole:
Yep, it was clear
On Tue, Apr 24, 2018 at 4:57 PM Gail Badner <gbadner(a)redhat.com
<mailto:gbadner@redhat.com>> wrote:
I just want to make sure that what I'm suggesting is clear.
datediff would be registered as a function:
registerFunction("datediff",
new StandardSQLFunction("datediff", StandardBasicTypes.INTEGER )
);
Then, to specify datediff with the keyword, day, as the first
parameter (rendered without quotes):
final Expression<Integer> diff = cb.function("DATEDIFF",
Integer.class, cb.keyword("day"), ... ).as(Integer.class);
On Tue, Apr 24, 2018 at 1:43 PM, Steve Ebersole
<steve(a)hibernate.org <mailto:steve@hibernate.org>> wrote:
I'd personally not like that approach. I think specific
registrations (for extract and datediff) are better options
On Tue, Apr 24, 2018, 1:18 PM Gail Badner <gbadner(a)redhat.com
<mailto:gbadner@redhat.com>> wrote:
When I asked about whether JPA should support this in the
future, I was
thinking along the lines of adding something like the
following
to javax.persistence.criteria.CriteriaBuilder:
Keyword keyword(String value); // rendered as a String
without quotes
or:
Expression<String> literal(String value, encloseInQuotes);
On Tue, Apr 24, 2018 at 7:56 AM, Christian Beikov <
christian.beikov(a)gmail.com
<mailto:christian.beikov@gmail.com>> wrote:
> Maybe we should wait until it transitioned to Eclipse
then? Or do you
> think it might make sense to start discussions already?
>
> The API could be String based by default but allow to
"unwrap" to do
> something provider specific. If the providers model
requires it, the
> String could be parsed by the provider.
>
> Imagine an API like the following
>
> interface SQLFunction {
> ExpressionType getType(FunctionContext ctx,
List<ExpressionType>
> argumentTypes);
> Expression render(FunctionContext ctx,
List<Expression> arguments);
>
> interface FunctionContext {
> ExpressionType getExpressionType(Class<?> javaType);
> Expression getExpression(String expressionString);
> <T> T unwrap(Class<T> clz);
> }
>
> interface ExpressionType {
> Class<?> getJavaType();
> <T> T unwrap(Class<T> clz);
> }
>
> interface Expression {
> String getExpressionString();
> <T> T unwrap(Class<T> clz);
> }
> }
>
> That's just a rough idea of how it could look.
>
> Mit freundlichen Grüßen,
>
------------------------------------------------------------------------
> *Christian Beikov*
> Am 24.04.2018 um 16:33 schrieb Steve Ebersole:
> > JPA is technically under the old JCP still AFAIK. So
for now the
> > process would be the same it has always been.
> >
> > I just do not see how this would ever get agreed upon
for a
> > standardized contract - it is so very dependent upon
how the provider
> > models the query (SQM e.g.) versus the specific
mechanism they use to
> > render it (SQL AST).
> >
> >
> > On Tue, Apr 24, 2018 at 9:29 AM Steve Ebersole
<steve(a)hibernate.org <mailto:steve@hibernate.org>
> > <mailto:steve@hibernate.org
<mailto:steve@hibernate.org>>> wrote:
> >
> > On Tue, Apr 24, 2018 at 8:45 AM Gail Badner
<gbadner(a)redhat.com <mailto:gbadner@redhat.com>
> > <mailto:gbadner@redhat.com
<mailto:gbadner@redhat.com>>> wrote:
> >
> > 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?
> >
> >
> > The problem with defining support for this in the
spec is that it
> > is relying on Hibernate's "SQL function
registry"
and its
> > `SQLFunction` contract. I seriously doubt we'd
get all the EG
> > members to agree to some standardization of
anything like a
> > `SQLFunction` contract.
> >
> > I think proposing to add additional functions to
the spec as
> > "built-in" is probably more likely. I can
especially see EXTRACT
> > being likely. Maybe DATEDIFF. Oracle for sure
does not support
> > DATEDIFF, but it does support the
EXTRACT-from-INTERVAL approach.
> > Anyone know offhand other databases which to not
define DATEIDFF?
> >
> > I personally think having DATEDIFF defined as
"built-in" is the
> > best option as the provider can always map that to
> > EXTRACT-from-INTERVAL for Oracle, etal - its much
harder to do
> > that by mapping EXTRACT-from-INTERVAL to DATEDIFF.
> >
>
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev(a)lists.jboss.org
<mailto:hibernate-dev@lists.jboss.org>
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
<mailto:hibernate-dev@lists.jboss.org>
https://lists.jboss.org/mailman/listinfo/hibernate-dev
12:25 a.m.
What I was proposing instead was to treat extract and hopefully datediff as
first-class citizens:
cb.extract( DateField.DAY, startDate ).as( Integer.class );
cb.extract( DateField.DAY, endDate - startDate ).as( Integer.class );
cb.datediff( DateField.DAY, endDate, startDate ).as( Integer.class );
s.createQuery( "... where extract( DAY, startDate ) ..." );
s.createQuery( "... where extract( DAY, endDate - startDate ) ..." );
s.createQuery( "... where datediff( DAY, endDate, startDate ) ..." );
On Tue, Apr 24, 2018 at 5:11 PM Steve Ebersole <steve(a)hibernate.org> wrote:
Yep, it was clear
On Tue, Apr 24, 2018 at 4:57 PM Gail Badner <gbadner(a)redhat.com> wrote:
> I just want to make sure that what I'm suggesting is clear.
>
> datediff would be registered as a function:
>
> registerFunction( "datediff",
> new StandardSQLFunction( "datediff", StandardBasicTypes.INTEGER )
> );
>
> Then, to specify datediff with the keyword, day, as the first parameter
> (rendered without quotes):
>
> final Expression<Integer> diff = cb.function("DATEDIFF",
Integer.class,
> cb.keyword("day"), ... ).as(Integer.class);
>
>
> On Tue, Apr 24, 2018 at 1:43 PM, Steve Ebersole <steve(a)hibernate.org>
> wrote:
>
>> I'd personally not like that approach. I think specific registrations
>> (for extract and datediff) are better options
>>
>>
>> On Tue, Apr 24, 2018, 1:18 PM Gail Badner <gbadner(a)redhat.com> wrote:
>>
>>> When I asked about whether JPA should support this in the future, I was
>>> thinking along the lines of adding something like the following
>>> to javax.persistence.criteria.CriteriaBuilder:
>>>
>>> Keyword keyword(String value); // rendered as a String without quotes
>>>
>>> or:
>>>
>>> Expression<String> literal(String value, encloseInQuotes);
>>>
>>>
>>> On Tue, Apr 24, 2018 at 7:56 AM, Christian Beikov <
>>> christian.beikov(a)gmail.com> wrote:
>>>
>>> > Maybe we should wait until it transitioned to Eclipse then? Or do you
>>> > think it might make sense to start discussions already?
>>> >
>>> > The API could be String based by default but allow to "unwrap"
to do
>>> > something provider specific. If the providers model requires it, the
>>> > String could be parsed by the provider.
>>> >
>>> > Imagine an API like the following
>>> >
>>> > interface SQLFunction {
>>> > ExpressionType getType(FunctionContext ctx,
List<ExpressionType>
>>> > argumentTypes);
>>> > Expression render(FunctionContext ctx, List<Expression>
arguments);
>>> >
>>> > interface FunctionContext {
>>> > ExpressionType getExpressionType(Class<?> javaType);
>>> > Expression getExpression(String expressionString);
>>> > <T> T unwrap(Class<T> clz);
>>> > }
>>> >
>>> > interface ExpressionType {
>>> > Class<?> getJavaType();
>>> > <T> T unwrap(Class<T> clz);
>>> > }
>>> >
>>> > interface Expression {
>>> > String getExpressionString();
>>> > <T> T unwrap(Class<T> clz);
>>> > }
>>> > }
>>> >
>>> > That's just a rough idea of how it could look.
>>> >
>>> > Mit freundlichen Grüßen,
>>> >
>>> ------------------------------------------------------------------------
>>> > *Christian Beikov*
>>> > Am 24.04.2018 um 16:33 schrieb Steve Ebersole:
>>> > > JPA is technically under the old JCP still AFAIK. So for now the
>>> > > process would be the same it has always been.
>>> > >
>>> > > I just do not see how this would ever get agreed upon for a
>>> > > standardized contract - it is so very dependent upon how the
>>> provider
>>> > > models the query (SQM e.g.) versus the specific mechanism they use
>>> to
>>> > > render it (SQL AST).
>>> > >
>>> > >
>>> > > On Tue, Apr 24, 2018 at 9:29 AM Steve Ebersole
<steve(a)hibernate.org
>>> > > <mailto:steve@hibernate.org>> wrote:
>>> > >
>>> > > On Tue, Apr 24, 2018 at 8:45 AM Gail Badner
<gbadner(a)redhat.com
>>> > > <mailto:gbadner@redhat.com>> wrote:
>>> > >
>>> > > 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?
>>> > >
>>> > >
>>> > > The problem with defining support for this in the spec is that
>>> it
>>> > > is relying on Hibernate's "SQL function registry"
and its
>>> > > `SQLFunction` contract. I seriously doubt we'd get all the
EG
>>> > > members to agree to some standardization of anything like a
>>> > > `SQLFunction` contract.
>>> > >
>>> > > I think proposing to add additional functions to the spec as
>>> > > "built-in" is probably more likely. I can especially
see
>>> EXTRACT
>>> > > being likely. Maybe DATEDIFF. Oracle for sure does not
support
>>> > > DATEDIFF, but it does support the EXTRACT-from-INTERVAL
>>> approach.
>>> > > Anyone know offhand other databases which to not define
>>> DATEIDFF?
>>> > >
>>> > > I personally think having DATEDIFF defined as
"built-in" is the
>>> > > best option as the provider can always map that to
>>> > > EXTRACT-from-INTERVAL for Oracle, etal - its much harder to do
>>> > > that by mapping EXTRACT-from-INTERVAL to DATEDIFF.
>>> > >
>>> >
>>> > _______________________________________________
>>> > hibernate-dev mailing list
>>> > hibernate-dev(a)lists.jboss.org
>>> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>> >
>>> _______________________________________________
>>> hibernate-dev mailing list
>>> hibernate-dev(a)lists.jboss.org
>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>
>>
>
12:39 a.m.
That's a possibility indeed, but there will most likely always be some
nice function that uses a weird keyword syntax for which there is no
first class support.
So even if we propose this, IMO we should still also propose to add a
note to the function invocation syntax section, that a function may
resolve to a JPA vendor defined funciton as well. This would be like a
"last resort" to use a function at least in a "standard way". If the
JPA
vendor doesn't define the desired function, it's up to the user to
making use of JPA vendor specific APIs for registering the function.
Mit freundlichen Grüßen,
------------------------------------------------------------------------
*Christian Beikov*
Am 25.04.2018 um 00:25 schrieb Steve Ebersole:
What I was proposing instead was to treat extract and hopefully
datediff as first-class citizens:
cb.extract( DateField.DAY, startDate ).as( Integer.class );
cb.extract( DateField.DAY, endDate - startDate ).as( Integer.class );
cb.datediff( DateField.DAY, endDate, startDate ).as( Integer.class );
s.createQuery( "... where extract( DAY, startDate ) ..." );
s.createQuery( "... where extract( DAY, endDate - startDate ) ..." );
s.createQuery( "... where datediff( DAY, endDate, startDate ) ..." );
On Tue, Apr 24, 2018 at 5:11 PM Steve Ebersole <steve(a)hibernate.org
<mailto:steve@hibernate.org>> wrote:
Yep, it was clear
On Tue, Apr 24, 2018 at 4:57 PM Gail Badner <gbadner(a)redhat.com
<mailto:gbadner@redhat.com>> wrote:
I just want to make sure that what I'm suggesting is clear.
datediff would be registered as a function:
registerFunction("datediff",
new StandardSQLFunction("datediff", StandardBasicTypes.INTEGER )
);
Then, to specify datediff with the keyword, day, as the first
parameter (rendered without quotes):
final Expression<Integer> diff = cb.function("DATEDIFF",
Integer.class, cb.keyword("day"), ... ).as(Integer.class);
On Tue, Apr 24, 2018 at 1:43 PM, Steve Ebersole
<steve(a)hibernate.org <mailto:steve@hibernate.org>> wrote:
I'd personally not like that approach. I think specific
registrations (for extract and datediff) are better options
On Tue, Apr 24, 2018, 1:18 PM Gail Badner
<gbadner(a)redhat.com <mailto:gbadner@redhat.com>> wrote:
When I asked about whether JPA should support this in
the future, I was
thinking along the lines of adding something like the
following
to javax.persistence.criteria.CriteriaBuilder:
Keyword keyword(String value); // rendered as a
String without quotes
or:
Expression<String> literal(String value, encloseInQuotes);
On Tue, Apr 24, 2018 at 7:56 AM, Christian Beikov <
christian.beikov(a)gmail.com
<mailto:christian.beikov@gmail.com>> wrote:
> Maybe we should wait until it transitioned to
Eclipse then? Or do you
> think it might make sense to start discussions already?
>
> The API could be String based by default but allow
to "unwrap" to do
> something provider specific. If the providers model
requires it, the
> String could be parsed by the provider.
>
> Imagine an API like the following
>
> interface SQLFunction {
> ExpressionType getType(FunctionContext ctx,
List<ExpressionType>
> argumentTypes);
> Expression render(FunctionContext ctx,
List<Expression> arguments);
>
> interface FunctionContext {
> ExpressionType getExpressionType(Class<?>
javaType);
> Expression getExpression(String expressionString);
> <T> T unwrap(Class<T> clz);
> }
>
> interface ExpressionType {
> Class<?> getJavaType();
> <T> T unwrap(Class<T> clz);
> }
>
> interface Expression {
> String getExpressionString();
> <T> T unwrap(Class<T> clz);
> }
> }
>
> That's just a rough idea of how it could look.
>
> Mit freundlichen Grüßen,
>
------------------------------------------------------------------------
> *Christian Beikov*
> Am 24.04.2018 um 16:33 schrieb Steve Ebersole:
> > JPA is technically under the old JCP still AFAIK.
So for now the
> > process would be the same it has always been.
> >
> > I just do not see how this would ever get agreed
upon for a
> > standardized contract - it is so very dependent
upon how the provider
> > models the query (SQM e.g.) versus the specific
mechanism they use to
> > render it (SQL AST).
> >
> >
> > On Tue, Apr 24, 2018 at 9:29 AM Steve Ebersole
<steve(a)hibernate.org <mailto:steve@hibernate.org>
> > <mailto:steve@hibernate.org
<mailto:steve@hibernate.org>>> wrote:
> >
> > On Tue, Apr 24, 2018 at 8:45 AM Gail Badner
<gbadner(a)redhat.com <mailto:gbadner@redhat.com>
> > <mailto:gbadner@redhat.com
<mailto:gbadner@redhat.com>>> wrote:
> >
> > 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?
> >
> >
> > The problem with defining support for this in
the spec is that it
> > is relying on Hibernate's "SQL function
registry" and its
> > `SQLFunction` contract. I seriously doubt
we'd get all the EG
> > members to agree to some standardization of
anything like a
> > `SQLFunction` contract.
> >
> > I think proposing to add additional functions
to the spec as
> > "built-in" is probably more likely. I can
especially see EXTRACT
> > being likely. Maybe DATEDIFF. Oracle for sure
does not support
> > DATEDIFF, but it does support the
EXTRACT-from-INTERVAL approach.
> > Anyone know offhand other databases which to
not define DATEIDFF?
> >
> > I personally think having DATEDIFF defined as
"built-in" is the
> > best option as the provider can always map that to
> > EXTRACT-from-INTERVAL for Oracle, etal - its
much harder to do
> > that by mapping EXTRACT-from-INTERVAL to DATEDIFF.
> >
>
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev(a)lists.jboss.org
<mailto:hibernate-dev@lists.jboss.org>
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
<mailto:hibernate-dev@lists.jboss.org>
https://lists.jboss.org/mailman/listinfo/hibernate-dev
12:46 a.m.
On Tue, Apr 24, 2018 at 5:43 PM Christian Beikov <christian.beikov(a)gmail.com>
wrote:
That's a possibility indeed, but there will most likely always be
some
nice function that uses a weird keyword syntax for which there is no
first class support.
And not only that but some databases allow extensions to the SQL spec as to
what can be extracted. So using an enum is nice, but limiting
So even if we propose this, IMO we should still also propose to add
a
note to the function invocation syntax section, that a function may
resolve to a JPA vendor defined funciton as well. This would be like a
"last resort" to use a function at least in a "standard way". If the
JPA
vendor doesn't define the desired function, it's up to the user to
making use of JPA vendor specific APIs for registering the function.
I'm not sure what this means
8:51 a.m.
Am 25.04.2018 um 00:46 schrieb Steve Ebersole:
On Tue, Apr 24, 2018 at 5:43 PM Christian Beikov
<christian.beikov(a)gmail.com <mailto:christian.beikov@gmail.com>> wrote:
That's a possibility indeed, but there will most likely always be
some
nice function that uses a weird keyword syntax for which there is no
first class support.
And not only that but some databases allow extensions to the SQL spec
as to what can be extracted. So using an enum is nice, but limiting
So even if we propose this, IMO we should still also propose to add a
note to the function invocation syntax section, that a function may
resolve to a JPA vendor defined funciton as well. This would be
like a
"last resort" to use a function at least in a "standard way". If
the JPA
vendor doesn't define the desired function, it's up to the user to
making use of JPA vendor specific APIs for registering the function.
I'm not sure what this means
That means that section 4.6.17.3 should be changed
to allow for
FUNCTION('XYZ', arg1, ...) to resolve to a JPA vendor specific function
like the ones registered in a Hibernate Dialect. To me it seems that
currently the wording requires to pass this through to the SQL directly
as XYZ(arg1). This would allow the use of user defined SQLFunctions
through the JPA Criteria API.
3:07 p.m.
We can propose it. Personally I do not think that section explicitly
precludes what we do, but I agree that it does imply the function
invocation should be "passed through". I think this might be difficult to
get consensus on though - just a quick look at EclipseLink and TopLink show
that they define a completely different mechanism for such "provider
treated" function calls (OPERATION rather than FUNCTION), so I doubt there
will be much support for changing the wording here. We'll see...
I think we should standardize a number of HQL functions beyond the JPA ones
across Dialects (we have Dialect coverage for many of these already):
- Date / Time (already standardized across Dialects)
- year
- month
- day
- hour
- minute
- second
- Date / Time (can be done through datetime arithmetic)
- datediff
- dateadd
- Convert / Cast
- to_char
- to_number
- to_date
- Trigonometry
- cos
- sin
- tan
- ...
As for other cases like "date field" where we need to be able to refer to
SQL-y things, we should add some kind of support for that generically imo.
This is something I've wanted to do for some time - I just don't know a
good syntax yet. I had thought to use braces (e.g. `extract( {day} from
startDate)`) but we use braces already for a few other things. We could
take a page from JDBC (escape syntax) and have `extract( {sql day} from
startDate)` - that makes it more syntactically understandable.
On Wed, Apr 25, 2018 at 1:51 AM Christian Beikov <christian.beikov(a)gmail.com>
wrote:
Am 25.04.2018 um 00:46 schrieb Steve Ebersole:
>
>
> On Tue, Apr 24, 2018 at 5:43 PM Christian Beikov
> <christian.beikov(a)gmail.com <mailto:christian.beikov@gmail.com>> wrote:
>
> That's a possibility indeed, but there will most likely always be
> some
> nice function that uses a weird keyword syntax for which there is no
> first class support.
>
>
> And not only that but some databases allow extensions to the SQL spec
> as to what can be extracted. So using an enum is nice, but limiting
>
> So even if we propose this, IMO we should still also propose to add a
> note to the function invocation syntax section, that a function may
> resolve to a JPA vendor defined funciton as well. This would be
> like a
> "last resort" to use a function at least in a "standard
way". If
> the JPA
> vendor doesn't define the desired function, it's up to the user to
> making use of JPA vendor specific APIs for registering the function.
>
>
> I'm not sure what this means
That means that section 4.6.17.3 should be changed to allow for
FUNCTION('XYZ', arg1, ...) to resolve to a JPA vendor specific function
like the ones registered in a Hibernate Dialect. To me it seems that
currently the wording requires to pass this through to the SQL directly
as XYZ(arg1). This would allow the use of user defined SQLFunctions
through the JPA Criteria API.
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
2193
days inactive
2194
days old
19 comments
3 participants
participants (3)
-
Christian Beikov -
Gail Badner -
Steve Ebersole