openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Pinaki Poddar" <ppod...@bea.com>
Subject RE: openjpa NativeQuery and NamedNativeQuery only allow select sql statements
Date Mon, 04 Dec 2006 20:41:54 GMT
The query API in JPA is implemented as a facade that delegates the query
execution to the kernel. 
The openjpa kernel can execute a non-SELECT SQL query. 
The observed exception is raised by the JPA facade query. The cause is
the SQL query "insert into DeptBean  (deptno) values  (?)" is not parsed
(or at least its first word is not analyzed) to determine whether it is
a SELECT/UPDATE/DELETE operation. 

So effectively it is a bug. 

I am wondering, however, whether ability to execute non-SELECT SQL query
in the same transaction will cause deeper inconsitencies.

  

Pinaki Poddar
BEA Systems
415.402.7317  


-----Original Message-----
From: George Hongell [mailto:ghongell@us.ibm.com] 
Sent: Monday, December 04, 2006 12:13 PM
To: Patrick Linskey
Cc: Ahmad Nouri; David Wisneski; open-jpa-dev@incubator.apache.org
Subject: RE: openjpa NativeQuery and NamedNativeQuery only allow select
sql statements

Hi Patrick,
I am using Query.executeUpdate()for all but the queries where I am
trying to call a stored procedure where what execute I use depends on
whether the stored procedure returns a result set.

Here is my "insert" example.
            String name = "insert into DeptBean  (deptno) values  (?)";
            Query q1 = _em.createNativeQuery(name);
            q1.setParameter(1,200);
            int inserted = q1.executeUpdate ();

<4|false|0.0.0> org.apache.openjpa.persistence.InvalidStateException: 
Cannot perform an update or delete operation on select query: "insert
into DeptBean  (deptno) values  (?)".
        at
org.apache.openjpa.persistence.QueryImpl.executeUpdate(QueryImpl.java:30
6)
        at
com.ibm.ws.query.tests.JUNamedNativeQueryTest.testInsertQuery(JUNamedNat
iveQueryTest.java:315)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.jav
a:64)
        at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor
Impl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:615)
        at junit.framework.TestCase.runTest(Unknown Source)
        at junit.framework.TestCase.runBare(Unknown Source)
        at junit.framework.TestResult$1.protect(Unknown Source)
        at junit.framework.TestResult.runProtected(Unknown Source)

Here is my "update" example.
            String name = "nativeUpdateDeptBudget";
            Query q1 = _em.createNamedQuery(name);
            q1.setParameter(1,10000);
            int updated = q1.executeUpdate ();


<4|false|0.0.0> org.apache.openjpa.persistence.InvalidStateException: 
Cannot perform an update or delete operation on select query: "update
DeptBean d set d.budget = (d.budget + ?1)".
        at
org.apache.openjpa.persistence.QueryImpl.executeUpdate(QueryImpl.java:30
6)
        at
com.ibm.ws.query.tests.JUNamedNativeQueryTest.testUpdateDeptBudget(JUNam
edNativeQueryTest.java:367)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.jav
a:64)
        at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor
Impl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:615)
        at junit.framework.TestCase.runTest(Unknown Source)
        at junit.framework.TestCase.runBare(Unknown Source)
        at junit.framework.TestResult$1.protect(Unknown Source)
        at junit.framework.TestResult.runProtected(Unknown Source)
        at junit.framework.TestResult.run(Unknown Source)
        at junit.framework.TestCase.run(Unknown Source)
        at junit.framework.TestSuite.runTest(Unknown Source)
        at junit.framework.TestSuite.run(Unknown Source)
        at
org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUn
it3TestReference.java:128)
        at
org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.ja
va:38)
        at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTe
stRunner.java:460)
        at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTe
stRunner.java:673)
        at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRun
ner.java:386)
        at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRu
nner.java:196)




"Patrick Linskey" <plinskey@bea.com>
12/04/2006 10:21 AM

To
<open-jpa-dev@incubator.apache.org>, George Hongell/Santa
Teresa/IBM@IBMUS cc David Wisneski/Santa Teresa/IBM@IBMUS, Ahmad
Nouri/Santa Teresa/IBM@IBMUS Subject
RE: openjpa NativeQuery and NamedNativeQuery only allow select sql
statements






Hi,

How are you executing the query? I'm guessing that you're using
Query.getResultList() or Query.getSingleResult() instead of
Query.executeUpdate().

-Patrick

-- 
Patrick Linskey
BEA Systems, Inc. 

_______________________________________________________________________
Notice:  This email message, together with any attachments, may contain
information  of  BEA Systems,  Inc.,  its subsidiaries  and  affiliated
entities,  that may be confidential,  proprietary,  copyrighted  and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it. 

> -----Original Message-----
> From: George Hongell [mailto:ghongell@us.ibm.com] 
> Sent: Monday, December 04, 2006 10:12 AM
> To: open-jpa-dev@incubator.apache.org
> Cc: David Wisneski; Ahmad Nouri
> Subject: openjpa NativeQuery and NamedNativeQuery only allow 
> select sql statements
> 
> It seems that openjpa NativeQuery and NamedNativeQuery only 
> allow "select" 
> sql statements.  When I try to
> insert, delete, update, create table, alter table, drop 
> table, or call a 
> stored procedure, I get 
> <4|false|0.9.6-incubating-SNAPSHOT> 
> org.apache.openjpa.persistence.InvalidStateException: Cannot 
> perform an 
> update or delete operation on select query: "call out2(?, ?)".
> Is this intentional? 
> I expected the NativeQuery to allow me to execute any valid 
> sql statement.
> 


Mime
View raw message