Hi Patrick -
Good memory, that I am on SQLite.
Do you think OpenJPA will ship with a preconfigured DBDictionary for
SQLite at some point?
-Marc
On Nov 27, 2007 12:32 AM, Patrick Linskey <[EMAIL PROTECTED]> wrote:
> Hi,
>
> It sounds like SQLite does not allow table aliases in DELETE
> statments. The allowsAliasInBulkClause DBDictionary property controls
> this.
>
> I believe that the DBDictionary behavior is overly-cautious when this
> is set to false, but it should safely get things working.
>
> -Patrick
>
>
> On 11/27/07, Marc Siegel <[EMAIL PROTECTED]> wrote:
> > Hi Folks,
> >
> > Background:
> > This is with OpenJPA 1.0. Java 1.5.
> >
> > The following query succeeds as a SELECT:
> > String jpql = "SELECT t FROM GaTextAd t " +
> > "WHERE t.isChanged = FALSE " +
> > "AND t.isDisapproved = TRUE " +
> > "AND t.googleId IS NULL";
> >
> > But when the same WHERE clause is used to DELETE:
> > String jpql = "DELETE FROM GaTextAd t " +
> > "WHERE t.isChanged = FALSE " +
> > "AND t.isDisapproved = TRUE " +
> > "AND t.googleId IS NULL";
> >
> >
> > It provokes the following error:
> > <openjpa-1.0.0-r420667:568756 nonfatal general error>
> > org.apache.openjpa.persistence.PersistenceException: near "t0": syntax
> > error {DELETE FROM GaTextAd t0 WHERE (t0.isChanged = ? AND
> > t0.isDisapproved = ? AND t0.googleId IS NULL)} [code=0, state=null]
> > FailedObject: [EMAIL PROTECTED]
> > 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:67)
> > at
> > org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeBulkOperation(JDBCStoreQuery.java:512)
> > at
> > org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeDelete(JDBCStoreQuery.java:425)
> > at
> > org.apache.openjpa.kernel.ExpressionStoreQuery$DataStoreExecutor.executeDelete(ExpressionStoreQuery.java:681)
> > at org.apache.openjpa.kernel.QueryImpl.delete(QueryImpl.java:1008)
> > at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:801)
> > at org.apache.openjpa.kernel.QueryImpl.deleteAll(QueryImpl.java:866)
> > at org.apache.openjpa.kernel.QueryImpl.deleteAll(QueryImpl.java:862)
> > at
> > org.apache.openjpa.kernel.DelegatingQuery.deleteAll(DelegatingQuery.java:541)
> > at
> > org.apache.openjpa.persistence.QueryImpl.executeUpdate(QueryImpl.java:314)
> > at com.leadgenllc.lib.GaPush.deleteAllRejected(GaPush.java:403)
> > at
> > com.leadgenllc.lib.GaPushTest.testRejectTextAds(GaPushTest.java:46)
> > 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 org.junit.internal.runners.TestMethod.invoke(TestMethod.java:59)
> > at
> > org.junit.internal.runners.MethodRoadie.runTestMethod(MethodRoadie.java:98)
> > at
> > org.junit.internal.runners.MethodRoadie$2.run(MethodRoadie.java:79)
> > at
> > org.junit.internal.runners.MethodRoadie.runBeforesThenTestThenAfters(MethodRoadie.java:87)
> > at
> > org.junit.internal.runners.MethodRoadie.runTest(MethodRoadie.java:77)
> > at org.junit.internal.runners.MethodRoadie.run(MethodRoadie.java:42)
> > at
> > org.junit.internal.runners.JUnit4ClassRunner.invokeTestMethod(JUnit4ClassRunner.java:88)
> > at
> > org.junit.internal.runners.JUnit4ClassRunner.runMethods(JUnit4ClassRunner.java:51)
> > at
> > org.junit.internal.runners.JUnit4ClassRunner$1.run(JUnit4ClassRunner.java:44)
> > at
> > org.junit.internal.runners.ClassRoadie.runUnprotected(ClassRoadie.java:27)
> > at
> > org.junit.internal.runners.ClassRoadie.runProtected(ClassRoadie.java:37)
> > at
> > org.junit.internal.runners.JUnit4ClassRunner.run(JUnit4ClassRunner.java:42)
> > at
> > org.apache.maven.surefire.junit4.JUnit4TestSet.execute(JUnit4TestSet.java:62)
> > at
> > org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.executeTestSet(AbstractDirectoryTestSuite.java:138)
> > at
> > org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.execute(AbstractDirectoryTestSuite.java:125)
> > at org.apache.maven.surefire.Surefire.run(Surefire.java:132)
> > 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
> > org.apache.maven.surefire.booter.SurefireBooter.runSuitesInProcess(SurefireBooter.java:290)
> > at
> > org.apache.maven.surefire.booter.SurefireBooter.main(SurefireBooter.java:818)
> > Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: near
> > "t0": syntax error {DELETE FROM GaTextAd t0 WHERE (t0.isChanged = ?
> > AND t0.isDisapproved = ? AND t0.googleId IS NULL)} [code=0,
> > state=null]
> > at
> > org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:201)
> > at
> > org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$000(LoggingConnectionDecorator.java:57)
> > at
> > org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:228)
> > at
> > org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:163)
> > at
> > org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:140)
> > at
> > org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:163)
> > at
> > org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1308)
> > at
> > org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:152)
> > at
> > org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:475)
> > at
> > org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:455)
> > at
> > org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:444)
> > at
> > org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeBulkOperation(JDBCStoreQuery.java:504)
> > ... 36 more
> > NestedThrowables:
> > java.sql.SQLException: near "t0": syntax error
> > at org.sqlite.DB.throwex(DB.java:252)
> > at org.sqlite.NativeDB.prepare(Native Method)
> > at org.sqlite.DB.prepare(DB.java:62)
> > at org.sqlite.PrepStmt.<init>(PrepStmt.java:25)
> > at org.sqlite.Conn.prepareStatement(Conn.java:205)
> > at org.sqlite.Conn.prepareStatement(Conn.java:198)
> > at org.sqlite.Conn.prepareStatement(Conn.java:187)
> > at
> > org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:165)
> > at
> > org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:225)
> > at
> > org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:163)
> > at
> > org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:140)
> > at
> > org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:163)
> > at
> > org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1308)
> > at
> > org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:152)
> > at
> > org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:475)
> > at
> > org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:455)
> > at
> > org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:444)
> > at
> > org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeBulkOperation(JDBCStoreQuery.java:504)
> > at
> > org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeDelete(JDBCStoreQuery.java:425)
> > at
> > org.apache.openjpa.kernel.ExpressionStoreQuery$DataStoreExecutor.executeDelete(ExpressionStoreQuery.java:681)
> > at org.apache.openjpa.kernel.QueryImpl.delete(QueryImpl.java:1008)
> > at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:801)
> > at org.apache.openjpa.kernel.QueryImpl.deleteAll(QueryImpl.java:866)
> > at org.apache.openjpa.kernel.QueryImpl.deleteAll(QueryImpl.java:862)
> > at
> > org.apache.openjpa.kernel.DelegatingQuery.deleteAll(DelegatingQuery.java:541)
> > at
> > org.apache.openjpa.persistence.QueryImpl.executeUpdate(QueryImpl.java:314)
> > at com.leadgenllc.lib.GaPush.deleteAllRejected(GaPush.java:403)
> > at
> > com.leadgenllc.lib.GaPushTest.testRejectTextAds(GaPushTest.java:46)
> > 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 org.junit.internal.runners.TestMethod.invoke(TestMethod.java:59)
> > at
> > org.junit.internal.runners.MethodRoadie.runTestMethod(MethodRoadie.java:98)
> > at
> > org.junit.internal.runners.MethodRoadie$2.run(MethodRoadie.java:79)
> > at
> > org.junit.internal.runners.MethodRoadie.runBeforesThenTestThenAfters(MethodRoadie.java:87)
> > at
> > org.junit.internal.runners.MethodRoadie.runTest(MethodRoadie.java:77)
> > at org.junit.internal.runners.MethodRoadie.run(MethodRoadie.java:42)
> > at
> > org.junit.internal.runners.JUnit4ClassRunner.invokeTestMethod(JUnit4ClassRunner.java:88)
> > at
> > org.junit.internal.runners.JUnit4ClassRunner.runMethods(JUnit4ClassRunner.java:51)
> > at
> > org.junit.internal.runners.JUnit4ClassRunner$1.run(JUnit4ClassRunner.java:44)
> > at
> > org.junit.internal.runners.ClassRoadie.runUnprotected(ClassRoadie.java:27)
> > at
> > org.junit.internal.runners.ClassRoadie.runProtected(ClassRoadie.java:37)
> > at
> > org.junit.internal.runners.JUnit4ClassRunner.run(JUnit4ClassRunner.java:42)
> > at
> > org.apache.maven.surefire.junit4.JUnit4TestSet.execute(JUnit4TestSet.java:62)
> > at
> > org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.executeTestSet(AbstractDirectoryTestSuite.java:138)
> > at
> > org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.execute(AbstractDirectoryTestSuite.java:125)
> > at org.apache.maven.surefire.Surefire.run(Surefire.java:132)
> > 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
> > org.apache.maven.surefire.booter.SurefireBooter.runSuitesInProcess(SurefireBooter.java:290)
> > at
> > org.apache.maven.surefire.booter.SurefireBooter.main(SurefireBooter.java:818)
> >
>
>
> --
> Patrick Linskey
> 202 669 5907
>
