From open-jpa-dev-return-2699-apmail-incubator-open-jpa-dev-archive=incubator.apache.org@incubator.apache.org Tue Mar 13 19:17:57 2007 Return-Path: Delivered-To: apmail-incubator-open-jpa-dev-archive@locus.apache.org Received: (qmail 77113 invoked from network); 13 Mar 2007 19:17:56 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 13 Mar 2007 19:17:56 -0000 Received: (qmail 74070 invoked by uid 500); 13 Mar 2007 19:18:04 -0000 Delivered-To: apmail-incubator-open-jpa-dev-archive@incubator.apache.org Received: (qmail 74039 invoked by uid 500); 13 Mar 2007 19:18:04 -0000 Mailing-List: contact open-jpa-dev-help@incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: open-jpa-dev@incubator.apache.org Delivered-To: mailing list open-jpa-dev@incubator.apache.org Received: (qmail 74025 invoked by uid 99); 13 Mar 2007 19:18:04 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 13 Mar 2007 12:18:04 -0700 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: domain of mprudhomapache@gmail.com designates 209.85.132.251 as permitted sender) Received: from [209.85.132.251] (HELO an-out-0708.google.com) (209.85.132.251) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 13 Mar 2007 12:17:52 -0700 Received: by an-out-0708.google.com with SMTP id b2so1806907ana for ; Tue, 13 Mar 2007 12:17:31 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:in-reply-to:references:mime-version:content-type:message-id:cc:content-transfer-encoding:from:subject:date:to:x-mailer:sender; b=Zv+1vBRb3mOmEmVus24PAmuoPqNtV99w28A49dUYUMpAMIeKM+TDelgImWhFN3tV45mAK32TirG31GcFyw2ISAnNZcPlVEbnUAdANGCmtt3iCSdKpqKn50Trpa5t+ysRXsefWXFiB99pU9rqz9TkXu8j1arPZDjjjy5Sxm6eBS4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:in-reply-to:references:mime-version:content-type:message-id:cc:content-transfer-encoding:from:subject:date:to:x-mailer:sender; b=X3bAfmeYww9og2+pYw3cAd5hFSH1VMwdnGy2/R3V6v3Rpy2+LMMH6gEl22KRheuRzfngVbzXV7/32+NtMKQYsbPkpM+P4cfaRiwOkTC0a4X5d09B1hFDBXj8xOyrt0vPmOHW7ARdDzmfjK8NxA56biXQntrl26ia01kc3Jwyyeg= Received: by 10.100.191.5 with SMTP id o5mr1105175anf.1173813451208; Tue, 13 Mar 2007 12:17:31 -0700 (PDT) Received: from ?192.168.15.101? ( [66.248.222.34]) by mx.google.com with ESMTP id b14sm12005441ana.2007.03.13.12.17.30; Tue, 13 Mar 2007 12:17:31 -0700 (PDT) In-Reply-To: <45F6EC01.5060307@sun.com> References: <89c0c52c0703130825n73a5fae2t626088854b935e30@mail.gmail.com> <6202A0C0-D7F0-49D1-9EB3-D769240C4FE3@apache.org> <45F6EC01.5060307@sun.com> Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <46C925F5-8DD3-42CF-B79A-35AD5013B3DF@apache.org> Cc: John Stecher Content-Transfer-Encoding: 7bit From: Marc Prud'hommeaux Subject: Re: OpenJPA error with NamedNativeQuery Date: Tue, 13 Mar 2007 12:17:23 -0700 To: open-jpa-dev@incubator.apache.org X-Mailer: Apple Mail (2.752.3) Sender: Marc Prud'hommeaux X-Virus-Checked: Checked by ClamAV on apache.org 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 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 >>> >>> >>