OpenJPA
  1. OpenJPA
  2. OPENJPA-2056

Postgres V9.1 issue with LIKE clause and Escape Strings

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.0, 2.0.1, 2.1.1
    • Fix Version/s: 2.2.0
    • Component/s: jdbc, sql
    • Labels:
      None
    • Environment:
      Running against a Postgres database at version 9.1

      Description

      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.

        Activity

        Albert Lee made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Milosz Tylenda made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 2.2.0 [ 12315910 ]
        Resolution Fixed [ 1 ]
        Milosz Tylenda made changes -
        Component/s sql [ 12311310 ]
        Milosz Tylenda made changes -
        Field Original Value New Value
        Assignee Milosz Tylenda [ milosz ]
        Andrew Hastie created issue -

          People

          • Assignee:
            Milosz Tylenda
            Reporter:
            Andrew Hastie
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development