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

ConnectSE: discovery does not work well for Oracle's INT data type

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • None
    • 10.0.7
    • None
    • None

    Description

      Create this table in Oracle:

      [bar@home ~]$ sqlplus
      SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 26 12:37:51 2013
      Copyright (c) 1982, 2011, Oracle.  All rights reserved.
      Enter user-name: system
      Enter password: 
      Connected to:
      Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
      SQL> create table t1 (a int);
      Table created.
      SQL> insert into t1 values (10);
      1 row created.
      SQL> commit;
      Commit complete.

      Now create a ConnectSE ODBC table and try to SELECT from it.
      It does not work:

      mysql> drop table if exists t1; create table t1 engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;Query OK, 0 rows affected (0.00 sec)
       
      Query OK, 0 rows affected (0.12 sec)
       
      ERROR 1296 (HY000): Got error 122 '[Oracle][ODBC]SQL data type out of range <-25>.' from CONNECT

      If the table structure is specified explicitly, it works fine:

      mysql> drop table if exists t1; create table t1 (a int) engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;
      Query OK, 0 rows affected (0.00 sec)
       
      Query OK, 0 rows affected (0.06 sec)
       
      +------+
      | a    |
      +------+
      |   10 |
      +------+
      1 row in set (0.05 sec)

      Note, discovery works fine with VARCHAR columns.
      Create this table on the Oracle side:

      SQL> drop table t1;
      Table dropped.
      SQL> create table t1 (a varchar(10));
      Table created.
      SQL> insert into t1 values ('test');
      1 row created.
      SQL> commit;
      Commit complete.

      Now try to create an ODBC table on the Maria side and read from it:

      mysql> drop table if exists t1; create table t1 engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;
      Query OK, 0 rows affected (0.00 sec)
       
      Query OK, 0 rows affected (0.13 sec)
       
      +------+
      | A    |
      +------+
      | test |
      +------+
      1 row in set (0.06 sec)

      Discovery should be fixed for the Oracle's INT data type.

      Attachments

        Activity

          After the table is created by:

          create table t1 (a int);

          the SQLColumns function (used also by Discovery) returns:

          "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE"
          ------
          "", "SYSTEM", "T1", "A", 3, "DECIMAL", 38, 40, 0, 10, 1, "", "", 3, , 0, 1, "YES"

          38 is too much for an integer, therefore CONNECT discovery now tests the length and if too big automatically change the column type from int to bigint.

          bertrandop Olivier Bertrand added a comment - After the table is created by: create table t1 (a int); the SQLColumns function (used also by Discovery) returns: "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE" ------ "", "SYSTEM", "T1", "A", 3, "DECIMAL", 38, 40, 0, 10, 1, "", "", 3, , 0, 1, "YES" 38 is too much for an integer, therefore CONNECT discovery now tests the length and if too big automatically change the column type from int to bigint.

          Even if the CONNECT table specifies "A bigint(38)" the error described above still occur when doing the select statement.

          bertrandop Olivier Bertrand added a comment - Even if the CONNECT table specifies "A bigint(38)" the error described above still occur when doing the select statement.

          The issue is that Oracle consider "int" as its "number" data type. This is a proprietary type in which numbers are coded internally up to 38 characters. Precision and Scale can be specified but default to 38 and 0.
          CONNECT cannot really know how to translate numbers without knowing their range and size. This could be int, bigint or double.
          When retrieving the result (in the select statement) ODBC provides the SQLBindCOL function, asking the data source to translate the numeric column contains to the proposed type.
          This would be fine except that Oracle, or the Oracle ODBC driver, supports converting the "number" column values to int or double but the conversion to bigint (internally -25) is not supported. Note that this work fine with other drivers.
          One can argue whether this is an Oracle or CONNECT bug but this is irrelevant. A solution must be found that would solve the Oracle problem without penalizing the other drivers.
          Meanwhile, a manual solution is to change the BIGINT column specification to either INT or DOUBLE depending on the range of value the Oracle table column can contain.

          bertrandop Olivier Bertrand added a comment - The issue is that Oracle consider "int" as its "number" data type. This is a proprietary type in which numbers are coded internally up to 38 characters. Precision and Scale can be specified but default to 38 and 0. CONNECT cannot really know how to translate numbers without knowing their range and size. This could be int, bigint or double. When retrieving the result (in the select statement) ODBC provides the SQLBindCOL function, asking the data source to translate the numeric column contains to the proposed type. This would be fine except that Oracle, or the Oracle ODBC driver, supports converting the "number" column values to int or double but the conversion to bigint (internally -25) is not supported. Note that this work fine with other drivers. One can argue whether this is an Oracle or CONNECT bug but this is irrelevant. A solution must be found that would solve the Oracle problem without penalizing the other drivers. Meanwhile, a manual solution is to change the BIGINT column specification to either INT or DOUBLE depending on the range of value the Oracle table column can contain.

          As 38 is also too big for BIGINT, connect now translates numeric types as:
          type = (prec || len > 20) ? TYPE_FLOAT
          : (len > 10) ? TYPE_BIGINT : TYPE_INT;
          Doing so the issue reported above is fixed.
          Note that the error would still occur for a table created as:
          create table t1 (a number(20));
          But this is an Oracle problem, not a CONNECT one.
          In this case the fix will be to manually specify the column type of the connect table.

          bertrandop Olivier Bertrand added a comment - As 38 is also too big for BIGINT, connect now translates numeric types as: type = (prec || len > 20) ? TYPE_FLOAT : (len > 10) ? TYPE_BIGINT : TYPE_INT; Doing so the issue reported above is fixed. Note that the error would still occur for a table created as: create table t1 (a number(20)); But this is an Oracle problem, not a CONNECT one. In this case the fix will be to manually specify the column type of the connect table.

          The last version of CONNECT was added the DECIMAL (TYPE_DECIM) type.
          All decimal, number, numeric columns of ODBC and MYSQL tables are now handled by CONNECT using this type
          that does not loose any digits in the translation.

          bertrandop Olivier Bertrand added a comment - The last version of CONNECT was added the DECIMAL (TYPE_DECIM) type. All decimal, number, numeric columns of ODBC and MYSQL tables are now handled by CONNECT using this type that does not loose any digits in the translation.

          People

            bertrandop Olivier Bertrand
            bar Alexander Barkov
            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.