You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by dboudigue <di...@alcatel-lucent.fr> on 2007/09/12 12:28:53 UTC
native sql
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.
Re: native sql
Posted by Patrick Linskey <pl...@gmail.com>.
Hi,
I suspect that the inefficiency is happening because of your use of
compound identity -- in some circumstances, OpenJPA can't do bulk
deletes when using compound identity currently.
However, I also noticed that your query involves only the primary
keys. So, you're not actually doing a bulk delete; you're just
deleting the one record that matches those values, correct?
If this is the case, then you could use the delete(getReference()) pattern:
em.delete(em.getReference(XCap.class, new XCapKey(...));
This will result in a single DELETE statement, since the SELECT can be
optimized away when using getReference().
-Patrick
On 9/13/07, dboudigue <di...@alcatel-lucent.fr> wrote:
>
> here it is.
>
> http://www.nabble.com/file/p12669430/XCap.java XCap.java
> http://www.nabble.com/file/p12669430/XCapKey.java XCapKey.java
>
> Patrick Linskey-2 wrote:
> >
> > Can you post the XCAP class source as well?
> >
> > Generally-speaking, a select should not always be necessary.
> >
> > -Patrick
> >
> > On 9/13/07, dboudigue <di...@alcatel-lucent.fr> wrote:
> >>
> >> Patrick, here are the code and traces.
> >> Anyway, an option to skip select in jpl delete or update that would be
> >> a
> >> nice feature.
> >>
> >> Didier
> >>
> >> ....
> >>
> >> EntityManagerFactory factory =
> >> Persistence.createEntityManagerFactory("xcap", props);
> >> EntityManager em = factory.createEntityManager();
> >> em.getTransaction().begin();
> >> Query delete = em.createQuery("delete from XCAP x where x.user = :user
> >> and
> >> x.file = :file and x.auid = :auid");
> >> delete.setParameter("user", user);
> >> delete.setParameter("file", file);
> >> delete.setParameter("auid", auid);
> >> int deleted = delete.executeUpdate();
> >> em.close();
> >> factory.close();
> >>
> >>
> >> .....
> >>
> >> 36 xcap INFO [main] openjpa.Runtime - Starting OpenJPA 1.0.0
> >> 159 xcap INFO [main] openjpa.jdbc.JDBC - Using dictionary class
> >> "org.apache.openjpa.jdbc.sql.MySQLDictionary".
> >> 329 xcap INFO [main] openjpa.Enhance - Creating subclass and
> >> redefining
> >> methods for "[class com.alcatel_lucent.jpa.entity.XCap]". This means that
> >> your application will be less efficient than it would if you ran the
> >> OpenJPA
> >> enhancer.
> >> 2017 xcap TRACE [main] openjpa.jdbc.SQL - <t 7438914, conn 2016650>
> >> executing prepstmnt 21006994 SELECT t0.auid, t0.FILENM, t0.USERNM,
> >> t0.CONTENT_TYPE, t0.DOC, t0.DOC_SZ, t0.etag, t0.MODIFIED_DATE FROM XCAP
> >> t0
> >> WHERE (t0.USERNM = ? AND t0.FILENM = ? AND t0.auid = ?) [params=(String)
> >> didier1, (String) index1, (String) auid1]
> >> 2051 xcap TRACE [main] openjpa.jdbc.SQL - <t 7438914, conn 2016650>
> >> [33
> >> ms] spent
> >>
> >>
> >>
> >> --
> >> View this message in context:
> >> http://www.nabble.com/native-sql-tf4428366.html#a12650013
> >> 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#a12669430
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
>
--
Patrick Linskey
202 669 5907
Re: native sql
Posted by dboudigue <di...@alcatel-lucent.fr>.
here it is.
http://www.nabble.com/file/p12669430/XCap.java XCap.java
http://www.nabble.com/file/p12669430/XCapKey.java XCapKey.java
Patrick Linskey-2 wrote:
>
> Can you post the XCAP class source as well?
>
> Generally-speaking, a select should not always be necessary.
>
> -Patrick
>
> On 9/13/07, dboudigue <di...@alcatel-lucent.fr> wrote:
>>
>> Patrick, here are the code and traces.
>> Anyway, an option to skip select in jpl delete or update that would be
>> a
>> nice feature.
>>
>> Didier
>>
>> ....
>>
>> EntityManagerFactory factory =
>> Persistence.createEntityManagerFactory("xcap", props);
>> EntityManager em = factory.createEntityManager();
>> em.getTransaction().begin();
>> Query delete = em.createQuery("delete from XCAP x where x.user = :user
>> and
>> x.file = :file and x.auid = :auid");
>> delete.setParameter("user", user);
>> delete.setParameter("file", file);
>> delete.setParameter("auid", auid);
>> int deleted = delete.executeUpdate();
>> em.close();
>> factory.close();
>>
>>
>> .....
>>
>> 36 xcap INFO [main] openjpa.Runtime - Starting OpenJPA 1.0.0
>> 159 xcap INFO [main] openjpa.jdbc.JDBC - Using dictionary class
>> "org.apache.openjpa.jdbc.sql.MySQLDictionary".
>> 329 xcap INFO [main] openjpa.Enhance - Creating subclass and
>> redefining
>> methods for "[class com.alcatel_lucent.jpa.entity.XCap]". This means that
>> your application will be less efficient than it would if you ran the
>> OpenJPA
>> enhancer.
>> 2017 xcap TRACE [main] openjpa.jdbc.SQL - <t 7438914, conn 2016650>
>> executing prepstmnt 21006994 SELECT t0.auid, t0.FILENM, t0.USERNM,
>> t0.CONTENT_TYPE, t0.DOC, t0.DOC_SZ, t0.etag, t0.MODIFIED_DATE FROM XCAP
>> t0
>> WHERE (t0.USERNM = ? AND t0.FILENM = ? AND t0.auid = ?) [params=(String)
>> didier1, (String) index1, (String) auid1]
>> 2051 xcap TRACE [main] openjpa.jdbc.SQL - <t 7438914, conn 2016650>
>> [33
>> ms] spent
>>
>>
>>
>> --
>> View this message in context:
>> http://www.nabble.com/native-sql-tf4428366.html#a12650013
>> 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#a12669430
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: native sql
Posted by Patrick Linskey <pl...@gmail.com>.
Can you post the XCAP class source as well?
Generally-speaking, a select should not always be necessary.
-Patrick
On 9/13/07, dboudigue <di...@alcatel-lucent.fr> wrote:
>
> Patrick, here are the code and traces.
> Anyway, an option to skip select in jpl delete or update that would be a
> nice feature.
>
> Didier
>
> ....
>
> EntityManagerFactory factory =
> Persistence.createEntityManagerFactory("xcap", props);
> EntityManager em = factory.createEntityManager();
> em.getTransaction().begin();
> Query delete = em.createQuery("delete from XCAP x where x.user = :user and
> x.file = :file and x.auid = :auid");
> delete.setParameter("user", user);
> delete.setParameter("file", file);
> delete.setParameter("auid", auid);
> int deleted = delete.executeUpdate();
> em.close();
> factory.close();
>
>
> .....
>
> 36 xcap INFO [main] openjpa.Runtime - Starting OpenJPA 1.0.0
> 159 xcap INFO [main] openjpa.jdbc.JDBC - Using dictionary class
> "org.apache.openjpa.jdbc.sql.MySQLDictionary".
> 329 xcap INFO [main] openjpa.Enhance - Creating subclass and redefining
> methods for "[class com.alcatel_lucent.jpa.entity.XCap]". This means that
> your application will be less efficient than it would if you ran the OpenJPA
> enhancer.
> 2017 xcap TRACE [main] openjpa.jdbc.SQL - <t 7438914, conn 2016650>
> executing prepstmnt 21006994 SELECT t0.auid, t0.FILENM, t0.USERNM,
> t0.CONTENT_TYPE, t0.DOC, t0.DOC_SZ, t0.etag, t0.MODIFIED_DATE FROM XCAP t0
> WHERE (t0.USERNM = ? AND t0.FILENM = ? AND t0.auid = ?) [params=(String)
> didier1, (String) index1, (String) auid1]
> 2051 xcap TRACE [main] openjpa.jdbc.SQL - <t 7438914, conn 2016650> [33
> ms] spent
>
>
>
> --
> View this message in context: http://www.nabble.com/native-sql-tf4428366.html#a12650013
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
>
--
Patrick Linskey
202 669 5907
Re: native sql
Posted by dboudigue <di...@alcatel-lucent.fr>.
Patrick, here are the code and traces.
Anyway, an option to skip select in jpl delete or update that would be a
nice feature.
Didier
....
EntityManagerFactory factory =
Persistence.createEntityManagerFactory("xcap", props);
EntityManager em = factory.createEntityManager();
em.getTransaction().begin();
Query delete = em.createQuery("delete from XCAP x where x.user = :user and
x.file = :file and x.auid = :auid");
delete.setParameter("user", user);
delete.setParameter("file", file);
delete.setParameter("auid", auid);
int deleted = delete.executeUpdate();
em.close();
factory.close();
.....
36 xcap INFO [main] openjpa.Runtime - Starting OpenJPA 1.0.0
159 xcap INFO [main] openjpa.jdbc.JDBC - Using dictionary class
"org.apache.openjpa.jdbc.sql.MySQLDictionary".
329 xcap INFO [main] openjpa.Enhance - Creating subclass and redefining
methods for "[class com.alcatel_lucent.jpa.entity.XCap]". This means that
your application will be less efficient than it would if you ran the OpenJPA
enhancer.
2017 xcap TRACE [main] openjpa.jdbc.SQL - <t 7438914, conn 2016650>
executing prepstmnt 21006994 SELECT t0.auid, t0.FILENM, t0.USERNM,
t0.CONTENT_TYPE, t0.DOC, t0.DOC_SZ, t0.etag, t0.MODIFIED_DATE FROM XCAP t0
WHERE (t0.USERNM = ? AND t0.FILENM = ? AND t0.auid = ?) [params=(String)
didier1, (String) index1, (String) auid1]
2051 xcap TRACE [main] openjpa.jdbc.SQL - <t 7438914, conn 2016650> [33
ms] spent
--
View this message in context: http://www.nabble.com/native-sql-tf4428366.html#a12650013
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: native sql
Posted by Patrick Linskey <pl...@gmail.com>.
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 <di...@alcatel-lucent.fr> 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 <di...@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
> >
> >
>
> --
> 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
Re: native sql
Posted by dboudigue <di...@alcatel-lucent.fr>.
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 <di...@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
>
>
--
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.
Re: native sql
Posted by Patrick Linskey <pl...@gmail.com>.
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 <di...@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