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