Connect Engine ODBC Table_Type - Discovery Process

Some minor issues when using MariaDB's Connect ODBC Table_Type Engine with Microsoft Parallel Data Warehouse (PDW):

table_options: TABNAME

When using a Table Definition such as the following for importing a Table:

USE db_name;

CREATE OR REPLACE TABLE `t_name_connect`

  ENGINE=CONNECT

  TABLE_TYPE=ODBC 

  CONNECTION='dsn_name'

 OPTION_LIST='UseDSN=Yes,User=db_user_name,Password=********'

  TABNAME='db_name.schema_name.table_name';

CREATE OR REPLACE TABLE t_name_mariadb

AS

  SELECT

    *

  FROM

    t_name_connect;

The Query works perfectly (10/10), and I am able to import my tables easily to MariaDB. However, an observed limitation is that in TABNAME option, I can only specify my table as:

• db_name.schema_name.table_name.
• And not [db name].[schema name].[table name], which is common syntax for database / schema / table with spaces in TRANSACT-SQL.

table_options: SRCDEF

With Microsoft PDW, using "SQL Server Native Client 11.0" it is not possible to use SRCDEF table option, as otherwise, we would obtain error:

"SQLNumResultCols: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Cursor support is not an implemented feature for SQL Server Parallel DataWarehousing TDS endpoint".

This limitation could be avoided with first method, but we are not able create table definitions as the following by means of the Table Discovery Process:

CREATE OR REPLACE TABLE `t_SQLServer_Table`

  ENGINE=CONNECT

  TABLE_TYPE=ODBC 

  CONNECTION='dns_name'

  OPTION_LIST='UseDSN=Yes,User=user_name,Password=*****'

  SRCDEF='

    SELECT

      *

    FROM

      [db_name].[schema_name].[table_name]

  ';

Previous Table definition would only work if we specified manually the Column Definitions.

Datetime Field Precission

Another issue is Datetime Precission (CONNECT Data Types), which can also be handled with querys such as:

CREATE OR REPLACE TABLE `t_name_connect`

(

  `Col_Name` VARCHAR(255)

)

  ENGINE=CONNECT

  TABLE_TYPE=ODBC 

  CONNECTION='dsn_name'

  OPTION_LIST='UseDSN=Yes,User=user_name,Password=******'

  TABNAME='db_name.sch_namel.tb_name';

CREATE OR REPLACE TABLE t_name_mariadb

AS

  SELECT

    STR_TO_DATE(

      `Col_Name`,

      '%Y-%m-%d %H:%i:%S.%f' /* datetime2: YYYY-MM-DD HH:MM:SS.fff */

    ) AS `Col_Name`

  FROM

    `t_name_connect`;

Although a little verbose, works.

So:

1. Could TABNAME accept `[`, `]` Characters?
2. Any way to make work SRCDEF with Microsoft PDW and the Discovery Process?
3. Any more comfortable way to handle dates in the way shown without losing precision?