Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17212

Connect Engine ODBC Table_Type - Discovery Process

    XMLWordPrintable

    Details

      Description

      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?

        Attachments

          Activity

            People

            • Assignee:
              bertrandop Olivier Bertrand
              Reporter:
              Juan Juan Telleria
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: