Error Connecting to Teiid (9.0.1) via ODBC

[jrod2016]

Hi,

I get the following error while connecting to Teiid via odbc:

DataSource.Error: ODBC: ERROR [50000] ERROR: TEIID31100 Parsing error: Encountered "[*]from[*] \"adfcorevdb\".\"TestDallas\".\"Product_Catalog\"" at line 1, column 1.
Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...
org.teiid.jdbc.TeiidSQLException: TEIID31100 Parsing error: Encountered "[*]from[*] \"adfcorevdb\".\"TestDallas\".\"Product_Catalog\"" at line 1, column 1.
Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" |
ERROR [50000] "merge" ...;
Error while executing the query
Details:
    DataSourceKind=Odbc
    DataSourcePath=dsn=PostgreSQL35W
    OdbcErrors=Table

I have installed the 64-bit Windows odbc driver as specified here: Chapter 7. ODBC Support

I am using PowerBI as a client. The problem seems to be that the odbc client is issuing a partial sql command while previewing the View

So, the preview is issuing

" from adfcorevdb.TestDallas.Product_Catalog"

instead of

"Select * from adfcorevdb.TestDallas.Product_Catalog"

Any suggestions/ideas? Could this be a driver issue?

Thanks,

John

[rareddy]

What kind ODBC client did you use in PowerBI? You need to setup as PostgreSQL.

Teiild does not support partial statements like " from ..." it needs to be full form. If there an option in BI to avoid the partial queries then you need to set that one up.

Ramesh..

[jrod2016]

I have set it up as PostgreSQL, this is what my settings look like:

[rareddy]

" from adfcorevdb.TestDallas.Product_Catalog" is not right ANSI-SQL syntax, so you need to find the option in the PowerBI to turn off the simplification of the query.

[jrod2016]

I was able to find a solution for this. The issue was the Teiid View name had an underscore in it. This was throwing off the PowerBI engine which introspectively determines column names. Renamed the view and all is well. Incidentally PowerBI and Excel share the same engine to do previews on ODBC sources. So, Excel was failing here as well.

Spent all day yesterday debugging this. Hope this would be useful to someone else.

[rareddy]

Nice. Thanks for the info.

[shawkins]

Just wanted to follow up with more on this:

The issue with Teiid pg/ODBC metadata and tables with '_' in the name likely stems from the differing expectations of the LIKE escape charcter.  PostgreSQL and many of it's clients assume their default escape character of '\' while Teiid assumes the ansi sql compliant no default escape character.  That is an issue for metadata queries with predicates similar to "col like 'name\_with\_underscore'".  There are both a session property and a server wide property to setting the default like escape character behavior - see https://teiid.gitbooks.io/documents/content/client-dev/ODBC_Support.html

[marimar2000]

Hello,

I have a similar error.

In my case the DSN, a file and tables, all contain '_'

Do you know what I can do in this case?

Thank you

[shawkins]

Set the system propery org.teiid.backslashDefaultMatchEscape or rename the Teiid tables to not contain _ and leave the nameInSource property as the actual source name.