Accessing SQL Server 2005 from Ruby (See related posts)

The following is improved version of the code created by David Mullet, from
http://rubyonwindows.blogspot.com/2007/03/ruby-ado-and-sqlserver.html

   1  
   2  require 'win32ole'
   3  
   4  class SqlServer
   5      # This class manages database connection and queries
   6      attr_accessor :connection, :data, :fields
   7  	attr_writer :username, :password
   8  
   9      def initialize(host, username = 'sa', password='')
  10          @connection = nil
  11          @data = nil
  12  	@host = host
  13  	@username = username
  14  	@password = password
  15      end
  16  
  17      def open(database)
  18          # Open ADO connection to the SQL Server database
  19          connection_string =  "Provider=SQLOLEDB.1;"
  20          connection_string << "Persist Security Info=False;"
  21          connection_string << "User ID=#{@username};"
  22          connection_string << "password=#{@password};"
  23          connection_string << "Initial Catalog=#{database};"
  24          connection_string << "Data Source=#{@host};"
  25          connection_string << "Network Library=dbmssocn"
  26          @connection = WIN32OLE.new('ADODB.Connection')
  27          @connection.Open(connection_string)
  28      end
  29  
  30      def query(sql)
  31          # Create an instance of an ADO Recordset
  32          recordset = WIN32OLE.new('ADODB.Recordset')
  33          # Open the recordset, using an SQL statement and the
  34          # existing ADO connection
  35          recordset.Open(sql, @connection)
  36          # Create and populate an array of field names
  37          @fields = []
  38          recordset.Fields.each do |field|
  39              @fields << field.Name
  40          end
  41          begin
  42              # Move to the first record/row, if any exist
  43              recordset.MoveFirst
  44              # Grab all records
  45              @data = recordset.GetRows
  46          rescue
  47              @data = []
  48          end
  49          recordset.Close
  50          # An ADO Recordset's GetRows method returns an array 
  51          # of columns, so we'll use the transpose method to 
  52          # convert it to an array of rows
  53          @data = @data.transpose
  54      end
  55  
  56      def close
  57          @connection.Close
  58      end
  59  end

How to use it:
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