在 Windows 平台使用 win32ole 透過 ADO 連接 SQL Server 與 Access,提供兩種封裝類別完整實作、連線字串參數化、查詢結果轉置為列陣列、編碼處理。
require 'win32ole'
class SqlServer
attr_accessor :connection, :data, :fields
def initialize
@connection = nil
@data = nil
end
def open
conn_str = "Provider=SQLOLEDB.1;"
conn_str << "Persist Security Info=False;"
conn_str << "User ID=USER_ID;"
conn_str << "password=PASSWORD;"
conn_str << "Initial Catalog=DATABASE;"
conn_str << "Data Source=IP_ADDRESS;"
conn_str << "Network Library=dbmssocn"
@connection = WIN32OLE.new('ADODB.Connection')
@connection.Open(conn_str)
end
def query(sql)
rs = WIN32OLE.new('ADODB.Recordset')
rs.Open(sql, @connection)
@fields = rs.Fields.each.map(&:Name)
begin
rs.MoveFirst
@data = rs.GetRows.transpose # 轉為列陣列
rescue
@data = []
end
rs.Close
end
def close
@connection.Close
end
end
db = SqlServer.new
db.open
db.query("SELECT PLAYER FROM PLAYERS WHERE TEAM = 'REDS';")
puts db.fields
puts db.data.inspect
db.close
class MssqlDb
attr_accessor :connection, :data, :fields
def initialize(host, db, user, pass)
@host, @mdb, @username, @password = host, db, user, pass
end
def open
conn_str = "Provider=SQLOLEDB.1;User ID=#{@username};password=#{@password};Data Source=#{@host},1433;Initial Catalog=#{@mdb}"
@connection = WIN32OLE.new('ADODB.Connection')
@connection.Open(conn_str)
@password = '' # 清除記憶體密碼
end
def query(sql)
rs = WIN32OLE.new('ADODB.Recordset')
rs.Open(sql, @connection)
@fields = rs.Fields.each.map(&:Name)
begin
@data = rs.GetRows.transpose
rescue
@data = []
end
rs.Close
end
def close; @connection.Close; end
end
# 連線字串差異:Provider=Microsoft.ACE.OLEDB.12.0 (或 .Jet.OLEDB.4.0)
conn_str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\path\\db.accdb;"
# 其餘 ADO 操作相同
WIN32OLE.codepage = WIN32OLE::CP_UTF8 設定 UTF-8 編碼,避免中文亂碼。GetRows 回傳欄陣列,transpose 轉為列陣列符合直覺。rs 需顯式 Close,否則連線洩漏。tiny_tds (FreeTDS) 或 sequel + odbc 獲得跨平台、無 COM 依賴、連線池支援。