[MDEV-7522] CONNECT table for mdb files (with easysoft driver) Created: 2015-01-28  Updated: 2015-02-04  Resolved: 2015-02-03

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.0.16
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Takuya Aoki (Inactive) Assignee: Olivier Bertrand
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

CentOS release 6.5 (X86_64)

Attachments: File db1.mdb    

 Description   

I am testing with Easysoft ODBC-Access Drive to create a CONNECT table linked to a .mdb file.
It is working, but I found that MariaDB is limiting the column name to 24 characters when the original .mdb file has a column name that is longer.
Therefore, I am getting an error telling me the column does not exist.
Can this be fixed so I can use column names longer than 24 characters?

Installing Easysoft ODBC-Access Drive

Download from
 
http://www.easysoft.com/products/data_access/odbc-access-driver/index.html#section=tab-1
 
 
 
su root
 
tar xvf odbc-access-1.1.0-linux-x86-64-ul64.tar
 
cd odbc-access-1.1.0-linux-x86-64-ul64
 
./install
 
vi /etc/odbc.ini
 
###
 
[ACCESS_SAMPLE]
 
Description=Easysoft ODBC-Access Driver
 
Driver=Easysoft ODBC-ACCESS
 
mdbfile=/var/lib/mysql/db1.mdb
 
smbpath=
 
lockfile=
 
smblib=
 
smbuser=
 
smbauth=
 
readonly=yes
 
exclusive=no
 
ignore_rel=no
 
###
 
 

checking isql

[root@kc0022 ~]# isql ACCESS_SAMPLE
 
+---------------------------------------+
 
| Connected!                            |
 
|                                       |
 
| sql-statement                         |
 
| help [tablename]                      |
 
| quit                                  |
 
|                                       |
 
+---------------------------------------+
 
SQL> SELECT * FROM t_test_short
 
+-----------+---------------------------------------------------+---------------------------------------------------+----------------------+
 
| id        | name                                              | place                                             | register_date        |
 
+-----------+---------------------------------------------------+---------------------------------------------------+----------------------+
 
| 0         | Tom                                               | US                                                | 2014-12-01 00:00:00  |
 
| 1         | Sara                                              | France                                            | 2015-01-02 00:00:00  |
 
| 2         | Jef                                               | Poland                                            | 2015-01-15 00:00:00  |
 
+-----------+---------------------------------------------------+---------------------------------------------------+----------------------+
 
SQLRowCount returns -1
 
3 rows fetched
 
SQL> SELECT * FROM t_test_long
 
+-----------+---------------------------------------------------+---------------------------------------------------+----------------------+
 
| id        | name_of_members_who_have_contacted                | place                                             | register_date        |
 
+-----------+---------------------------------------------------+---------------------------------------------------+----------------------+
 
| 0         | Tom                                               | US                                                | 2014-12-01 00:00:00  |
 
| 1         | Sara                                              | France                                            | 2015-01-02 00:00:00  |
 
| 2         | Jef                                               | Poland                                            | 2015-01-15 00:00:00  |
 
+-----------+---------------------------------------------------+---------------------------------------------------+----------------------+
 
SQLRowCount returns -1
 
3 rows fetched
 
SQL> quit;

making a CONNECT table

[root@kc0022 ~]# mysql -u root
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 7
 
Server version: 10.0.16-MariaDB MariaDB Server
 
 
 
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> use test;
 
Reading table information for completion of table and column names
 
You can turn off this feature to get a quicker startup with -A
 
 
 
Database changed
 
MariaDB [test]> CREATE OR REPLACE TABLE `mdb_t_test_short`
 
    -> engine=CONNECT table_type=ODBC tabname='t_test_short' connection='DSN=ACCESS_SAMPLE';
 
Query OK, 0 rows affected (0.57 sec)
 
 
 
MariaDB [test]> EXPLAIN `mdb_t_test_short`;
 
+---------------+-------------+------+-----+-------------------+-----------------------------+
 
| Field         | Type        | Null | Key | Default           | Extra                       |
 
+---------------+-------------+------+-----+-------------------+-----------------------------+
 
| id            | int(10)     | NO   |     | NULL              |                             |
 
| name          | varchar(50) | YES  |     | NULL              |                             |
 
| place         | varchar(50) | YES  |     | NULL              |                             |
 
| register_date | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
 
+---------------+-------------+------+-----+-------------------+-----------------------------+
 
4 rows in set (0.07 sec)
 
 
 
MariaDB [test]> SELECT * FROM `mdb_t_test_short`;
 
+----+------+--------+---------------------+
 
| id | name | place  | register_date       |
 
+----+------+--------+---------------------+
 
|  0 | Tom  | US     | 2014-12-01 00:00:00 |
 
|  1 | Sara | France | 2015-01-02 00:00:00 |
 
|  2 | Jef  | Poland | 2015-01-15 00:00:00 |
 
+----+------+--------+---------------------+
 
3 rows in set (0.04 sec)
 
 
 
MariaDB [test]> CREATE OR REPLACE TABLE `mdb_t_test_long`
 
    -> engine=CONNECT table_type=ODBC tabname='t_test_long' connection='DSN=ACCESS_SAMPLE';
 
Query OK, 0 rows affected (0.09 sec)
 
 
 
MariaDB [test]> EXPLAIN `mdb_t_test_long`;
 
+--------------------------+-------------+------+-----+-------------------+-----------------------------+
 
| Field                    | Type        | Null | Key | Default           | Extra                       |
 
+--------------------------+-------------+------+-----+-------------------+-----------------------------+
 
| id                       | int(10)     | NO   |     | NULL              |                             |
 
| name_of_members_who_have | varchar(50) | YES  |     | NULL              |                             |
 
| place                    | varchar(50) | YES  |     | NULL              |                             |
 
| register_date            | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
 
+--------------------------+-------------+------+-----+-------------------+-----------------------------+
 
4 rows in set (0.06 sec)
 
 
 
MariaDB [test]> SELECT * FROM `mdb_t_test_long`;
 
ERROR 1296 (HY000): Got error 174 '[unixODBC][Easysoft ODBC]General error column 'NAME_OF_MEMBERS_WHO_HAVE' not found in specified tables' from CONNECT



 Comments   
Comment by Elena Stepanova [ 2015-01-29 ]

FederatedX doesn't have the problem, so it doesn't seem to be a caused by table discovery as such – probably a Connect problem, then?

Comment by Takuya Aoki (Inactive) [ 2015-02-02 ]

Can you tell me if it happens only on ODBC tables or with other CONNECT tables as well?

Comment by Olivier Bertrand [ 2015-02-03 ]

I have tested this both on Windows an Linux.

On windows

CREATE OR REPLACE TABLE db1_exec (
 
command varchar(150) not null,
 
number int(5) not null flag=1,
 
message varchar(255) flag=2)
 
engine=CONNECT table_type=ODBC connection='DSN=MS Access Db1' OPTION_LIST='Execsrc=1';
 
select * from db1_exec where command = 'CREATE TABLE t_long (id integer, name_of_members_who_have_contacted varchar(50), place varchar(50), register_date datetime)';
 
select * from db1_exec where command = "INSERT INTO t_long VALUES(0,'Tom','US','2014-12-01')";
 
select * from db1_exec where command = "INSERT INTO t_long  VALUES(1,'Sara','France','2015-01-02')";
 
select * from db1_exec where command = "INSERT INTO t_long VALUES(2,'Jef','Poland','2015-01-15')";
 
 
 
CREATE OR REPLACE TABLE t_long engine=CONNECT table_type=ODBC connection='DSN=MS Access Db1';
 
select * from t_long;

id name_of_members_who_have_contacted place register_date
0 Tom US 2014-12-01 00:00:00
1 Sara France 2015-01-02 00:00:00
2 Jef Poland 2015-01-15 00:00:00 

explain t_long;

Field Type Null Key Default Extra
id int(10) YES <null>
name_of_members_who_have_contacted varchar(50) YES <null>
place varchar(50) YES <null>
register_date timestamp NO <null> CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

This shows that CONNECT does not do any truncation of column names to 24 characters.

On Linux

After installing the easySoft stuff, I did the same test and this time, while it was working all right with isbl, the column name have been truncated to 24 characters when using CONNECT.

After adding some tracing in the code, I finally found the reason. When defining the table without specifying the columns, MariaDB calls CONNECT's connect_assisted_discovery function that calls the ODBCColumns function that get the columns definition via the ODBC SQLColumns function.

When allocating the memory to store the results, it also interrogates the ODBC server to know some of its limitations, for instance:

n = ocp->GetMaxValue(SQL_MAX_COLUMNS_IN_TABLE);

To know the maximum number of columns in a table and:

n = ocp->GetMaxValue(SQL_MAX_COLUMN_NAME_LEN);

to know the size of the buffer to allocate to receive column names.

What happen is that on Windows this last function returns 128 but on Linux it returns 24! This is why the column names are truncated.

This is not a CONNECT problem but a unixODBC/MSSQL problem that should be transferred to them. The possible workaround:

  1. Don't use on Linux column names longer than 24 characters
  2. Specify the columns in the CREATE TABLE statement.

Indeed, when column are specified, you can use column names longer that that and they are accepted when querying the table! As a matter of fact, this unixODBC/MSSQL bug seems limited to the return of the SQLGetInfo function (called by GetMaxValue)

Comment by Takuya Aoki (Inactive) [ 2015-02-04 ]

Thank you for your work specifying the reason.
It was a problem of the ODBC driver (I contacted easysoft and they fixed it for the new release).

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