弁財天

ゴフマン「専門家を信じるのではなく、自分自身で考えて判断せよ」

エスケープ文字は空か1文字でなければなりません update1

ERROR 2011-11-30 00:00:00,466 TurnoverReferersTask:runTask - unexpected exception
<openjpa-2.0.1-r422266:989424 fatal general error> org.apache.openjpa.persistence.PersistenceException: ERROR: エスケープシーケンスが無効です
  Hint: エスケープ文字は空か1文字でなければなりません。 {prepstmnt 220284995 DELETE FROM referer WHERE id IN (SELECT DISTINCT t0.id FROM referer t0 WHERE (t0.excerpt IS NULL OR t0.excerpt LIKE ? ESCAPE '\\')) [params=?]} [code=0, state=22025]
FailedObject: DELETE FROM referer WHERE id IN (SELECT DISTINCT t0.id FROM referer t0 WHERE (t0.excerpt IS NULL OR t0.excerpt LIKE ? ESCAPE '\\')) [java.lang.String]
        at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4832)
        at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4792)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:78)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeBulkOperation(JDBCStoreQuery.java:531)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeDelete(JDBCStoreQuery.java:443)
        at org.apache.openjpa.kernel.ExpressionStoreQuery$DataStoreExecutor.executeDelete(ExpressionStoreQuery.java:758)
        at org.apache.openjpa.kernel.QueryImpl.delete(QueryImpl.java:1023)
        at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:820)
        at org.apache.openjpa.kernel.QueryImpl.deleteAll(QueryImpl.java:886)
        at org.apache.openjpa.kernel.QueryImpl.deleteAll(QueryImpl.java:882)
        at org.apache.openjpa.kernel.DelegatingQuery.deleteAll(DelegatingQuery.java:558)
        at org.apache.openjpa.persistence.QueryImpl.executeUpdate(QueryImpl.java:345)
        at org.apache.roller.weblogger.business.jpa.JPARefererManagerImpl.clearReferrers(JPARefererManagerImpl.java:92)
        at org.apache.roller.weblogger.business.runnable.TurnoverReferersTask.runTask(TurnoverReferersTask.java:125)
        at org.apache.roller.weblogger.business.runnable.RollerTaskWithLeasing.run(RollerTaskWithLeasing.java:64)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
        at java.util.concurrent.FutureTask.run(FutureTask.java:166)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
        at java.lang.Thread.run(Thread.java:679)
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ERROR: エスケープシーケンスが無効です
  Hint: エスケープ文字は空か1文字でなければなりません。 {prepstmnt 220284995 DELETE FROM referer WHERE id IN (SELECT DISTINCT t0.id FROM referer t0 WHERE (t0.excerpt IS NULL OR t0.excerpt LIKE ? ESCAPE '\\')) [params=?]} [code=0, state=22025]
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:273)
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:249)
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$1000(LoggingConnectionDecorator.java:70)
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:1095)
        at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:285)
        at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:285)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1702)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeUpdate(JDBCStoreQuery.java:698)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeBulkOperation(JDBCStoreQuery.java:529)
        ... 17 more
NestedThrowables:
org.postgresql.util.PSQLException: ERROR: エスケープシーケンスが無効です
  Hint: エスケープ文字は空か1文字でなければなりません。
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)
        at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:285)
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:1093)
        at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:285)
        at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:285)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1702)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeUpdate(JDBCStoreQuery.java:698)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeBulkOperation(JDBCStoreQuery.java:529)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeDelete(JDBCStoreQuery.java:443)
        at org.apache.openjpa.kernel.ExpressionStoreQuery$DataStoreExecutor.executeDelete(ExpressionStoreQuery.java:758)
        at org.apache.openjpa.kernel.QueryImpl.delete(QueryImpl.java:1023)
        at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:820)
        at org.apache.openjpa.kernel.QueryImpl.deleteAll(QueryImpl.java:886)
        at org.apache.openjpa.kernel.QueryImpl.deleteAll(QueryImpl.java:882)
        at org.apache.openjpa.kernel.DelegatingQuery.deleteAll(DelegatingQuery.java:558)
        at org.apache.openjpa.persistence.QueryImpl.executeUpdate(QueryImpl.java:345)
        at org.apache.roller.weblogger.business.jpa.JPARefererManagerImpl.clearReferrers(JPARefererManagerImpl.java:92)
        at org.apache.roller.weblogger.business.runnable.TurnoverReferersTask.runTask(TurnoverReferersTask.java:125)
        at org.apache.roller.weblogger.business.runnable.RollerTaskWithLeasing.run(RollerTaskWithLeasing.java:64)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
        at java.util.concurrent.FutureTask.run(FutureTask.java:166)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
        at java.lang.Thread.run(Thread.java:679)

Postgres V9.1 issue with LIKE clause and Escape Strings
OpenJPA OPENJPA-2056 Postgres V9.1 issue with LIKE clause and Escape Strings

  1. Set a property in the persistence.xml as follows to override the default PGDictionary setting:-
    <property name="openjpa.jdbc.DBDictionary" value="postgres(SearchStringEscape=\)"/>
  2. In the Postgres configuration file (postgresql.conf) make the setting under "VERSION/PLATFORM COMPATIBILITY" :-
    standard_conforming_strings = off

Hi Andrew & Craig. Thanks for the extensive information. The escape string now depends on PostgreSQL version:
1. 9.0 and older use '
'.
2. 9.1 and later use '\'.

I expect this to be a good solution for the vast majority of users. Those with PostgreSQL 9.0 and older who have set standard_conforming_strings to ON can use the SearchStringEscape property workaround proposed by Andrew. Also, setting a DBDictionary property called RequiresSearchStringEscapeForLike to false might help in such cases.

1. 9.0 and older use '
'.

2. 9.1 and later use '\'.

投稿されたコメント:

コメント
コメントは無効になっています。