openjpa-dev mailing list archives

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

    [ https://issues.apache.org/jira/browse/OPENJPA-2056?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13123664#comment-13123664
] 

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: https://issues.apache.org/jira/browse/OPENJPA-2056
>             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.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:-
> <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: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message