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

Issues with ODBC metadata foreign key queries

    Details

      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: