You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Christophe Marcourt <ch...@marcourt.ch> on 2014/05/14 09:50:59 UTC
JPQL and DB2 native code
Hello,
I'm using a DB2 native functionality for the historic.
As the query are proprietary : select * from <table_name> *for system_time
between* <from_date> *and *<to_date> where <where_clause>... I had to use
pure SQL.
I'm having a problem as JPQL parser can't parse this query. The "for
system_time between ... and ..." is not known by JQPL.
Is there a way to override, intercept or listen this parsing ?
What is the object to use DBDictionary, JPQLParser, else ?
Thank you for your help.
Christophe Marcourt
Re: JPQL and DB2 native code
Posted by Pinaki Poddar <pp...@apache.org>.
If you are interested to invoke database specific functions in query via JPA,
one compliant means is to use Criteria Query. Via Criteria query interface,
you can build a function of any name which may take zero or more arguments.
The interface and strict type structure of the API is somewhat intimidating
than a pure and simple JPQL query string -- but it will makes some queries
possible that are not expressible in JPQL.
Here is an old article on Criteria Query on IBM developerworks [1] with an
example of function() near the end.
Regards --
[1] http://www.ibm.com/developerworks/library/j-typesafejpa/
-----
Pinaki Poddar
Chair, Apache OpenJPA Project
--
View this message in context: http://openjpa.208410.n2.nabble.com/JPQL-and-DB2-native-code-tp7586405p7587022.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: JPQL and DB2 native code
Posted by Guilherme Ferreira <gu...@hotmail.com>.
Christophe Marcourt <ch...@...> writes:
>
> Hi Kevin,
>
> yes I'm using temporal data from DB2 .
> I'm already querying DB2 in SQL native and I had to do a "mapper" for my
> entities as my DAO is a generic one. Quite tough. My problem is the
> <at> Inheritance annotation for the joined strategy. In JPA it's
automatic but
> in pure SQL : nada !
>
> So that's why I was asking for a way to intercept JPQL parsing. But it
> seems it's not possible.
>
> I'm afraid I have to do it on my own.
>
> Cheers.
>
> Christophe Marcourt
>
Hi Christophe,
Have you been able to figure out a way of using DB2 temporal queries with
JPA? I've been facing a similar issue, where I've also been able to issue
pure, native SQL queries containing the temporal capabilities, but can't
figure out a way of using JPQL with temporal.
One of the things I''m trying now is to use the "set special register
command", which in some cases might work well, but so far no luck either.
Best Regards,
Guilherme Ferreira
Re: JPQL and DB2 native code
Posted by Christophe Marcourt <ch...@marcourt.ch>.
Hi Kevin,
yes I'm using temporal data from DB2 :-).
I'm already querying DB2 in SQL native and I had to do a "mapper" for my
entities as my DAO is a generic one. Quite tough. My problem is the
@Inheritance annotation for the joined strategy. In JPA it's automatic but
in pure SQL : nada !
So that's why I was asking for a way to intercept JPQL parsing. But it
seems it's not possible.
I'm afraid I have to do it on my own.
Cheers.
Christophe Marcourt
2014-05-16 21:22 GMT+02:00 Kevin Sutter <kw...@gmail.com>:
> Hi Christophe,
> So, you are attempting to use the Temporal support for DB2? That's pretty
> cool. We had entertained that support a some time ago, but updating the
> JPQL is a killer. Once we update the JPQL then we've broken the standard
> as defined by the specification. So, integrating the Temporal support
> directly into the Java object model and the associated JPQL was
> deep-sixed... At least until this becomes back of the JDBC/SQL standard.
>
> So, in the mean time, I would suggest using native SQL instead of
> attempting to use JPQL. When using native SQL, OpenJPA just puts on the
> blinders and passes the SQL through to the backend database. The OpenJPA
> manual [1] touches on this native SQL support. But, you may also want to
> reference the specification [2] and/or the Pro JPA 2 book [3] for more
> information.
>
> Good luck,
> Kevin
>
> [1]
>
> http://ci.apache.org/projects/openjpa/trunk/docbook/manual.html#jpa_overview_sqlquery
> [2] https://jcp.org/aboutJava/communityprocess/final/jsr317/
> [3] http://www.amazon.com/Pro-JPA-2-Mike-Keith/dp/1430249269
>
>
> On Wed, May 14, 2014 at 2:50 AM, Christophe Marcourt <
> christophe@marcourt.ch
> > wrote:
>
> > Hello,
> >
> > I'm using a DB2 native functionality for the historic.
> > As the query are proprietary : select * from <table_name> *for
> system_time
> > between* <from_date> *and *<to_date> where <where_clause>... I had to use
> > pure SQL.
> >
> > I'm having a problem as JPQL parser can't parse this query. The "for
> > system_time between ... and ..." is not known by JQPL.
> >
> > Is there a way to override, intercept or listen this parsing ?
> >
> > What is the object to use DBDictionary, JPQLParser, else ?
> >
> > Thank you for your help.
> >
> > Christophe Marcourt
> >
>
Re: JPQL and DB2 native code
Posted by Kevin Sutter <kw...@gmail.com>.
Hi Christophe,
So, you are attempting to use the Temporal support for DB2? That's pretty
cool. We had entertained that support a some time ago, but updating the
JPQL is a killer. Once we update the JPQL then we've broken the standard
as defined by the specification. So, integrating the Temporal support
directly into the Java object model and the associated JPQL was
deep-sixed... At least until this becomes back of the JDBC/SQL standard.
So, in the mean time, I would suggest using native SQL instead of
attempting to use JPQL. When using native SQL, OpenJPA just puts on the
blinders and passes the SQL through to the backend database. The OpenJPA
manual [1] touches on this native SQL support. But, you may also want to
reference the specification [2] and/or the Pro JPA 2 book [3] for more
information.
Good luck,
Kevin
[1]
http://ci.apache.org/projects/openjpa/trunk/docbook/manual.html#jpa_overview_sqlquery
[2] https://jcp.org/aboutJava/communityprocess/final/jsr317/
[3] http://www.amazon.com/Pro-JPA-2-Mike-Keith/dp/1430249269
On Wed, May 14, 2014 at 2:50 AM, Christophe Marcourt <christophe@marcourt.ch
> wrote:
> Hello,
>
> I'm using a DB2 native functionality for the historic.
> As the query are proprietary : select * from <table_name> *for system_time
> between* <from_date> *and *<to_date> where <where_clause>... I had to use
> pure SQL.
>
> I'm having a problem as JPQL parser can't parse this query. The "for
> system_time between ... and ..." is not known by JQPL.
>
> Is there a way to override, intercept or listen this parsing ?
>
> What is the object to use DBDictionary, JPQLParser, else ?
>
> Thank you for your help.
>
> Christophe Marcourt
>