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

Issues with ODBC metadata foreign key queries

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Done
    • Affects Version/s: 8.4
    • Fix Version/s: 8.7.2.6_2, 8.12.2, 8.13, 8.7.6
    • Component/s: ODBC
    • Labels:
      None
    • Environment:

      Most likely not OS / hardware dependent.

      Description

      The current foreign key handling should return imported key, but instead returns exported keys.

      When exported keys are asked for an exception is thrown:

      While attempting to use Client Microsoft's Power BI desktop using ODBC connecting to Teiid Embedded server using VDB with metadata tags.

      Received the following errors :

      [36.864]ERROR from backend during send_query: 'SERROR'
      [36.865]ERROR from backend during send_query: 'C50000'
      [36.869]ERROR from backend during send_query: 'MTEIID31100 Parsing error: Encountered "('ScottTestMart' AS [*]name[*]) as" at line 1, column 33.
      Was expecting: "string" | "varbinary" | "varchar" | "boolean" | "byte" | "tinyint" | "short" | "smallint" | "char" | "integer" ...'
      [36.875]ERROR from backend during send_query: 'Dorg.teiid.jdbc.TeiidSQLException: TEIID31100 Parsing error: Encountered "('ScottTestMart' AS [*]name[*]) as" at line 1, column 33.
      Was expecting: "string" | "varbinary" | "varchar" | "boolean" | "byte" | "tinyint" | "short" | "smallint" | "char" | "integer" ...'
      [37.397]STATEMENT ERROR: func=PGAPI_ForeignKeys, desc='', errnum=1, errmsg='PGAPI_ForeignKeys query error'
      

      Due to this query being generated by the BI tool :

       
      'select 'ScottTestMart'::name as PKTABLE_CAT,
      n2.nspname as PKTABLE_SCHEM,
      c2.relname as PKTABLE_NAME,
      a2.attname as PKCOLUMN_NAME,
      'ScottTestMart'::name as FKTABLE_CAT,
      n1.nspname as FKTABLE_SCHEM,
      c1.relname as FKTABLE_NAME,
      a1.attname as FKCOLUMN_NAME,
      i::int2 as KEY_SEQ,
      case ref.confupdtype
      when 'c' then 0::int2
      when 'n' then 2::int2
      when 'd' then 4::int2
      when 'r' then 1::int2
      else 3::int2
      end as UPDATE_RULE,
      case ref.confdeltype
      when 'c' then 0::int2
      when 'n' then 2::int2
      when 'd' then 4::int2
      when 'r' then 1::int2
      else 3::int2
      end as DELETE_RULE,
      ref.conname as FK_NAME,
      cn.conname as PK_NAME,
      case
      when ref.condeferrable then
      case
      when ref.condeferred then 5::int2
      else 6::int2
      end
      else 7::int2
      end as DEFERRABLITY
      from
      ((((((( (select cn.oid, conrelid, conkey, confrelid, confkey,
      generate_series(array_lower(conkey, 1), array_upper(conkey, 1)) as i,
      confupdtype, confdeltype, conname,
      condeferrable, condeferred
        from pg_catalog.pg_constraint cn,
      pg_catalog.pg_class c,
      pg_catalog.pg_namespace n
        where contype = 'f'
        and confrelid = c.oid
        and relname = 'Categories'
        and n.oid = c.relnamespace
        and n.nspname = 'ScottTestMart'
      ) ref
      inner join pg_catalog.pg_class c1
        on c1.oid = ref.conrelid)
      inner join pg_catalog.pg_namespace n1
        on n1.oid = c1.relnamespace)
      inner join pg_catalog.pg_attribute a1
        on a1.attrelid = c1.oid
        and a1.attnum = conkey[i])
      inner join pg_catalog.pg_class c2
        on c2.oid = ref.confrelid)
      inner join pg_catalog.pg_namespace n2
        on n2.oid = c2.relnamespace)
      inner join pg_catalog.pg_attribute a2
        on a2.attrelid = c2.oid
        and a2.attnum = confkey[i])
      left outer join pg_catalog.pg_constraint cn
        on cn.conrelid = ref.confrelid
        and cn.contype = 'p')
        order by ref.oid, ref.i'
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                dnicodemus Dave Nicodemus
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: