You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Andrew Hastie <an...@ahastie.net> on 2011/09/30 16:44:07 UTC
Postgres V9.1 issue with LIKE clause and Escape Strings
Hi,
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
Re: Postgres V9.1 issue with LIKE clause and Escape Strings
Posted by Andrew Hastie <an...@ahastie.net>.
JIRA incident raised:-
https://issues.apache.org/jira/browse/OPENJPA-2056
Andrew
Re: Postgres V9.1 issue with LIKE clause and Escape Strings
Posted by Andrew Hastie <an...@ahastie.net>.
Hi Milosz (replying off list),
Yep, happy to raise on Jira. Let me do a bit more research on possible
workarounds and to get some more info from the Postgres JDBC project.
Hopefully get something posted in next couple of days.
Regards,
Andrew
On 03/10/11 19:37, Miłosz Tylenda wrote:
> Hi Andrew,
>
> You are right, the PostgreSQL dictionary needs an update. Thanks for the investigation. Wouldn't you mind opening a JIRA issue [1] for that? Otherwise I will open one.
>
> Greetings,
> Milosz
>
> [1] https://issues.apache.org/jira/browse/OPENJPA
>
>
>> Hi,
>>
>> 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
>>
>>
Re: Postgres V9.1 issue with LIKE clause and Escape Strings
Posted by "ringerc@ringerc.id.au" <ri...@ringerc.id.au>.
That's to do with standards_confirming_strings; \ is no longer an escape by
default, so '\\' is being interpreted as literally \\ instead of an escaped
'\'. Until OpenJPA is updated to recognise this, just SET
standards_conforming_strings=off or configure it in postgresql.conf .
You can use an explicitly escaped string like E'\\' to force escape
interpretation, making the string independent of the
standards_conforming_strings param.
Yes, I know this is horrible. Pg didn't conform to the standard, and the
transition is painful.
(I'm not subscribed, BTW, so reply all for any followup).
--
View this message in context: http://openjpa.208410.n2.nabble.com/Postgres-V9-1-issue-with-LIKE-clause-and-Escape-Strings-tp6848069p6857386.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Postgres V9.1 issue with LIKE clause and Escape Strings
Posted by Miłosz Tylenda <mt...@o2.pl>.
Hi Andrew,
You are right, the PostgreSQL dictionary needs an update. Thanks for the investigation. Wouldn't you mind opening a JIRA issue [1] for that? Otherwise I will open one.
Greetings,
Milosz
[1] https://issues.apache.org/jira/browse/OPENJPA
> Hi,
>
> 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
>
>