Ruby connect to SQL server native client

嘗試了一天，終於找到可以串連SQL Server的方法了。

第一種方法,使用win32ole串連。

特別注意紅色部分哦，網路上面很多人報出來的錯誤都是因為串連的字串的Provider有問題，修改為紅色的值，就可以和SQL server的native client串連成功了。

require 'win32ole'

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

    def initialize(host, username = 'sa', password='')
        @connection = nil
        @data = nil
        @host = host
        @username = username
        @password = password
    end

    def open(database)
        # Open ADO connection to the SQL Server database
        connection_string = "Provider=SQLNCLI;"
        connection_string << "Persist Security Info=True;"
        connection_string << "User ID=#{@username};"
        connection_string << "password=#{@password};"
        connection_string << "Initial Catalog=#{database};"
        connection_string << "Data Source=#{@host};"
        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('hostip', 'username', 'password')
db.open('databasename')
warmlead_url = "select * from table'"
db.query(warmlead_url)

puts field_names = db.fields

cust = db.data

puts cust.size

puts cust[0].inspect

db.close

 

第二種方法，使用dbi串連。

按照如下步驟先配置Ruby環境，然後再運行指令碼。

1. 先去下載ruby-dbi下面的dbi-0.4.3.gem和dbi-0.1.0.tar.gz，下載網址為http://rubyforge.org/frs/?group_id=234&release_id=4323

2. 安裝dbi-0.4.3.gem

   gem install dbi-0.4.3.gem

3. 解壓dbi-0.1.0.tar.gz，尋找ADO.rb檔案。dbi-0.1.0.tar.gz包解壓後的路徑下面尋找（bdi-0.1.0/lib/dbd/ADO.rb）

4. 手動建立ADO檔案夾，建立好之後的路徑為c:/ruby/lib/ruby/site_ruby/1.8/DBD/ADO

5. 拷貝ADO.rb檔案到步驟4中建立好的檔案夾下面

6. 使用下面的Ruby代碼串連SQL Server Native Client, 特別注意紅色的部分。

require 'dbi'

class Server
attr_reader :name
def initialize(name, username, password, database)
  @server_name=name
  @username = username
  @password = password
  @database = database
  @dbh=DBI.connect("DBI:ADO:Provider=SQLNCLI;Data Source=#{name};Persist Security Info=False;User ID=#{@username};password=#{@password};Initial Catalog=#{database};")
end
def databases
  db=Array.new
  @dbh.select_all('SELECT name FROM master.sys.databases ORDER BY 1') do | row |
   db.<< Database.new(@dbh,row[0])
  end 
  db
end
end

class Database
attr_reader :name
def initialize(dbh,name)
  @dbh=dbh
  @name=name
end
end

server=Server.new("hostname","username","password","database_name")
server.databases.each {|x| puts x.name}