openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Marc Prud'hommeaux <mprud...@apache.org>
Subject Re: OpenJPA error with NamedNativeQuery
Date Tue, 13 Mar 2007 19:17:23 GMT
Mitesh-

Good catch! You're right: it should be "createNamedQuery", not  
"createNativeQuery".

As it stands, the query will just try executing the string  
"quoteForUpdate" as SQL, which is why we interpret it as a non-select  
statement.



On Mar 13, 2007, at 11:22 AM, Mitesh Meswani wrote:

> Hi John,
>
> > EJB method calling is this:
> > public Quoteejb testForUpdateQuery(String symbol){
> > Query q = em.createNativeQuery("quoteForUpdate");
> > q.setParameter(1,symbol);
> Shouldn't you call Query q = em.createNativeNamedQuery 
> ("quoteForUpdate");  ?
>
> Regards,
> Mitesh
>
> Marc Prud'hommeaux wrote:
>> John-
>>
>> I just ran a test with a named native query that did a "select ...  
>> for update" and it worked fine.
>>
>> One possibility is that our identification of whether the native  
>> query is a select or an update is done by some fairly simplistic  
>> parsing of whether the statement starts with "select" or not. Is  
>> it possible there is some extra hidden character in the string? If  
>> you delete the NamedNativeQuery line and re-type it, do you still  
>> get the same error?
>>
>> If that doesn't help, can we try to narrow down the problem by  
>> bypassing the named query and trying to just execute the query  
>> directly? E.g., do the following:
>>
>> em.createNativeQuery("select * from quoteejb q where q.symbol=?  
>> for update").setParameter(1, "SYMBOL").getResultList();
>>
>>
>>
>> On Mar 13, 2007, at 11:03 AM, John Stecher wrote:
>>
>>> Marc,
>>> I know that we recompiled the classes multiple times and I also  
>>> know that the query is a correct/working for DB2 when running in  
>>> a JDBC environment.  The statement never started with update.  I  
>>> wouldn't expect that OpenJPA would be attempting to dictate what  
>>> type of query I am running in a native sql statement by it  
>>> syntax.   How are others doing this select for update on a  
>>> finder?  A pure update doesn't work as it would not allow me to  
>>> hold the lock on the database.  Patrick had said that this should  
>>> work with Kodo in our benchmarking disucssion (as did Mike Keith  
>>> for Toplink) so I am interested to see if it is in some manner  
>>> working with a test case that I am not privy to.
>>>
>>> Any help would be greatly appreciated as I believe this is a  
>>> gating factor for us moving forward testing the SPEC benchmarks  
>>> as well as DayTrader being developed for Apache.
>>>
>>> John
>>>
>>> On 3/13/07, Marc Prud'hommeaux <mprudhom@apache.org> wrote:
>>> 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.b 
>>> as
>>> > icExecuteCall
>>> > (DatabaseAccessor.java:551)
>>> > at
>>> >  
>>> oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.e 
>>> xe
>>> > cuteCall
>>> > (DatabaseAccessor.java:437)
>>> > at oracle.toplink.essentials.threetier.ServerSession.executeCall(
>>> > ServerSession.java:465)
>>> > at
>>> >  
>>> oracle.toplink.essentials.internal.queryframework.DatasourceCallQuer 
>>> yM
>>> > echanism.executeCall
>>> > (DatasourceCallQueryMechanism.java:213)
>>> > at
>>> >  
>>> oracle.toplink.essentials.internal.queryframework.DatasourceCallQuer 
>>> yM
>>> > echanism.executeCall
>>> > (DatasourceCallQueryMechanism.java :199)
>>> > at
>>> >  
>>> oracle.toplink.essentials.internal.queryframework.DatasourceCallQuer 
>>> yM
>>> > echanism.executeSelectCall
>>> > (DatasourceCallQueryMechanism.java:270)
>>> > at
>>> >  
>>> oracle.toplink.essentials.internal.queryframework.DatasourceCallQuer 
>>> yM
>>> > 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.testForUpdate 
>>> Qu
>>> > 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
>>>
>>>
>>


Mime
View raw message