原文地址: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
最后修改時間:2022-04-07 14:29:34
「喜歡這篇文章,您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容,轉載時必須標注文章的來源(墨天輪),文章鏈接,文章作者等基本信息,否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容,歡迎發送郵件至:contact@modb.pro進行舉報,并提供相關證據,一經查實,墨天輪將立刻刪除相關內容。




