Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: 10.0.7
-
Component/s: None
-
Labels: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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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.
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.
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.