You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by Michael Dick <mi...@gmail.com> on 2011/11/01 02:07:23 UTC
Re: Positional Parameters with update query
Hi Prashant,
This sounds like
OPENJPA-1999<https://issues.apache.org/jira/browse/OPENJPA-1999>.
Can you try adding the compatibility option to enable non sequential
parameters?
Here's the relevant property for persistence.xml :
<property name="openjpa.Compatibility"
value="ConvertPositionalParametersToNamed=true"/>
-mike
On Sun, Oct 30, 2011 at 7:32 AM, Prashant Bhat <pr...@gmail.com>wrote:
> Hi,
>
> When positional parameters are used with IN (select ...) query as in the
> below test case, OpenJpa throws an error that "JPQL positional parameters
> must start at 1". The same query with named parameters works.
>
>
> openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/query/TestQueryParameterBinding.java
>
> public void testPositionalParametersWithUpdateQuery() {
> String JPQL_POSITIONAL = "UPDATE Binder p SET p.p3=?1 WHERE p.p1
> IN "
> + " (SELECT x.p1 FROM Binder x WHERE x.p1=?2) AND p.p2=?3";
> Query q = em.createQuery(JPQL_POSITIONAL);
> q.setParameter(1, DBL_VALUE);
> q.setParameter(2, INT_VALUE);
> q.setParameter(3, STR_VALUE);
>
> em.getTransaction().begin();
> int result = q.executeUpdate();
> em.getTransaction().commit();
> assertEquals(1, result);
> }
>
> <openjpa-2.2.0-SNAPSHOT-r422266:1195104M nonfatal user error>
> org.apache.openjpa.persistence.ArgumentException: Query "UPDATE Binder p
> SET p.p3 = ?1 WHERE p.p1 in (SELECT x.p1 FROM Binder x WHERE x.p1=?2) AND
> p.p2=?3" did not contain positional parameter 1. JPQL positional parameters
> must start at 1. Detected parameters "[2]".
>
> In our application I was able to workaround this error, by adding ?1 = ?1
> to the where clause! So it's is not a critical issue, but would be useful
> improvement. Should I create a Jira Issue for this?
>
> Regards,
> Prashant
>
Re: Positional Parameters with update query
Posted by Kevin Sutter <kw...@gmail.com>.
Hi Prashant,
Mike can correct me if I'm wrong, but I believe the non-sequential
positional parameter detection is due to the sub-select. Since your query
has a sub-select "(SELECT x.p1 FROM Binder x WHERE x.p1=?2)", the
processing for the parameters starts over as if it's a new Select. Since
only positional parameter 2 was found, the message was displayed. Not
intuitive, I agree. But, I believe that's the explanation for the
processing. :-)
Kevin
On Tue, Nov 1, 2011 at 9:53 AM, Prashant Bhat <pr...@gmail.com> wrote:
> Hi Mike,
>
> The query works with this compatibility option. The issue
> OPENJPA-1999<https://issues.apache.org/jira/browse/OPENJPA-1999> is
> for non-sequential positional parameters, but in this query, parameters
> are specified in correct sequence. So IMO this should be enabled by
> default.
>
> I've set this option in our application now and tested all other queries to
> be working also. Thanks.
>
> Regards,
> Prashant
>
> On Tue, Nov 1, 2011 at 9:07 AM, Michael Dick <michael.d.dick@gmail.com
> >wrote:
>
> > Hi Prashant,
> >
> > This sounds like
> > OPENJPA-1999<https://issues.apache.org/jira/browse/OPENJPA-1999>.
> > Can you try adding the compatibility option to enable non sequential
> > parameters?
> >
> > Here's the relevant property for persistence.xml :
> > <property name="openjpa.Compatibility"
> > value="ConvertPositionalParametersToNamed=true"/>
> >
> > -mike
> >
> > On Sun, Oct 30, 2011 at 7:32 AM, Prashant Bhat <prashant.mr@gmail.com
> > >wrote:
> >
> > > Hi,
> > >
> > > When positional parameters are used with IN (select ...) query as in
> the
> > > below test case, OpenJpa throws an error that "JPQL positional
> parameters
> > > must start at 1". The same query with named parameters works.
> > >
> > >
> > >
> >
> openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/query/TestQueryParameterBinding.java
> > >
> > > public void testPositionalParametersWithUpdateQuery() {
> > > String JPQL_POSITIONAL = "UPDATE Binder p SET p.p3=?1 WHERE
> p.p1
> > > IN "
> > > + " (SELECT x.p1 FROM Binder x WHERE x.p1=?2) AND p.p2=?3";
> > > Query q = em.createQuery(JPQL_POSITIONAL);
> > > q.setParameter(1, DBL_VALUE);
> > > q.setParameter(2, INT_VALUE);
> > > q.setParameter(3, STR_VALUE);
> > >
> > > em.getTransaction().begin();
> > > int result = q.executeUpdate();
> > > em.getTransaction().commit();
> > > assertEquals(1, result);
> > > }
> > >
> > > <openjpa-2.2.0-SNAPSHOT-r422266:1195104M nonfatal user error>
> > > org.apache.openjpa.persistence.ArgumentException: Query "UPDATE Binder
> p
> > > SET p.p3 = ?1 WHERE p.p1 in (SELECT x.p1 FROM Binder x WHERE x.p1=?2)
> AND
> > > p.p2=?3" did not contain positional parameter 1. JPQL positional
> > parameters
> > > must start at 1. Detected parameters "[2]".
> > >
> > > In our application I was able to workaround this error, by adding ?1 =
> ?1
> > > to the where clause! So it's is not a critical issue, but would be
> useful
> > > improvement. Should I create a Jira Issue for this?
> > >
> > > Regards,
> > > Prashant
> > >
> >
>
Re: Positional Parameters with update query
Posted by Prashant Bhat <pr...@gmail.com>.
Hi Mike,
The query works with this compatibility option. The issue
OPENJPA-1999<https://issues.apache.org/jira/browse/OPENJPA-1999> is
for non-sequential positional parameters, but in this query, parameters
are specified in correct sequence. So IMO this should be enabled by default.
I've set this option in our application now and tested all other queries to
be working also. Thanks.
Regards,
Prashant
On Tue, Nov 1, 2011 at 9:07 AM, Michael Dick <mi...@gmail.com>wrote:
> Hi Prashant,
>
> This sounds like
> OPENJPA-1999<https://issues.apache.org/jira/browse/OPENJPA-1999>.
> Can you try adding the compatibility option to enable non sequential
> parameters?
>
> Here's the relevant property for persistence.xml :
> <property name="openjpa.Compatibility"
> value="ConvertPositionalParametersToNamed=true"/>
>
> -mike
>
> On Sun, Oct 30, 2011 at 7:32 AM, Prashant Bhat <prashant.mr@gmail.com
> >wrote:
>
> > Hi,
> >
> > When positional parameters are used with IN (select ...) query as in the
> > below test case, OpenJpa throws an error that "JPQL positional parameters
> > must start at 1". The same query with named parameters works.
> >
> >
> >
> openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/query/TestQueryParameterBinding.java
> >
> > public void testPositionalParametersWithUpdateQuery() {
> > String JPQL_POSITIONAL = "UPDATE Binder p SET p.p3=?1 WHERE p.p1
> > IN "
> > + " (SELECT x.p1 FROM Binder x WHERE x.p1=?2) AND p.p2=?3";
> > Query q = em.createQuery(JPQL_POSITIONAL);
> > q.setParameter(1, DBL_VALUE);
> > q.setParameter(2, INT_VALUE);
> > q.setParameter(3, STR_VALUE);
> >
> > em.getTransaction().begin();
> > int result = q.executeUpdate();
> > em.getTransaction().commit();
> > assertEquals(1, result);
> > }
> >
> > <openjpa-2.2.0-SNAPSHOT-r422266:1195104M nonfatal user error>
> > org.apache.openjpa.persistence.ArgumentException: Query "UPDATE Binder p
> > SET p.p3 = ?1 WHERE p.p1 in (SELECT x.p1 FROM Binder x WHERE x.p1=?2) AND
> > p.p2=?3" did not contain positional parameter 1. JPQL positional
> parameters
> > must start at 1. Detected parameters "[2]".
> >
> > In our application I was able to workaround this error, by adding ?1 = ?1
> > to the where clause! So it's is not a critical issue, but would be useful
> > improvement. Should I create a Jira Issue for this?
> >
> > Regards,
> > Prashant
> >
>