In some circumstances, OpenJPA will issue a select and then a delete
to delete records.
Have you made any changes in the transaction prior to issuing the JPQL
delete? Are any warnings printed out to your log when issuing the
delete? Do you have any callbacks or listeners registered in your
persistence unit?
-Patrick
On 9/12/07, dboudigue <[EMAIL PROTECTED]> wrote:
>
> Thanks Patrick for these jpa explanations :-)
>
> Using a jpl delete causes 2 jdbc queries a database select and delete, and
> for performance reasons I want to
> avoid the first select query, since I have the table primary key.
>
> Regards
> Didier
>
>
> Patrick Linskey-2 wrote:
> >
> > Native queries can only be SELECT statements per the spec; OpenJPA
> > assumes that if they don't start with SELECT, they're stored
> > procedures. That's what's causing the error.
> >
> > The reason for this is that you can't do anything interesting (from a
> > JPA standpoint) with the results of such a query anyways; you're not
> > loading entities back from the database. So, you should just get a
> > Connection and execute that SQL statement against the database
> > directly.
> >
> > In OpenJPA, you can get the current connection by calling
> > OpenJPAEntityManager.getConnection().
> >
> > Out of curiosity, why don't you use a JPQL bulk delete instead of a
> > native SQL bulk delete?
> >
> > -Patrick
> >
> > On 9/12/07, dboudigue <[EMAIL PROTECTED]> wrote:
> >>
> >> Got the following error when trying to delete data with a SQL native
> >> query as
> >> :
> >>
> >> em.getTransaction().begin();
> >> Query delete = em.createNativeQuery("delete from XCAP where usernm =
> >> ?1");
> >> assertNotNull(delete);
> >> delete.setParameter(1, user);
> >> int deleted = delete.executeUpdate();
> >>
> >> <openjpa-1.0.0-r420667:568756 nonfatal general error>
> >> org.apache.openjpa.persistence.PersistenceException: Unable to retrieve
> >> metadata for procedure.
> >> at
> >> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:3849)
> >> at
> >> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:97)
> >> at
> >> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:83)
> >> at
> >> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:59)
> >> at
> >> org.apache.openjpa.jdbc.kernel.SQLStoreQuery$SQLExecutor.executeUpdate(SQLStoreQuery.java:243)
> >> at
> >> org.apache.openjpa.kernel.QueryImpl.update(QueryImpl.java:1039)
> >> at
> >> org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:803)
> >> at
> >> org.apache.openjpa.kernel.QueryImpl.updateAll(QueryImpl.java:878)
> >> at
> >> org.apache.openjpa.kernel.DelegatingQuery.updateAll(DelegatingQuery.java:573)
> >> at
> >> org.apache.openjpa.persistence.QueryImpl.executeUpdate(QueryImpl.java:319)
> >> at
> >> com.alcatel_lucent.jpa.test.JXCapSQLWriteTest.delete(JXCapSQLWriteTest.java:56)
> >> at
> >> com.alcatel_lucent.jpa.test.JXCapSQLWriteTest.testXCapWrite(JXCapSQLWriteTest.java:41)
> >> 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 junit.framework.TestCase.runTest(TestCase.java:154)
> >> at junit.framework.TestCase.runBare(TestCase.java:127)
> >> at junit.framework.TestResult$1.protect(TestResult.java:106)
> >> at junit.framework.TestResult.runProtected(TestResult.java:124)
> >> at junit.framework.TestResult.run(TestResult.java:109)
> >> at junit.framework.TestCase.run(TestCase.java:118)
> >> at junit.framework.TestSuite.runTest(TestSuite.java:208)
> >> at junit.framework.TestSuite.run(TestSuite.java:203)
> >> at
> >> org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:128)
> >> at
> >> org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
> >> at
> >> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
> >> at
> >> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
> >> at
> >> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
> >> at
> >> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
> >> Caused by: java.sql.SQLException: Unable to retrieve metadata for
> >> procedure.
> >> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
> >> at
> >> com.mysql.jdbc.CallableStatement.extractProcedureName(CallableStatement.java:793)
> >> at
> >> com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:628)
> >> at
> >> com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:453)
> >> at
> >> com.mysql.jdbc.Connection.parseCallableStatement(Connection.java:4365)
> >> at com.mysql.jdbc.Connection.prepareCall(Connection.java:4439)
> >> at com.mysql.jdbc.Connection.prepareCall(Connection.java:4413)
> >> at
> >> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareCall(DelegatingConnection.java:185)
> >> at
> >> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareCall(DelegatingConnection.java:183)
> >> at
> >> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareCall(DelegatingConnection.java:183)
> >> at
> >> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareCall(DelegatingConnection.java:172)
> >> at
> >> org.apache.openjpa.jdbc.sql.SQLBuffer.prepareCall(SQLBuffer.java:534)
> >> at
> >> org.apache.openjpa.jdbc.sql.SQLBuffer.prepareCall(SQLBuffer.java:514)
> >> at
> >> org.apache.openjpa.jdbc.sql.SQLBuffer.prepareCall(SQLBuffer.java:503)
> >> at
> >> org.apache.openjpa.jdbc.kernel.SQLStoreQuery$SQLExecutor.executeUpdate(SQLStoreQuery.java:224)
> >> ... 25 more
> >>
> >> Regards
> >> Didier
> >> --
> >> View this message in context:
> >> http://www.nabble.com/native-sql-tf4428366.html#a12632752
> >> Sent from the OpenJPA Users mailing list archive at Nabble.com.
> >>
> >>
> >
> >
> > --
> > Patrick Linskey
> > 202 669 5907
> >
> >
>
> --
> View this message in context:
> http://www.nabble.com/native-sql-tf4428366.html#a12649366
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
>
--
Patrick Linskey
202 669 5907
