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