From dev-return-19564-apmail-openjpa-dev-archive=openjpa.apache.org@openjpa.apache.org Tue Oct 4 10:54:58 2011 Return-Path: X-Original-To: apmail-openjpa-dev-archive@www.apache.org Delivered-To: apmail-openjpa-dev-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 996797C3B for ; Tue, 4 Oct 2011 10:54:58 +0000 (UTC) Received: (qmail 6686 invoked by uid 500); 4 Oct 2011 10:54:58 -0000 Delivered-To: apmail-openjpa-dev-archive@openjpa.apache.org Received: (qmail 6660 invoked by uid 500); 4 Oct 2011 10:54:58 -0000 Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@openjpa.apache.org Delivered-To: mailing list dev@openjpa.apache.org Received: (qmail 6652 invoked by uid 99); 4 Oct 2011 10:54:58 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 04 Oct 2011 10:54:58 +0000 X-ASF-Spam-Status: No, hits=-2000.5 required=5.0 tests=ALL_TRUSTED,RP_MATCHES_RCVD X-Spam-Check-By: apache.org Received: from [140.211.11.116] (HELO hel.zones.apache.org) (140.211.11.116) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 04 Oct 2011 10:54:55 +0000 Received: from hel.zones.apache.org (hel.zones.apache.org [140.211.11.116]) by hel.zones.apache.org (Postfix) with ESMTP id E67BE2A8140 for ; Tue, 4 Oct 2011 10:54:33 +0000 (UTC) Date: Tue, 4 Oct 2011 10:54:33 +0000 (UTC) From: "Andrew Hastie (Commented) (JIRA)" To: dev@openjpa.apache.org Message-ID: <1511603075.6804.1317725673946.JavaMail.tomcat@hel.zones.apache.org> In-Reply-To: <1842559610.6791.1317724954046.JavaMail.tomcat@hel.zones.apache.org> Subject: [jira] [Commented] (OPENJPA-2056) Postgres V9.1 issue with LIKE clause and Escape Strings MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 X-Virus-Checked: Checked by ClamAV on apache.org [ https://issues.apache.org/jira/browse/OPENJPA-2056?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13120000#comment-13120000 ] Andrew Hastie commented on OPENJPA-2056: ---------------------------------------- I think there are several solutions to this problem, but ideally we need something that is backwards compatible with previous Postgres releases. I'm no expect on the internals of OpenJPA, but here are some suggestions: 1. Determine from the current connection the setting of "standard_conforming_strings" (Needs SQL statement "SHOW standard_conforming_strings;"). This can then be used to influence the escape string to being either "\\" or "\\\\" in the Dictionary impl as required. ASFAIK Postgres at V9.1 returns "ON" and earlier releases return "OFF". Here are some links to the Postgres documentation covering this topic:- http://www.postgresql.org/docs/9.1/interactive/runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS http://www.postgresql.org/docs/9.1/interactive/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE 2. Use an explicitly escaped string like E'\\' to force escape interpretation, making the string independent of the standards_conforming_strings param. Until this is fixed, there are several workarounds for the problem:- 1. Set a property in the persistence.xml as follows to override the default PGDictionary setting:- 2. In the Postgres configuration file (postgresql.conf) make the setting under "VERSION/PLATFORM COMPATIBILITY" :- standard_conforming_strings = off Option 1 is preferred as not all users may have access to the Postgres configuration files. > Postgres V9.1 issue with LIKE clause and Escape Strings > ------------------------------------------------------- > > Key: OPENJPA-2056 > URL: https://issues.apache.org/jira/browse/OPENJPA-2056 > Project: OpenJPA > Issue Type: Bug > Components: jdbc > Affects Versions: 2.0.0, 2.0.1, 2.1.1 > Environment: Running against a Postgres database at version 9.1 > Reporter: Andrew Hastie > > Noticed an issue with the Postgres DBDictionary definition after updating Postgres from version 8.4 to 9.1:- > Here's what you get in the Postgres trace file when executing some JPA driven queries where an SQL LIKE is involved:- > 2011-09-30 14:29:41 BST ERROR: invalid escape string > 2011-09-30 14:29:41 BST HINT: Escape string must be empty or one character. > 2011-09-30 14:29:41 BST STATEMENT: SELECT t0.id, t0.identificationMask, t0.productName FROM DBTYPE t0 WHERE (t0.identificationMask LIKE $1 ESCAPE '\\') > 2011-09-30 14:29:41 BST ERROR: current transaction is aborted, commands ignored until end of transaction block > This appears to be down to a change the Postgres project have made to escape string handling:- > http://www.postgresql.org/docs/9.1/static/release-9-1.html (see section E.2.2.1) > You appear to be able to override the default DBDictionary setting for this as follows to get things working again:- > > So, does this mean OpenJPA needs a version dependent dictionary definition for Postgres from now on? Anybody got any better solutions or care to confirm what I'm seeing? > I've also posted this to the Postgres JDBC mailing list in case they have any comments. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira