openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Milosz Tylenda (Commented) (JIRA)" <>
Subject [jira] [Commented] (OPENJPA-2056) Postgres V9.1 issue with LIKE clause and Escape Strings
Date Sun, 09 Oct 2011 11:30:29 GMT


Milosz Tylenda commented on OPENJPA-2056:

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.
> Postgres V9.1 issue with LIKE clause and Escape Strings
> -------------------------------------------------------
>                 Key: OPENJPA-2056
>                 URL:
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc, sql
>    Affects Versions: 2.0.0, 2.0.1, 2.1.1
>         Environment: Running against a Postgres database at version 9.1
>            Reporter: Andrew Hastie
>            Assignee: Milosz Tylenda
>             Fix For: 2.2.0
> 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.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:-
> (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:-
> <property name="openjpa.jdbc.DBDictionary" value="postgres(SearchStringEscape=\)"/>
> 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:!default.jspa
For more information on JIRA, see:


View raw message