windows下sql server连接

require 'win32ole'

class SqlServer
    # This class manages database connection and queries
    attr_accessor :connection, :data, :fields

    def initialize
        @connection = nil
        @data = nil
    end

    def open
        # Open ADO connection to the SQL Server database
        connection_string =  "Provider=SQLOLEDB.1;"
        connection_string << "Persist Security Info=False;"
        connection_string << "User ID=USER_ID;"
        connection_string << "password=PASSWORD;"
        connection_string << "Initial Catalog=DATABASE;"
        connection_string << "Data Source=IP_ADDRESS;"
        connection_string << "Network Library=dbmssocn"
        @connection = WIN32OLE.new('ADODB.Connection')
        @connection.Open(connection_string)
    end

    def query(sql)
        # Create an instance of an ADO Recordset
        recordset = WIN32OLE.new('ADODB.Recordset')
        # Open the recordset, using an SQL statement and the
        # existing ADO connection
        recordset.Open(sql, @connection)
        # Create and populate an array of field names
        @fields = []
        recordset.Fields.each do |field|
            @fields << field.Name
        end
        begin
            # Move to the first record/row, if any exist
            recordset.MoveFirst
            # Grab all records
            @data = recordset.GetRows
        rescue
            @data = []
        end
        recordset.Close
        # An ADO Recordset's GetRows method returns an array 
        # of columns, so we'll use the transpose method to 
        # convert it to an array of rows
        @data = @data.transpose
    end

    def close
        @connection.Close
    end
end

测试代码如下：

db = SqlServer.new
db.open
db.query("SELECT PLAYER FROM PLAYERS WHERE TEAM = 'REDS';")
field_names = db.fields
players = db.data
db.close

db = SqlServer.new('localhost', 'sa', 'SOMEPASSWORD')
db.open('Northwind')
db.query("SELECT * from Customers;")
puts field_names = db.fields
cust = db.data
puts cust.size
puts cust[0].inspect
db.close

抄到的别人版本的：

 MSSQL  
 require "dbi"  
 require "win32ole"  
 WIN32OLE.codepage = WIN32OLE::CP_UTF8  
 require 'iconv'  
 Re_cn=/[\x7f-\xff]/  
   
 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  
   
   
   
   
   
   
 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)  
                 p 'access open ok.'  
     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