I am fairly certain that the only way OpenJPA will ever think that a
native (i.e., SQL) query is not a select query is if it doesn't start
with "select".
Is it possible that you didn't recompile the class after
experimenting with the "quoteForUpdate" @NamedNativeQuery? At some
point in the past, did the query string start with "update"?
On Mar 13, 2007, at 8:25 AM, Kevin Sutter wrote:
Hi,
John is having some problems with posting to the open-jpa-dev
mailing list
via his e-mail account, so I am posting this message for him so
that we can
start this conversation...
=================================================
Fellow OpenJPAers, :)
As Craig had brought up (through Scott Oaks) a while ago and
Patrick was
privy to on a discussion with a benchmarking organization we needed a
mechanism to implement pessimistic locking in some fashion under
certain
circumstances inside of our current optimistic world. We decided
at that
time that the only really portable way to do this at the moment was to
create a named query in the benchmark that could then be overridden
in the
ORM.xml file. Well I took that at face value and started pushing the
methodology on the folks working on DayTrader for Apache and today
we found
out that you cannot issue a select for update query with either the
Oracle
Toplink RI or OpenJPA 0.9.7-inc builds at this time. Maybe I am just
missing something in our approach but we decided to leave out the
ORM.xmlmapping and just use a native named query in the code for
DayTrader just to
get it off the ground and running. Code snipit below:
Native named Query defined as this:
@NamedNativeQueries({
@NamedNativeQuery(name="quoteForUpdate", query="select * from
quoteejb q
where q.symbol=? for update",resultClass=
org.apache.geronimo.samples.daytrader.Quoteejb.class)
})
EJB method calling is this:
public Quoteejb testForUpdateQuery(String symbol){
Query q = em.createNativeQuery("quoteForUpdate");
q.setParameter(1,symbol);
//Quoteejb temp = (Quoteejb) q.getSingleResult();
Quoteejb temp = (Quoteejb) q.getResultList().get(0);
temp.setCompanyname("IBM_"+System.currentTimeMillis());
return em.merge(temp);
}
This code produces the following errors on Toplink and on OpenJPA
Toplink:
Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2006.8 (Build
060830)): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: com.ibm.db2.jcc.c.SqlException:
[ibm][db2][jcc][10103][10941] Method executeQuery cannot be used for
update.Error Code: -99999
Call:quoteForUpdate
Query:DataReadQuery()
at
oracle.toplink.essentials.exceptions.DatabaseException.sqlException(
DatabaseException.java:303)
at
oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.bas
icExecuteCall
(DatabaseAccessor.java:551)
at
oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.exe
cuteCall
(DatabaseAccessor.java:437)
at oracle.toplink.essentials.threetier.ServerSession.executeCall(
ServerSession.java:465)
at
oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryM
echanism.executeCall
(DatasourceCallQueryMechanism.java:213)
at
oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryM
echanism.executeCall
(DatasourceCallQueryMechanism.java:199)
at
oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryM
echanism.executeSelectCall
(DatasourceCallQueryMechanism.java:270)
at
oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryM
echanism.executeSelect
(DatasourceCallQueryMechanism.java:252)
OpenJPA:Caused by: <4|false|0.9.7-incubating-SNAPSHOT>
org.apache.openjpa.persistence.InvalidStateException: Cannot perform a
select on update or delete query: "quoteForUpdate".
at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:
215)
at org.apache.openjpa.persistence.QueryImpl.getResultList
(QueryImpl.java
:264)
at
com.ibm.performance.primitives.jee5.ejb.QuoteejbFacade.testForUpdateQu
ery
(QuoteejbFacade.java:62)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke
(NativeMethodAccessorImpl.java
:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(
DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at
com.sun.enterprise.security.application.EJBSecurityManager.runMethod(
EJBSecurityManager.java:1050)
at com.sun.enterprise.security.SecurityUtil.invoke
(SecurityUtil.java:165)
at com.sun.ejb.containers.BaseContainer.invokeTargetBeanMethod(
BaseContainer.java:2766)
at com.sun.ejb.containers.BaseContainer.intercept
(BaseContainer.java:3847)
at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(
EJBLocalObjectInvocationHandler.java:184)
... 30 more
This query works perfectly fine in a straight up JDBC case so I
can't see
why it wouldn't work here.
Looking forward to any responses. Would be happy if it was
something the
folks building DayTrader were doing wrong with the code but I think
it might
be a OpenJPA issue.
Sincerely,
John
