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.
