【译】在Linux版的Microsoft SQLServer上安装ODBC驱动程序

原文地址：https://blog.dbi-services.com/installing-the-odbc-drivers-for-microsoft-sqlserver-for-linux/
原文作者：Cesare Cervini

• 本文是包括SQLite、Postgresql、Firebird、Oracle RDBMS、HSQLDB、MariaDB、MongoDB和Excel在内的系列文章之一。如果目标是建立一个独立的环境，用来测试此处介绍的gawk上ODBC扩展是否完成的话，请参阅 SQLite 上安装所需的ODBC驱动程序管理器。
  测试系统版本：debian v11 (bullseye)

以root用户身份进入后，可按照此处的文档获取并安装适用于Linux的sqlserver express。以下是需要用到的操作步骤：

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"

安装mssql数据库软件

apt-get install -y mssql-server

配置systemd服务

/opt/mssql/bin/mssql-conf setup

1. 查看systemd服务是否正常启动（参数“–no-pager”表示“不使用分页器来显示查询后的输出”）：

systemctl status mssql-server --no-pager

输出如下，则表示systemd服务已启动

● mssql-server.service - Microsoft SQL Server Database Engine
     Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2021-08-27 15:22:14 CEST; 15s ago
       Docs: https://docs.microsoft.com/en-us/sql/linux
   Main PID: 7795 (sqlservr)
      Tasks: 120
     Memory: 880.5M
        CPU: 5.983s
     CGroup: /system.slice/mssql-server.service
             ├─7795 /opt/mssql/bin/sqlservr
             └─7817 /opt/mssql/bin/sqlservr

2. 获取并安装SQLServer的ODBC驱动程序：

apt install tdsodbc

apt install curl

curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

apt-get update 

apt-get install mssql-tools unixodbc-dev

3. 检查设备是否已在系统范围内添加：

odbcinst -q -d

odbcinst -q -d -n "ODBC Driver 17 for SQL Server"

4. 在debian上，使用本地管理工具sqlcmd创建sampledb数据库：

$ sqlcmd -S localhost -U SA -P admin2021!
CREATE DATABASE sampledb
go
1> SELECT Name from sys.Databases;
2> go
Name                                                                                                                            
-----------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            
sampledb                                                                                                                        
 
(5 rows affected)

5. 填充sampledb数据库;

此处提供了用于为slqserver创建表的语句，以及在此处填充表的语句。
单击并将文件分别保存到create_tables_mssql.sql和populate_tables_mssql.sql。
使用默认命令行管理工具sqlcmd执行上述SQL脚本：

sqlcmd -S localhost -U SA -P admin2021! -i create_tables_mssql.sql

sqlcmd -S localhost -U SA -P admin2021! -i populate_tables_mssql.sql

检查一下数据：

cat - <<eot | sqlcmd -S localhost -U SA -P admin2021!

USE sampleDB
SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN');
eot

Changed database context to 'sampledb'.
country_name                         country_id country_id   street_address                           city                 
---------------------------------------- ---------- ---------- ---------------------------------------- ---------------------
China                                    CN         NULL       NULL                                     NULL                          
United Kingdom                           UK         UK         8204 Arthur St                           London                        
United Kingdom                           UK         UK         Magdalen Centre, The Oxford Science Park Oxford                        
United States of America                 US         US         2014 Jabberwocky Rd                      Southlake                     
United States of America                 US         US         2011 Interiors Blvd                      South San Francisco           
United States of America                 US         US         2004 Charade Rd                          Seattle                       
 
(6 rows affected)
SQL> 

6. 通过编辑用户的DSN（Data Source Name，数据来源名称）文件来配置ODBC DSN：

vi ~/.odbc.ini

7. 检查DSN：

odbcinst -q -s

结果如下：

odbcinst -q -s -n mymssqlserverdb

8. 使用ODBC驱动程序管理器测试工具isql通过ODBC尝试连接到mssql db：

isql -v mymssqlserverdb SA admin2021!
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

SELECT c.country_name, c.country_id, l.country_id, l.street_address, l.city FROM countries c LEFT JOIN locations l ON l.country_id = c.country_id WHERE c.country_id IN ('US', 'UK', 'CN');
Output:
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
| country_name                            | country_id| country_id| street_address                          | city          |
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
| China                                   | CN        |           |                                         |               |
| United Kingdom                          | UK        | UK        | 8204 Arthur St                          | London        |
| United Kingdom                          | UK        | UK        | Magdalen Centre, The Oxford Science Park| Oxford        |
| United States of America                | US        | US        | 2014 Jabberwocky Rd                     | Southlake     |
| United States of America                | US        | US        | 2011 Interiors Blvd                     | South San Francisco           |
| United States of America                | US        | US        | 2004 Charade Rd                         | Seattle       |
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
SQLRowCount returns 6
6 rows fetched

9. 使用python模块pyodbc测试DSN：

python3
import pyodbc 
cnxn = pyodbc.connect(DSN='mymssqlserverdb;UID=SA;PWD=admin2021!')
cursor = cnxn.cursor()  
cursor.execute("""SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN')""")
row = cursor.fetchone() 
while row:
    print (row) 
    row = cursor.fetchone()
Output:
('China', 'CN', None, None, None)
('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London')
('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford')
('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake')
('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco')
('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle')

现在可以从debian帐户下的任何ODBC应用程序访问到mssqlserver。
其他数据源的说明可以通过以下链接访问：
SQLite
Firebird
HSQLDB
MariaDB
PostgreSQL
Oracle
MongoDB
Excel