EM Desktop 5.3 : trying to use an ODBC connector in SQL:2003 configuration

#1

Hello,

I have an ODBC connector with no known type by Easymorph (in my case : Denodo VQL) so I’m trying SQL:2003 to make it work. In visual mode I get this message, probably related to the easymorph feature limiting result to a number of rows. Can you tell me what this is ?

image

Does it mean all my views must implement an “easymorph_row_number” so that Easymorph can work ? Looks like a very strong constraint. If it’s the case, is there any possible workaround ? Like disabling this feature and letting us set a parameter in the connection settings to limite number of rows for each query ?

#2

Try using the “Other SQL” dialect instead of “SQL:2003” because the latter uses specific data views for obtaining table and field metadata.

#3

I can’t : other sql is not compatible with visual mode and we need it.

#4

Unfortunately, Denodo doesn’t use a common SQL dialect such as ANSI SQL or Postgres. To use the visual queries in EasyMorph we would have to add support for Denodo SQL. We can do this relatively quickly if someone can give us access to a test instance of Denodo.

#5

Hi Romain,

The easymorph_row_number column is declared in the SELECT query, created by EasyMorph for the SQL:2003 dialect. Yes, it’s used to limit the number of rows. And since the result preview always limits the numero of displayed rows, you are getting this error even if the “Keep only first rows” option is turned off.

As a workaround, you can try to choose another SQL dialect in the used connector. Maybe some of the supported dialects are better suited for the Denodo VQL.

I see that Denodo VQL uses the LIMIT keyword to limit the number of rows in a result. Other dialects that support the LIMIT keyword and are thus worth trying: MySQL, PostgreSQL and SQLite. Also some of the less common dialects support that keyword as well: Athena, Exasol, Hive, SapHana, Vertica.