Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
10.0.15
-
CentOS 7
Description
I have been attempting to try out using the CONNECT storage engine to connect to MS SQL Server via MariaDB 10.0 on CentOS 7.
I did the following to set up the environment:
#Install EPEL
|
sudo rpm -ivh http://download.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm
|
#Install unixODBC
|
sudo yum install unixODBC unixODBC-devel
|
#Install FreeTDS
|
sudo yum install freetds freetds-devel
|
#Install and load CONNECT
|
sudo yum install MariaDB-connect-engine
|
mysql -u root --execute="INSTALL SONAME 'ha_connect';"
|
Then I created the two files tds.driver.template and tds.datasource.template.
|
tds.driver.template |
[FreeTDS]
|
Description = ODBC for TDS protocol
|
Driver = /usr/lib64/libtdsodbc.so
|
|
tds.datasource.template |
[connect_test]
|
Driver = FreeTDS
|
Description = MSSQL Server
|
Trace = No
|
Server = 192.168.1.12
|
Database = connect_test
|
Port = 1433
|
TDS_Version = 7.1
|
And executed:
#Install FreeTDS Driver for ODBC
|
sudo odbcinst -i -d -f tds.driver.template
|
#Install data source for ODBC
|
sudo odbcinst -i -s -l -f tds.datasource.template
|
Everything appears to work via ODBC's isql client:
[gmontee@localhost ~]$ isql connect_test sa 'password'
|
+---------------------------------------+
|
| Connected! |
|
| |
|
| sql-statement |
|
| help [tablename] |
|
| quit |
|
| |
|
+---------------------------------------+
|
SQL> SELECT * FROM dbo.test_table;
|
+------------+---------------------------------------------------+
|
| a | b |
|
+------------+---------------------------------------------------+
|
+------------+---------------------------------------------------+
|
SQLRowCount returns 0
|
SQL> INSERT INTO dbo.test_table VALUES(1, 'correct');
|
SQLRowCount returns 1
|
SQL> INSERT INTO dbo.test_table VALUES(2, 'horse');
|
SQLRowCount returns 1
|
SQL> INSERT INTO dbo.test_table VALUES(3, 'battery');
|
SQLRowCount returns 1
|
SQL> SELECT * FROM dbo.test_table;
|
+------------+---------------------------------------------------+
|
| a | b |
|
+------------+---------------------------------------------------+
|
| 1 | correct |
|
| 2 | horse |
|
| 3 | battery |
|
+------------+---------------------------------------------------+
|
SQLRowCount returns 3
|
3 rows fetched
|
However, connecting to the same data source with CONNECT doesn't work. I've tried the following different table options:
-- DSN
|
-- fields not specified
|
-- schema qualified in TABNAME
|
CREATE TABLE test_table |
ENGINE=CONNECT |
TABLE_TYPE=ODBC
|
TABNAME='dbo.test_table' |
CONNECTION='DSN=connect_test;UID=sa;PWD=password'; |
 |
-- DSN
|
-- fields not specified
|
-- schema in DBNAME
|
CREATE TABLE test_table |
ENGINE=CONNECT |
TABLE_TYPE=ODBC
|
DBNAME='dbo' |
TABNAME='test_table' |
CONNECTION='DSN=connect_test;UID=sa;PWD=password'; |
 |
-- DSN
|
-- fields not specified
|
-- use default schema
|
CREATE TABLE test_table |
ENGINE=CONNECT |
TABLE_TYPE=ODBC
|
TABNAME='test_table' |
CONNECTION='DSN=connect_test;UID=sa;PWD=password'; |
 |
-- DSN
|
-- fields specified
|
-- schema qualified in TABNAME
|
CREATE TABLE test_table ( |
a int, |
b varchar(50) |
)
|
ENGINE=CONNECT |
TABLE_TYPE=ODBC
|
TABNAME='dbo.test_table' |
CONNECTION='DSN=connect_test;UID=sa;PWD=password'; |
 |
-- DSN
|
-- fields specified
|
-- schema in DBNAME
|
CREATE TABLE test_table ( |
a int, |
b varchar(50) |
)
|
ENGINE=CONNECT |
TABLE_TYPE=ODBC
|
DBNAME='dbo' |
TABNAME='test_table' |
CONNECTION='DSN=connect_test;UID=sa;PWD=password'; |
 |
-- DSN
|
-- fields specified
|
-- use default schema
|
CREATE TABLE test_table ( |
a int, |
b varchar(50) |
)
|
ENGINE=CONNECT |
TABLE_TYPE=ODBC
|
TABNAME='test_table' |
CONNECTION='DSN=connect_test;UID=sa;PWD=password'; |
 |
-- DSN-less
|
-- fields specified
|
-- schema qualified in TABNAME
|
CREATE TABLE test_table ( |
a int, |
b varchar(50) |
)
|
ENGINE=CONNECT |
TABLE_TYPE=ODBC
|
TABNAME='dbo.test_table' |
CONNECTION='DRIVER=FreeTDS;Server=192.168.1.12;Port=1433;TDS_Version=7.1;Database=connect_test;UID=sa;PWD=password'; |
They all return the same vague error when attempting to connect:
ERROR 1296 (HY000): Got error 174 '[unixODBC][FreeTDS][SQL Server]Unable to connect to data source' from CONNECT
|
I am using MS SQL Server Express 2012 which can be downloaded for free.
Considering how many components are in play here (SQL Server, FreeTDS, unixODBC, CONNECT, MariaDB), there's a good chance I'm missing an option, but I'm not sure what that would be.