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

CONNECT TBL incorrectly applying default database to ODBC sub-table

    XMLWordPrintable

Details

    Description

      I have three tables.

      Two are CONNECT CSV tables coming from text files.

      The third is a CONNECT ODBC displaying data from a view on a sql server database.

      I can create a CONNECT TBL table and it works fine for the two csv type tables. However when I add the ODBC table to it things break down.

      The TBL table is created but when you attempt to select all from it, you are informed that the view cannot be found from the ODBC table. The reason the view cannot be found is that the TBL table is ignoring the server and database specified in the ODBC table and is instead forcing the default database onto the query. The view of course does not exist in the default local database.

      **The following works just fine:

      CREATE TABLE odbc_test ENGINE=CONNECT CHARSET=latin1 CONNECTION='Driver=SQL Server Native Client 11.0;Server=myServer;UID=myUser;PWD=123;Database=MyDatabase;' `TABLE_TYPE`='ODBC' tabname='myView' READONLY=1;
       
      select * from odbc_test;
      

      **The following also works:

      CREATE TABLE test1_csv (
      id int NOT NULL,
      species varchar(255) NOT NULL
      ) ENGINE=CONNECT TABLE_TYPE='CSV' 
      FILE_NAME='C:/MariaDB 10.1/data/demo/test1.txt'
      SEP_CHAR='\t' QCHAR='"' QUOTED=0 HEADER=1;
       
      CREATE TABLE test2_csv (
      id int NOT NULL,
      species varchar(255) NOT NULL
      ) ENGINE=CONNECT TABLE_TYPE='CSV' 
      FILE_NAME='C:/MariaDB 10.1/data/demo/test2.txt'
      SEP_CHAR='\t' QCHAR='"' QUOTED=0 HEADER=1;
      

      **If I create a TBL based on the two CSV tables it works fine:

      create table tbl_demo (
        	tabname char(50) not null special='TABID',
      	id int NOT NULL,
      	species varchar(255) NOT NULL
      	)
      engine=CONNECT table_type=TBL table_list='test1_csv,test2_csv' OPTION_LIST='thread=1';
       
      SELECT * FROM tbl_demo;
      

      **However as soon as I add the ODBC table to the mix, the select query fails and informs me that "dev.myView" does not exist. "dev" is the local db, it is ignoring the fact that the ODBC table has a differnt server and database "MyDatabase" specified:

      create table tbl_demo (
        	tabname char(50) not null special='TABID',
      	id int NOT NULL,
      	species varchar(255) NOT NULL
      	)
      engine=CONNECT table_type=TBL table_list='test1_csv,test2_csv,odbc_test' OPTION_LIST='thread=1';
       
      SELECT * FROM tbl_demo;
      

      I have tested every combination of options that I can think of but cannot get this to work, so I have come to the conclusion that it may be a bug.

      Attachments

        Activity

          People

            bertrandop Olivier Bertrand
            boneill81 Barry O' Neill
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.