[teiid-issues] [JBoss JIRA] (TEIID-3818) Microsoft's Power BI desktop using ODBC uses unsupported name type

[Steven Hawkins (JIRA)]

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

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

This is effectively a metadata query for foreign key information.  Looking at the 9.0 driver code this query should be getting the exported keys.  We do have handling in our layer, but it looks to only account for imported keys.  We'll resolve this with handling specific to the metadata query, rather than adding more general pg support.

> Microsoft's Power BI desktop using ODBC uses unsupported name type
> ------------------------------------------------------------------
>
>                 Key: TEIID-3818
>                 URL: https://issues.jboss.org/browse/TEIID-3818
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: ODBC
>    Affects Versions: 8.11.5
>         Environment: Most likely not OS / hardware dependent.
>            Reporter: Dave Nicodemus
>            Assignee: Steven Hawkins
>
> Full history in this thread https://developer.jboss.org/message/945727#945727
> 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}
> Apparently due to:
> {quote}
> Our pg layer isn't currently handling the name type.  It's not a full pg emulation, rather it's targeted at supporting as much of Teiid SQL as possible.  So this would need to be captured as an issue.
> {quote}



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