openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Patrick Linskey" <plins...@gmail.com>
Subject Re: native sql
Date Wed, 12 Sep 2007 16:15:16 GMT
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 <didier.boudigue@alcatel-lucent.fr> 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

Mime
View raw message