[MDEV-5340] ConnectSE: discovery does not work well for Oracle's INT data type Created: 2013-11-26  Updated: 2013-12-29  Resolved: 2013-12-20

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: 10.0.7

Type: Bug Priority: Critical
Reporter: Alexander Barkov Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
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.

 Comments   
Comment by Olivier Bertrand [ 2013-12-20 ]

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.

Comment by Olivier Bertrand [ 2013-12-20 ]

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

Comment by Olivier Bertrand [ 2013-12-20 ]

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.

Comment by Olivier Bertrand [ 2013-12-20 ]

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.

Comment by Olivier Bertrand [ 2013-12-29 ]

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.

Generated at Thu Feb 08 07:03:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.