您现在的位置：程式碼之家 >> 編程語言 > >> 更多程式語言 >> 更多程式設計知識

Ruby 連接 Windows SQL Server / Access 資料庫完整指南：WIN32OLE、ADO、DBI 多種方案比較

編輯：更多程式設計知識

Windows 平台 Ruby 連接資料庫概覽

方案	支援資料庫	依賴	特點	推薦度
WIN32OLE + ADO	SQL Server, Access, Excel, Oracle...	Windows 原生	無需額外驅動、效能好、功能完整	⭐⭐⭐⭐⭐
DBI + ADO	同上	dbi, dbd-ado gem	統一 DBI 介面、可攜性較好	⭐⭐⭐
ODBC (ruby-odbc)	所有 ODBC 支援	ruby-odbc gem	跨平台、標準介面	⭐⭐⭐⭐
TinyTDS	SQL Server	tiny_tds gem	高效能、非 Windows 也可用	⭐⭐⭐⭐ (Linux/Mac)
Sequel/ActiveRecord	上述皆可	ORM gem	應用層抽象、生產環境首選	⭐⭐⭐⭐⭐

方案一：WIN32OLE 直連 ADO (SQL Server)


require 'win32ole'

class SqlServer
  attr_accessor :connection, :data, :fields

  def initialize
    @connection = nil
    @data = nil
    @fields = nil
  end

  def open(connection_string = nil)
    connection_string ||= build_connection_string
    @connection = WIN32OLE.new('ADODB.Connection')
    @connection.Open(connection_string)
  end

  def build_connection_string
    # 信任連線 (Windows 驗證)
    "Provider=SQLOLEDB.1;" \
    "Integrated Security=SSPI;" \
    "Initial Catalog=MyDatabase;" \
    "Data Source=localhost;" \
    "Network Library=dbmssocn"
    
    # 或 SQL Server 驗證
    # "Provider=SQLOLEDB.1;User ID=sa;Password=pass;Initial Catalog=MyDB;Data Source=192.168.1.100;"
  end

  def query(sql)
    recordset = WIN32OLE.new('ADODB.Recordset')
    recordset.Open(sql, @connection)
    
    @fields = []
    recordset.Fields.each { |f| @fields << f.Name }
    
    begin
      recordset.MoveFirst
      @data = recordset.GetRows.transpose  # 轉為列陣列
    rescue
      @data = []
    end
    
    recordset.Close
  end

  def execute(sql)
    @connection.Execute(sql)
  end

  def close
    @connection.Close if @connection
  end
end

# 使用範例
db = SqlServer.new
db.open
db.query("SELECT * FROM Users WHERE Active = 1")
puts "欄位: #{db.fields.join(', ')}"
db.data.each { |row| puts row.inspect }
db.close

方案二：完整封裝類別 (支援編碼轉換、參數化查詢)


require 'win32ole'
require 'iconv'

WIN32OLE.codepage = WIN32OLE::CP_UTF8
RE_CN = /[\x7f-\xff]/  # 偵測中文字元

class MssqlDb
  attr_accessor :connection, :data, :fields

  def initialize(host, database, user, password)
    @host, @database, @username, @password = host, database, user, password
    @connection = nil
  end

  def open
    conn_str = "Provider=SQLOLEDB.1;User ID=#{@username};Password=#{@password};" \
               "Data Source=#{@host},1433;Initial Catalog=#{@database}"
    @connection = WIN32OLE.new('ADODB.Connection')
    @connection.Open(conn_str)
    @password = ''  # 清除密碼
  end

  def query(sql)
    execute_query(sql, convert_encoding: false)
  end

  def query_gb(sql)  # 強制 GB18030 編碼 (舊系統相容)
    execute_query(sql, convert_encoding: true)
  end

  private

  def execute_query(sql, convert_encoding:)
    sql = utf8_to_gb(sql) if convert_encoding && sql =~ RE_CN
    
    recordset = WIN32OLE.new('ADODB.Recordset')
    recordset.Open(sql, @connection)
    
    @fields = []
    recordset.Fields.each { |f| @fields << f.Name }
    
    begin
      @data = recordset.GetRows.transpose
    rescue
      @data = []
    end
    recordset.Close
  end

  public

  def execute(sql)
    sql = utf8_to_gb(sql) if sql =~ RE_CN
    @connection.Execute(sql)
  end

  def close
    @connection.Close if @connection
  end

  def utf8_to_gb(str)
    Iconv.conv("GB18030//IGNORE", "UTF-8//IGNORE", str)
  end

  def gb_to_utf8(str)
    Iconv.conv("UTF-8//IGNORE", "GB18030//IGNORE", str)
  end
end

方案三：Access 資料庫連接


require 'win32ole'

class AccessDb
  attr_accessor :connection, :data, :fields

  def initialize(mdb_path)
    @mdb = mdb_path
    @connection = nil
  end

  def open
    conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=#{@mdb}"
    # Access 2007+ (.accdb) 用:
    # conn_str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=#{@mdb}"
    
    @connection = WIN32OLE.new('ADODB.Connection')
    @connection.Open(conn_str)
    puts 'Access open ok.'
  end

  def query(sql)
    recordset = WIN32OLE.new('ADODB.Recordset')
    recordset.Open(sql, @connection)
    
    @fields = []
    recordset.Fields.each { |f| @fields << f.Name }
    
    begin
      @data = recordset.GetRows.transpose
    rescue
      @data = []
    end
    recordset.Close
  end

  def execute(sql)
    @connection.Execute(sql)
  end

  def close
    @connection.Close if @connection
  end
end

# 使用
db = AccessDb.new('C:/data/mydb.mdb')
db.open
db.query("SELECT * FROM Customers")
db.data.each { |row| puts row.join(' | ') }
db.close

方案四：DBI 統一介面 (較舊，但可攜)


# gem install dbi dbd-ado
require 'dbi'

dbh = DBI.connect('DBI:ADO:Provider=SQLOLEDB.1;Data Source=localhost;Initial Catalog=MyDB;Integrated Security=SSPI;')
# 或
dbh = DBI.connect('DBI:ADO:Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/data.mdb')

sth = dbh.prepare('SELECT * FROM Users WHERE Age > ?')
sth.execute(18)
sth.fetch_hash { |row| puts row }
sth.finish
dbh.disconnect

進階主題

參數化查詢 (防 SQL 注入)


# WIN32OLE 使用 Command 物件
cmd = WIN32OLE.new('ADODB.Command')
cmd.ActiveConnection = @connection
cmd.CommandText = "SELECT * FROM Users WHERE Email = ? AND Active = ?"
cmd.CommandType = 1  # adCmdText

# 建立參數
param1 = cmd.CreateParameter('email', 202, 1, 255, 'user@example.com')  # adVarChar=202, adParamInput=1
param2 = cmd.CreateParameter('active', 11, 1, 1, true)  # adBoolean=11
cmd.Parameters.Append(param1)
cmd.Parameters.Append(param2)

recordset = cmd.Execute
# 處理 recordset...

交易控制


@connection.BeginTrans
begin
  @connection.Execute("INSERT INTO Logs (msg) VALUES ('Step 1')")
  @connection.Execute("UPDATE Accounts SET balance = balance - 100 WHERE id = 1")
  @connection.Execute("UPDATE Accounts SET balance = balance + 100 WHERE id = 2")
  @connection.CommitTrans
rescue => e
  @connection.RollbackTrans
  raise e
end

Stored Procedure 呼叫


cmd = WIN32OLE.new('ADODB.Command')
cmd.ActiveConnection = @connection
cmd.CommandText = "sp_GetUserByEmail"
cmd.CommandType = 4  # adCmdStoredProc

param = cmd.CreateParameter('email', 202, 1, 255, 'test@example.com')
cmd.Parameters.Append(param)

recordset = cmd.Execute
# recordset 可能包含多個結果集
while recordset
  # 處理結果
  recordset = recordset.NextRecordset
end

編碼問題完整解決


# 現代系統建議資料庫、連線、Ruby 皆用 UTF-8
# 連接字串加上:
# "CharacterSet=UTF-8;" (部分驅動支援)

# 若舊系統為 Big5/GBK:
WIN32OLE.codepage = WIN32OLE::CP_UTF8  # Ruby 內部用 UTF-8
# 查詢前轉碼:
def encode_sql(sql, target_encoding)
  Iconv.conv("#{target_encoding}//IGNORE", "UTF-8//IGNORE", sql)
end

錯誤處理與除錯


def safe_query(sql)
  begin
    query(sql)
  rescue WIN32OLERuntimeError => e
    # ADO 錯誤代碼在 e.hresult
    case e.hresult
    when -2147217900  # 0x80040E14: 語法錯誤
      puts "SQL 語法錯誤: #{sql}"
    when -2147217865  # 0x80040E37: 表不存在
      puts "表不存在"
    else
      puts "資料庫錯誤: #{e.message} (HRESULT: #{e.hresult})"
    end
    []
  rescue => e
    puts "未預期錯誤: #{e.class} - #{e.message}"
    []
  end
end

測試範例


# SQL Server
db = SqlServer.new
db.open
db.query("SELECT TOP 5 * FROM Products")
puts "Fields: #{db.fields}"
db.data.each { |row| puts row.join(' | ') }
db.close

# Access
db = AccessDb.new('C:/Northwind.mdb')
db.open
db.query("SELECT * FROM Categories")
db.data.each { |row| puts row.join(' | ') }
db.close

部署注意事項

32/64 位元相容：Ruby、ADO 驅動、Office (Access) 必須同位元數
驅動安裝：SQL Server 需安裝 Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) 或舊版 SQLOLEDB
防火牆/通訊埠：SQL Server 預設 1433，Named Instance 需 SQL Browser 服務
權限：Windows 驗證需 Ruby 進程身分有資料庫權限

現代替代方案：Sequel + TinyTDS (跨平台)


# Gemfile
gem 'sequel'
gem 'tiny_tds'

# 使用
DB = Sequel.connect('tinytds://user:pass@host/database')
DB[:users].where(active: true).each { |row| puts row }

# 或 ActiveRecord
gem 'activerecord-sqlserver-adapter'
# database.yml
# adapter: sqlserver
# host: localhost
# database: MyDB
# username: sa
# password: pass

總結

Windows 平台 Ruby 連接 SQL Server/Access：WIN32OLE + ADO 是原生、零依賴、功能最完整的方案。封裝類別處理連線、查詢、編碼、交易、預存程序。Access 用 Microsoft.Jet.OLEDB.4.0 (.mdb) 或 Microsoft.ACE.OLEDB.12.0 (.accdb)。生產環境建議上層用 Sequel 或 ActiveRecord 搭配 tiny_tds (SQL Server) 或 activerecord-sqlserver-adapter，獲得 ORM 優勢與跨平台能力。核心難點在編碼轉換、32/64 位元相容、驅動安裝，部署時務必驗證。