Re: OpenJPA problems running against PG 8.1

Apologies - posted under wrong thread. Will re-post. (Monday morning
syndrome :-/ )
Andrew

On 03/10/11 10:31, Andrew Hastie wrote:
> Posting here for info only as I don't believe this is a "bug" as such,
> rather a change in behaviour:-
>
> Latest 2.1.1 stable version of Apache OpenJPA project appears broken
> when running against Postgres when upgraded to v9.1. This I think is
> down to the changes in escape string handling in SQL LIKE clauses.
> Here's a link to the bug I've lodged against OpenJPA which includes
> details of how to configure an override at runtime to the default
> Postgres DBDictionary setting which will get things working again:-
>
>
> http://openjpa.208410.n2.nabble.com/Postgres-V9-1-issue-with-LIKE-clause-and-Escape-Strings-td6848069.html
>
> Also copying here for info just in case:-
>
> =======================================================
> 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.
> =======================================================
>
> Andrew
>