Native Query: Update fails with InvalidStateException

7 Messages

sbedoll

Native Query: Update fails with InvalidStateException

Reply Threaded

I am using local transactions and get this error when executing a NativeQuery (query.executeUpdate)

<openjpa-1.0.1-r420667:592145 nonfatal user error> org.apache.openjpa.persistence.InvalidStateException: Cannot perform an update or delete operation on select query: "Update FPA2.NW_OR_004_14702765 Set Cost=? Where PT_GRID_ID=?".
at org.apache.openjpa.persistence.QueryImpl.executeUpdate(QueryImpl.java:324)
at fpa.fw.ds.jpadataadapter.JPADataSourceAdapter.executeNativeUpdate(JPADataSourceAdapter.java:815)

I am using IBM JPA. Is there something I am missing ?

Fay Wang

Re: Native Query: Update fails with InvalidStateException

Reply Threaded

Hi,
I could not reproduce this problem against DB2. Which database are you using? also,can you post your persistence.xml to see if there is any special setting?

Fay

--- On Mon, 10/27/08, sbedoll <bedoll@...> wrote:

> From: sbedoll <bedoll@...>
> Subject: Native Query: Update fails with InvalidStateException
> To: users@...
> Date: Monday, October 27, 2008, 6:37 AM
> I am using local transactions and get this error when
> executing a NativeQuery
> (query.executeUpdate)
>
> <openjpa-1.0.1-r420667:592145 nonfatal user error>
> org.apache.openjpa.persistence.InvalidStateException:
> Cannot perform an
> update or delete operation on select query: "Update
> FPA2.NW_OR_004_14702765
> Set Cost=? Where PT_GRID_ID=?".
> at
> org.apache.openjpa.persistence.QueryImpl.executeUpdate(QueryImpl.java:324)
> at
> fpa.fw.ds.jpadataadapter.JPADataSourceAdapter.executeNativeUpdate(JPADataSourceAdapter.java:815)
>
> I am using IBM JPA. Is there something I am missing ?
>
>
> --
> View this message in context:
> http://n2.nabble.com/Native-Query%3A-Update-fails-with-InvalidStateException-tp1382362p1382362.html
> Sent from the OpenJPA Users mailing list archive at
> Nabble.com.

sbedoll

Re: Native Query: Update fails with InvalidStateException

Reply Threaded

Fay Wang wrote:

Hi,
I could not reproduce this problem against DB2. Which database are you using? also,can you post your persistence.xml to see if there is any special setting?

Fay

--- On Mon, 10/27/08, sbedoll <bedoll@us.ibm.com> wrote:

> From: sbedoll <bedoll@us.ibm.com>
> Subject: Native Query: Update fails with InvalidStateException
> To: users@openjpa.apache.org
> Date: Monday, October 27, 2008, 6:37 AM
> I am using local transactions and get this error when
> executing a NativeQuery
> (query.executeUpdate)
>
> <openjpa-1.0.1-r420667:592145 nonfatal user error>
> org.apache.openjpa.persistence.InvalidStateException:
> Cannot perform an
> update or delete operation on select query: "Update
> FPA2.NW_OR_004_14702765
> Set Cost=? Where PT_GRID_ID=?".
> at
> org.apache.openjpa.persistence.QueryImpl.executeUpdate(QueryImpl.java:324)
> at
> fpa.fw.ds.jpadataadapter.JPADataSourceAdapter.executeNativeUpdate(JPADataSourceAdapter.java:815)
>
> I am using IBM JPA. Is there something I am missing ?
>
>
> --
> View this message in context:
> http://n2.nabble.com/Native-Query%3A-Update-fails-with-InvalidStateException-tp1382362p1382362.html
> Sent from the OpenJPA Users mailing list archive at
> Nabble.com.

I am using Oracle 10g. The native query for a select worked ok. There was a posting back in 2006 that exhibited the same behavior where it did not delegate properly based on the "update" string in the SQL.
I have uploaded the persistence.xml .. but removed the persistence units that were not involed to simplify the file.
The call that fails is: Query q = entityManager.createNativeQuery(update,objclass); Where "update" is a string that contains the native query.

Also note that this is not a NamedNativeQuery .. i am testing that scenario but the table name is dynamic so I don't see a how that can work for me.

junitpersistence.xml

sbedoll

Re: Native Query: Update fails with InvalidStateException

Reply Threaded

In reply to this post by Fay Wang

Some javascript/style in this post has been disabled (why?)

What version of the IBM feature pack did you use. I am at:

Name WebSphere Application Server Version 6.1 Feature Pack for EJB 3.0
Version 6.1.0.13
ID EJB3
Build Level f0747.05
Build Date 11/30/07

Installed Product
Fay Wang <fyw300@...>

Fay Wang <fyw300@...>
10/27/2008 10:10 AM

Please respond to
users@...

To

users@...

cc

Subject

Re: Native Query: Update fails with InvalidStateException

Hi, I could not reproduce this problem against DB2. Which database are you using? also,can you post your persistence.xml to see if there is any special setting? Fay --- On Mon, 10/27/08, sbedoll <bedoll@...> wrote: > From: sbedoll <bedoll@...> > Subject: Native Query: Update fails with InvalidStateException > To: users@... > Date: Monday, October 27, 2008, 6:37 AM > I am using local transactions and get this error when > executing a NativeQuery > (query.executeUpdate) > > <openjpa-1.0.1-r420667:592145 nonfatal user error> > org.apache.openjpa.persistence.InvalidStateException: > Cannot perform an > update or delete operation on select query: "Update > FPA2.NW_OR_004_14702765 > Set Cost=? Where PT_GRID_ID=?". > at > org.apache.openjpa.persistence.QueryImpl.executeUpdate(QueryImpl.java:324) > at > fpa.fw.ds.jpadataadapter.JPADataSourceAdapter.executeNativeUpdate(JPADataSourceAdapter.java:815) > > I am using IBM JPA. Is there something I am missing ? > > > -- > View this message in context: >http://n2.nabble.com/Native-Query%3A-Update-fails-with-InvalidStateException-tp1382362p1382362.html> Sent from the OpenJPA Users mailing list archive at > Nabble.com.

Fay Wang

Re: Native Query: Update fails with InvalidStateException

Reply Threaded

Hi, Stuart,

   The error message "Cannot perform an update or delete operation on select query: "Update FPA2.NW_OR_004_14702765 Set Cost=? Where PT_GRID_ID=?"." suggests that openjpa thinks your native query is a select statement. The call to executeUpdate therefore can not be performed on a select statement.

    There are a couple of reasons why openjpa thinks your query is a select statement. The following is an example (note the result class, BasicA.class, is an input paramter to the createNativeQuery api). An executeUpdate on it results in the same error as you describe:

        Query q = em.createNativeQuery("UPDATE BasicA t set t.name= ? WHERE t.id = ?", BasicA.class);

       The query below takes out the result class and works just fine:
        Query q = em.createNativeQuery("UPDATE BasicA t set t.name= ? WHERE t.id = ?");

Regards,
Fay

--- On Tue, 10/28/08, Stuart Bedoll <bedoll@...> wrote:
From: Stuart Bedoll <bedoll@...>
Subject: Re: Native Query: Update fails with InvalidStateException
To: users@...
Date: Tuesday, October 28, 2008, 1:06 PM

What version of the IBM feature pack did you use. I am at:

Name WebSphere Application Server Version 6.1 Feature Pack for EJB 3.0

Version 6.1.0.13

ID EJB3

Build Level f0747.05

Build Date 11/30/07

Installed Product

Fay Wang <fyw300@...>

Fay Wang <fyw300@...>
10/27/2008 10:10 AM

Please respond to

users@...

To

users@...

cc

Subject

Re: Native Query: Update fails with InvalidStateException

Hi,

   I could not reproduce this problem against DB2. Which database are you using? also,can you post your persistence.xml to see if there is any special setting?

Fay

--- On Mon, 10/27/08, sbedoll <bedoll@...> wrote:

> From: sbedoll <bedoll@...>

> Subject: Native Query: Update fails with InvalidStateException

> To: users@...

> Date: Monday, October 27, 2008, 6:37 AM

> I am using local transactions and get this error when

> executing a NativeQuery

> (query.executeUpdate)

>

> <openjpa-1.0.1-r420667:592145 nonfatal user error>

> org.apache.openjpa.persistence.InvalidStateException:

> Cannot perform an

> update or delete operation on select query: "Update

> FPA2.NW_OR_004_14702765

> Set Cost=? Where PT_GRID_ID=?".

> at

> org.apache.openjpa.persistence.QueryImpl.executeUpdate(QueryImpl.java:324)

> at

> fpa.fw.ds.jpadataadapter.JPADataSourceAdapter.executeNativeUpdate(JPADataSourceAdapter.java:815)

>

> I am using IBM JPA. Is there something I am missing ?

>

>

> --

> View this message in context:

> http://n2.nabble.com/Native-Query%3A-Update-fails-with-InvalidStateException-tp1382362p1382362.html

> Sent from the OpenJPA Users mailing list archive at

> Nabble.com.

Fay Wang

Re: Native Query: Update fails with InvalidStateException

Reply Threaded

In reply to this post by sbedoll

Hi, Stuart,
      The error message "Cannot perform an update or delete operation on select query" suggests that openjpa thinks your query is a select operation, and therefore when executeUpdate is called on this query, openjpa does not know how to update/delete on the select statement.

There are a couple of reason why openjpa will think a query is a select statement. The following is one example:

Query query = em.createNativeQuery("update BasicA set name = ? where id =?", BasicA.class);

Note that the result class (BasicA.class) is provided as an extra input parameter to the createNativeQuery api. For this query object, if you call executeUpdate on it, you will get "Cannot perform an update or delete operation on select query" error. You might want to check your code to see exactly how the native query is created.

Regards,
Fay

--- On Tue, 10/28/08, Stuart Bedoll <bedoll@...> wrote:
From: Stuart Bedoll <bedoll@...>
Subject: Re: Native Query: Update fails with InvalidStateException
To: users@...
Date: Tuesday, October 28, 2008, 1:06 PM

What version of the IBM feature pack did you use. I am at:

Name WebSphere Application Server Version 6.1 Feature Pack for EJB 3.0

Version 6.1.0.13

ID EJB3

Build Level f0747.05

Build Date 11/30/07

Installed Product

Fay Wang <fyw300@...>

Fay Wang <fyw300@...>
10/27/2008 10:10 AM

Please respond to

users@...

To

users@...

cc

Subject

Re: Native Query: Update fails with InvalidStateException

Hi,

   I could not reproduce this problem against DB2. Which database are you using? also,can you post your persistence.xml to see if there is any special setting?

Fay

--- On Mon, 10/27/08, sbedoll <bedoll@...> wrote:

> From: sbedoll <bedoll@...>

> Subject: Native Query: Update fails with InvalidStateException

> To: users@...

> Date: Monday, October 27, 2008, 6:37 AM

> I am using local transactions and get this error when

> executing a NativeQuery

> (query.executeUpdate)

>

> <openjpa-1.0.1-r420667:592145 nonfatal user error>

> org.apache.openjpa.persistence.InvalidStateException:

> Cannot perform an

> update or delete operation on select query: "Update

> FPA2.NW_OR_004_14702765

> Set Cost=? Where PT_GRID_ID=?".

> at

> org.apache.openjpa.persistence.QueryImpl.executeUpdate(QueryImpl.java:324)

> at

> fpa.fw.ds.jpadataadapter.JPADataSourceAdapter.executeNativeUpdate(JPADataSourceAdapter.java:815)

>

> I am using IBM JPA. Is there something I am missing ?

>

>

> --

> View this message in context:

> http://n2.nabble.com/Native-Query%3A-Update-fails-with-InvalidStateException-tp1382362p1382362.html

> Sent from the OpenJPA Users mailing list archive at

> Nabble.com.

sbedoll

Re: Native Query: Update fails with InvalidStateException

Reply Threaded

Some javascript/style in this post has been disabled (why?)

great.. that worked.. thanks!
Fay Wang <fyw300@...>

Fay Wang <fyw300@...>
10/28/2008 10:00 PM

Please respond to
users@...

To

users@...

cc

fyw300@...

Subject

Re: Native Query: Update fails with InvalidStateException

Hi, Stuart, The error message "Cannot perform an update or delete operation on select query" suggests that openjpa thinks your query is a select operation, and therefore when executeUpdate is called on this query, openjpa does not know how to update/delete on the select statement. There are a couple of reason why openjpa will think a query is a select statement. The following is one example: Query query = em.createNativeQuery("update BasicA set name = ? where id =?", BasicA.class); Note that the result class (BasicA.class) is provided as an extra input parameter to the createNativeQuery api. For this query object, if you call executeUpdate on it, you will get "Cannot perform an update or delete operation on select query" error. You might want to check your code to see exactly how the native query is created. Regards, Fay --- On Tue, 10/28/08, Stuart Bedoll <bedoll@...> wrote: From: Stuart Bedoll <bedoll@...> Subject: Re: Native Query: Update fails with InvalidStateException To: users@... Date: Tuesday, October 28, 2008, 1:06 PM What version of the IBM feature pack did you use. I am at: Name WebSphere Application Server Version 6.1 Feature Pack for EJB 3.0 Version 6.1.0.13 ID EJB3 Build Level f0747.05 Build Date 11/30/07 Installed Product Fay Wang <fyw300@...> Fay Wang <fyw300@...> 10/27/2008 10:10 AM Please respond to users@... To users@... cc Subject Re: Native Query: Update fails with InvalidStateException Hi, I could not reproduce this problem against DB2. Which database are you using? also,can you post your persistence.xml to see if there is any special setting? Fay --- On Mon, 10/27/08, sbedoll <bedoll@...> wrote: > From: sbedoll <bedoll@...> > Subject: Native Query: Update fails with InvalidStateException > To: users@... > Date: Monday, October 27, 2008, 6:37 AM > I am using local transactions and get this error when > executing a NativeQuery > (query.executeUpdate) > > <openjpa-1.0.1-r420667:592145 nonfatal user error> > org.apache.openjpa.persistence.InvalidStateException: > Cannot perform an > update or delete operation on select query: "Update > FPA2.NW_OR_004_14702765 > Set Cost=? Where PT_GRID_ID=?". > at > org.apache.openjpa.persistence.QueryImpl.executeUpdate(QueryImpl.java:324) > at > fpa.fw.ds.jpadataadapter.JPADataSourceAdapter.executeNativeUpdate(JPADataSourceAdapter.java:815) > > I am using IBM JPA. Is there something I am missing ? > > > -- > View this message in context: >http://n2.nabble.com/Native-Query%3A-Update-fails-with-InvalidStateException-tp1382362p1382362.html> Sent from the OpenJPA Users mailing list archive at > Nabble.com.