网上很多使用mariadb的connect引擎连接oracle，但是没有mariadb使用connect引擎连接sqlserver。本文来介绍一下

　　---1.使用最新版connect

　　# mysql

　　Welcome to the MariaDB monitor. Commands end with ; or \g.

　　Your MariaDB connection id is 5

　　Server version: 10.1.10-MariaDB-log Source distribution

　　Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

　　Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

　　#rpm2cpio MariaDB-10.1.10-centos6-x86_64-connect-engine.rpm | cpio --extract --make-directories

　　#cp /soft/usr/lib64/mysql/plugin/ha_connect.so /data/mysql/lib/plugin/

　　root@node01 13:30:33>INSTALL SONAME 'ha_connect';

　　Query OK, 0 rows affected (0.00 sec)

　　# ls /data/mysql/lib/plugin/

　　adt_null.so dialog.so ha_federatedx.so metadata_lock_info.so semisync_master.so

　　auth_0x0100.so example_key_management.so ha_innodb.so mypluglib.so semisync_slave.so

　　auth_pam.so file_key_management.so ha_mroonga.so mysql_clear_password.so server_audit.so

　　auth_socket.so ha_archive.so handlersocket.so qa_auth_client.so simple_password_check.so

　　auth_test_plugin.so ha_blackhole.so ha_spider.so qa_auth_interface.so sql_errlog.so

　　daemon_example.ini ha_connect.so ha_test_sql_discovery.so qa_auth_server.so wsrep_info.so

　　debug_key_management.so ha_example.so libdaemon_example.so query_cache_info.so

　　dialog_examples.so ha_federated.so locales.so query_response_time.so

　　--权限重要

　　#chown mysql:mysql -R /data/mysql

　　root@node01 15:18:54>show engines \G;

　　*************************** 1. row ***************************

　　Engine: MRG_MyISAM

　　Support: YES

　　Comment: Collection of identical MyISAM tables

　　Transactions: NO

　　XA: NO

　　Savepoints: NO

　　*************************** 2. row ***************************

　　Engine: MEMORY

　　Support: YES

　　Comment: Hash based, stored in memory, useful for temporary tables

　　Transactions: NO

　　XA: NO

　　Savepoints: NO

　　*************************** 3. row ***************************

　　Engine: MyISAM

　　Support: YES

　　Comment: MyISAM storage engine

　　Transactions: NO

　　XA: NO

　　Savepoints: NO

　　*************************** 4. row ***************************

　　Engine: CSV

　　Support: YES

　　Comment: CSV storage engine

　　Transactions: NO

　　XA: NO

　　Savepoints: NO

　　*************************** 5. row ***************************

　　Engine: CONNECT

　　Support: YES

　　Comment: Management of External Data (SQL/MED), including many file formats

　　Transactions: NO

　　XA: NO

　　Savepoints: NO

　　*************************** 6. row ***************************

　　Engine: SEQUENCE

　　Support: YES

　　Comment: Generated tables filled with sequential values

　　Transactions: YES

　　XA: NO

　　Savepoints: YES

　　*************************** 7. row ***************************

　　Engine: SPHINX

　　Support: YES

　　Comment: Sphinx storage engine 2.2.6-release

　　Transactions: NO

　　XA: NO

　　Savepoints: NO

　　*************************** 8. row ***************************

　　Engine: InnoDB

　　Support: DEFAULT

　　Comment: Percona-XtraDB, Supports transactions, row-level locking, foreign keys and encryption for tables

　　Transactions: YES

　　XA: YES

　　Savepoints: YES

　　*************************** 9. row ***************************

　　Engine: PERFORMANCE_SCHEMA

　　Support: YES

　　Comment: Performance Schema

　　Transactions: NO

　　XA: NO

　　Savepoints: NO

　　*************************** 10. row ***************************

　　Engine: Aria

　　Support: YES

　　Comment: Crash-safe tables with MyISAM heritage

　　Transactions: NO

　　XA: NO

　　Savepoints: NO

　　10 rows in set (0.00 sec)

　　--2.自动安装unixODBC 2.3.0:(网络通畅的情况下)

　　[root@node01 msodbcsql-11.0.2270.0]# ./build_dm.sh

　　Build unixODBC 2.3.0 DriverManager script

　　Copyright Microsoft Corp.

　　In order to use the Microsoft ODBC Driver 11 for SQL Server on Linux,

　　the unixODBC DriverManager must be installed on your computer. unixODBC

　　DriverManager is a third-party tool made available by the unixODBC Project.

　　To assist you in the installation process, this script will attempt to

　　download, properly configure, and build the unixODBC DriverManager from

　　http://www.unixodbc.org/ for use with the Microsoft ODBC Driver 11

　　for SQL Server ODBC Driver on Linux.

　　Alternatively, you can choose to download and configure unixODBC

　　DriverManager from http://www.unixodbc.org/ yourself.

　　Note: unixODBC DriverManager is licensed to you under the terms of an

　　agreement between you and the unixODBC Project, not Microsoft. Microsoft

　　does not guarantee the unixODBC DriverManager or grant any rights to

　　you. Prior to downloading, you should review the license for unixODBC

　　DriverManager at http://www.unixodbc.org/.

　　The script is provided as a convenience to you as-is, without any express

　　or implied warranties of any kind. Microsoft is not liable for any issues

　　arising out of your use of the script.

　　Enter 'YES' to have this script continue: YES

　　Verifying processor and operating system ................................... OK

　　Verifying wget is installed ................................................ OK

　　Verifying tar is installed ................................................. OK

　　Verifying make is installed ................................................ OK

　　Downloading unixODBC 2.3.0 DriverManager

　　--手动安装unixODBC 2.3.0:(网络不好的情况下 )--推荐

　　tar xvzf unixODBC-2.3.1.tar.gz.

　　CPPFLAGS="-DSIZEOF_LONG_INT=8"

　　export CPPFLAGS

　　./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/etc --enable-gui=no --enable-drivers=no --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE

　　make

　　make install

　　touch /etc/odbcinst.ini

　　touch /etc/odbc.ini

　　---3.安装微软odbc驱动

　　[root@node01 soft]# ls

　　msodbcsql-11.0.2270.0.tar.gz

　　[root@node01 soft]# tar msodbcsql-11.0.2270.0.tar.gz

　　tar: Old option `b' requires an argument.

　　Try `tar --help' or `tar --usage' for more information.

　　[root@node01 soft]# tar xvzf msodbcsql-11.0.2270.0.tar.gz

　　msodbcsql-11.0.2270.0/

　　msodbcsql-11.0.2270.0/include/

　　msodbcsql-11.0.2270.0/include/msodbcsql.h

　　msodbcsql-11.0.2270.0/bin/

　　msodbcsql-11.0.2270.0/bin/SQLCMD.rll

　　msodbcsql-11.0.2270.0/bin/BatchParserGrammar.dfa

　　msodbcsql-11.0.2270.0/bin/BatchParserGrammar.llr

　　msodbcsql-11.0.2270.0/bin/bcp.rll

　　msodbcsql-11.0.2270.0/bin/bcp-11.0.2270.0

　　msodbcsql-11.0.2270.0/bin/sqlcmd-11.0.2270.0

　　msodbcsql-11.0.2270.0/WARNING

　　msodbcsql-11.0.2270.0/build_dm.sh

　　msodbcsql-11.0.2270.0/lib64/

　　msodbcsql-11.0.2270.0/lib64/msodbcsqlr11.rll

　　msodbcsql-11.0.2270.0/lib64/libmsodbcsql-11.0.so.2270.0

　　msodbcsql-11.0.2270.0/install.sh

　　msodbcsql-11.0.2270.0/LICENSE

　　msodbcsql-11.0.2270.0/README

　　msodbcsql-11.0.2270.0/docs/

　　msodbcsql-11.0.2270.0/docs/en_US.tar.gz

　　[root@node01 soft]# ls

　　msodbcsql-11.0.2270.0 msodbcsql-11.0.2270.0.tar.gz

　　[root@node01 soft]# cd msodbcsql-11.0.2270.0

　　[root@node01 msodbcsql-11.0.2270.0]# ls

　　bin build_dm.sh docs include install.sh lib64 LICENSE README WARNING

　　# cd msodbcsql-11.0.2270.0

　　# ls

　　bin build_dm.sh docs include install.sh lib64 LICENSE README unixODBC-2.3.1.tar.gz WARNING

　　./install.sh verify

　　./install.sh install

　　--4.安装完成测试测试

　　# odbcinst -q -d -n "ODBC Driver 11 for SQL Server"

　　[ODBC Driver 11 for SQL Server]

　　Description=Microsoft ODBC Driver 11 for SQL Server

　　Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0

　　Threading=1

　　UsageCount=1

　　Trace=Yes

　　TraceFile=/var/log/odbc.log

　　# odbcinst -j

　　unixODBC 2.3.0

　　DRIVERS............: /etc/odbcinst.ini

　　SYSTEM DATA SOURCES: /etc/odbc.ini

　　FILE DATA SOURCES..: /etc/ODBCDataSources

　　USER DATA SOURCES..: /root/.odbc.ini

　　SQLULEN Size.......: 8

　　SQLLEN Size........: 8

　　SQLSETPOSIROW Size.: 8

　　# sqlcmd -S 192.168.1.143 -U sa -P sa01 -d master -Q 'select "Hello World"'

　　-----------

　　Hello World

　　# sqlcmd -S 192.168.1.143 -U sa -P sa01 -d master -Q 'select name from sys.databases'

　　name

　　--------------------------------------------------------------------------------------------------------------------------------

　　master

　　tempdb

　　model

　　msdb

　　test

　　DebugMonitor

　　InternalDebugMonitor

　　JinriMonitor

　　#vi /etc/template.ini

　　[dsn01]

　　Description = linux to sqlserver

　　Driver = ODBC Driver 11 for SQL Server

　　Server = 192.168.1.143

　　Port = 1433

　　UID = sa

　　PWD = sa01

　　Database = master

　　Trace = Yes

　　TraceFile = /var/log/odbc.log

　　#odbcinst -i -s -l -f /etc/template.ini

　　# isql -v dsn01 sa sa01

　　+---------------------------------------+

　　| Connected! |

　　| |

　　| sql-statement |

　　| help [tablename] |

　　| quit |

　　| |

　　+---------------------------------------+

　　SQL> select name from sys.databases;

　　+---------------------------------------------------------------------------------------------------------------------------------+

　　| name |

　　+---------------------------------------------------------------------------------------------------------------------------------+

　　| master |

　　| tempdb |

　　| model |

　　| msdb |

　　| test |

　　| DebugMonitor |

　　| InternalDebugMonitor |

　　| JinriMonitor |

　　+---------------------------------------------------------------------------------------------------------------------------------+

　　SQLRowCount returns 0

　　8 rows fetched

　　# sudo -u mysql isql -v dsn01 sa sa01

　　+---------------------------------------+

　　| Connected! |

　　| |

　　| sql-statement |

　　| help [tablename] |

　　| quit |

　　| |

　　+---------------------------------------+

　　SQL>

　　---5.新建connect引擎的表

　　create table t01

　　engine=CONNECT

　　table_type=ODBC

　　block_size=10

　　tabname='t01'

　　connection='DSN=dsn01; UID=sa; PWD=sa01';

　　root@node01 18:30:56>create table t01

　　-> engine=CONNECT

　　-> table_type=ODBC

　　-> block_size=10

　　-> tabname='t01'

　　-> connection='DSN=dsn01; UID=sa; PWD=sa01';

　　Query OK, 0 rows affected (0.26 sec)

　　root@node01 18:30:58>select * from t01;

　　+------+---------+

　　| sid | sname |

　　+------+---------+

　　| 101 | ocpyang |

　　+------+---------+

　　1 row in set (0.04 sec)

　　---6.验证

　　--mysql端插入数据

　　root@node01 18:37:00>insert into t01 values(102,'jyl');

　　Query OK, 1 row affected (0.48 sec)

　　root@node01 18:37:12>commit;

　　Query OK, 0 rows affected (0.00 sec)

　　root@node01 18:37:14>select * from t01;

　　+------+---------+

　　| sid | sname |

　　+------+---------+

　　| 101 | ocpyang |

　　| 102 | jyl |

　　+------+---------+

　　2 rows in set (0.02 sec)