Ruby连接使用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