Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
10.0.10
-
None
Description
If two tables having the same name present in more than one
different schemas, then discovery does not work.
For example, Cache in its default installation has the following
tables with the name "Person" in multiple demo schemas:
+-----------------+-------------+------------+------------+-----------+
|
| Table_Qualifier | Table_Owner | Table_Name | Table_Type | Remark |
|
+-----------------+-------------+------------+------------+-----------+
|
| | BasTutorial | Person | TABLE | |
|
| | CosTutorial | Person | TABLE | |
|
| | SQLUser | Person | TABLE | |
|
| | Sample | Person | TABLE | This s... |
|
| | Wasabi_Data | Person | TABLE | |
|
| | ZENMVC | Person | TABLE | Simple .. |
|
+-----------------+-------------+------------+------------+-----------+
|
So this query:
create table t1 tabname='person' engine=CONNECT table_type=ODBC connection='DSN=Samplesunixodbc'; |
returns an error:
ERROR 1939 (HY000): Engine CONNECT failed to discover table `test`.`t1` with 'CREATE TABLE whatever (`ID` INT(10) NOT NULL COMMENT 'ID',`DOB` DATE COMMENT 'DOB',`FirstName` VARCHAR(50) COMMENT 'FirstName',`LastName` VARCHAR(50) COMMENT 'LastName',`Name` VARCHAR(50) COMMENT 'Name',`Phone` VARCHAR(12) COMMENT 'Phone',`ID` INT(10) NOT NULL COMMENT 'ID',`DOB` DATE COMMENT 'DOB',`FirstName` VARCHAR(50) COMMENT 'FirstName',`LastName` VARCHAR(50) COMMENT 'LastName',`Name` VARCHAR(50) COMMENT 'Name',`Phone` VARCHAR(12) COMMENT 'Phone
|
because the ODBC call for SQLColumns() returns columns for all tables
"Person" from all these six schemas, and some of them have duplicate names.
The same problem happens with Oracle DSN:
create table t1 tabname='COUNTRIES' engine=CONNECT table_type=ODBC connection='DSN=oraodbc;UID=system;PWD=manager'; |
ERROR 1939 (HY000): Engine CONNECT failed to discover table `test`.`t1` with 'CREATE TABLE whatever (`COUNTRY_ID` CHAR(2) NOT NULL,`COUNTRY_NAME` VARCHAR(40),`REGION_ID` DOUBLE(40,0),`COUNTRY_ID` VARCHAR(10)) TABLE_TYPE='ODBC' TABNAME='COUNTRIES' CONNECTION='DSN=oraodbc;UID=system;PWD=manager''
|
if table "COUNTRIES" present in two schemas. Notice REGION_ID specified twice in the above error message.
The above error message is confusing. We need to solve this somehow.
Possible ways:
1. Detect the default schema name.
If schema name is not specified by the user,
then we can try to get to know the default schema
name before doing SQLColumn().
Unfortunately, it seems there are no ways to fetch current schema in the ODBC API.
Neither are portable SQL ways to get current schema.
It's very different between databases:
- MySQL:
SELECT DATABASE(); - PostgreSQL supports the SQL standard way:
SELECT CURRENT_SCHEMA; - Oracle:
SELECT SYS_CONTEXT('userenv','current_schema') x FROM dual; - Cache assumes "SQLUser" schema by default when schema is not
specified explicitly.
We can try to check the database type and run one
of the above SQL queries to know the default schema name.
But this, probably, sounds too complex.
2. Or just print a better error message.
The discovery code could make sure that schema
name in the result of SQLColumns() is the same for all columns.
In case if schema name is not the same, then print an error like this:
ERROR 1939 (HY0000): Table name "Person" presents in more than one schemas ('SAMPLE' and 'SQLUSer'). Use a qualified table name (e.g. 'Sample.Person' to disambiguate.
|
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-