Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-2138

PG DSN for for ODBC metadata query LIKE issues

    XMLWordPrintable

    Details

    • Type: Quality Risk
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 7.7
    • Fix Version/s: 8.2
    • Component/s: ODBC, Query Engine
    • Labels:
      None
    • Affects:
      Documentation (Ref Guide, User Guide, etc.)

      Description

      Various clients (Cognos, Excel, DBVisualizer) exhibit different results when querying metadata.

      For instance:
      Querying ModeShape on a fresh 5.3 deploy.

      select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\\_alterable' and n.oid = relnamespace
      

      0 Records

      select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl_alterable' and n.oid = relnamespace
      

      1 Record: ddl_alterable

      select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\_alterab__' and n.oid = relnamespace
      

      1 Record: ddl_alterable

      Looking at [1] per [2]. It seems proper escaping of the E'literal' syntax is not always being performed. The Third example above shows that a wildcard is being escaped and happens to match to a literal value it expects.

      [1]
      PostgreSQL also accepts "escape" string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g. E'foo'. (When continuing an escape string constant across lines, write E only before the first opening quote.) Within an escape string, a backslash character () begins a C-like backslash escape sequence, in which the combination of backslash and following character(s) represents a special byte value. \b is a backspace, \f is a form feed, \n is a newline, \r is a carriage return, \t is a tab. Also supported are \digits, where digits represents an octal byte value, and \xhexdigits, where hexdigits represents a hexadecimal byte value. (It is your responsibility that the byte sequences you create are valid characters in the server character set encoding.) Any other character following a backslash is taken literally. Thus, to include a backslash character, write two backslashes (
      ). Also, a single quote can be included in an escape string by writing \', in addition to the normal way of ''.

      [2] http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                jolee Johnathon Lee
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: