[teiid-issues] [JBoss JIRA] (TEIID-3818) Issues with ODBC metadata foreign key queries

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-3818?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13128503#comment-13128503 ] 

Steven Hawkins commented on TEIID-3818:
---------------------------------------

8.12.2 was just made available, which includes the fix - http://teiid.blogspot.com/2015/11/teiid-8122-available.html

> Issues with ODBC metadata foreign key queries
> ---------------------------------------------
>
>                 Key: TEIID-3818
>                 URL: https://issues.jboss.org/browse/TEIID-3818
>             Project: Teiid
>          Issue Type: Bug
>          Components: ODBC
>    Affects Versions: 8.4
>         Environment: Most likely not OS / hardware dependent.
>            Reporter: Dave Nicodemus
>            Assignee: Steven Hawkins
>            Priority: Critical
>             Fix For: 8.12.2, 8.13
>
>
> 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 : 
> {code}
> [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'
> {code}
> Due to this query being generated by the BI tool : 
> {code} 
> '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'
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)