ruby1.8支持mssql数据库并不是太好，但是也可以实现

 

我初次使用ruby连接sql server，使用的是dbi，可是ruby1.8版本的，对sql server支持的并不是太好。

 

可以不使用dbi，而使用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 execute(sql)

        @connection.Execute(sql)

    end

    def close

        @connection.Close

    end

end

 

ACCESS:

 

 

 

require "win32ole"
class AccessDb
    attr_accessor :mdb, :connection, :data, :fields

    def initialize(mdb=nil)
        @mdb = mdb
        @connection = nil
        @data = nil
        @fields = nil
    end

    def open
        connection_string =  'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
        connection_string << @mdb
        @connection = WIN32OLE.new('ADODB.Connection')
        @connection.Open(connection_string)
    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 execute(sql)
        @connection.Execute(sql)
    end

    def close
        @connection.Close
    end
end

 

 

 

用法：

 

db = MssqlDb.new(host,database,username,password)
db.open
db.query(sql)
field_names = db.fields
#p field_names.join ' '
rows = db.data
#p rows.methods.sort.join(' ')
rows.size.times{|i| p rows.fetch i }