windows上用ruby连接sqlserver方法

可以通过windows特有的ADO来连接, 需要借助于WIN32OLE, 不是最好的方法, 但是够用.

 

require "win32ole"

class MssqlDb
  attr_accessor :mdb, :connection, :data, :fields

  def initialize(host,mdb,user,pass)
    @host= host
    @mdb=@database= mdb
    @username= user
    @password= pass
    @connection = nil
    @data = nil
    @fields = nil
  end

  def open  
    connection_string = "Provider=SQLOLEDB.1;User ID=#@username;password=#@password;Data Source=#@host,1433;Initial Catalog=#@mdb"
    @connection = WIN32OLE.new('ADODB.Connection')
    @connection.Open(connection_string)
    #  @password=''
  end

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

  def queryGB(sql)
    if sql=~ Re_cn
    sql = utf8_to_gb(sql)
    end
    recordset = WIN32OLE.new('ADODB.Recordset')
    recordset.Open(sql, @connection)
    @fields = []
    recordset.Fields.each do |field|
      @fields << field.Name
    end
    begin
      @data = recordset.GetRows.transpose
    rescue
      @data = []
    end
    recordset.Close
  end

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

  def executeGB(sql)
    if sql=~ Re_cn
    sql = utf8_to_gb(sql)
    end
    @connection.Execute(sql)
  end

  def close
    @connection.Close
  end
  
  def utf8_to_gb(s)
    p 'conv to gb18030'
    Iconv.conv("GB18030//IGNORE","UTF-8//IGNORE",s)
  end
  def gb_to_utf8(s)
    p 'conv to utf8'
    Iconv.conv("UTF-8//IGNORE","GB18030//IGNORE",s)
  end  
end