From: Max Williams <toastkid.williams@...>
Date: 2009-10-28T23:26:05+09:00
Subject: Re: SQL Shell for MS Access

I connected to an mdb (access db) in windows with the 'win32ole' gem.  I 
also got this class from a blog post by David Mullet:
http://rubyonwindows.blogspot.com/2007/06/using-ruby-ado-to-work-with-ms-access.html

which also has a guide on how to connect.

I modified the class on this page to return results as an array of 
hashes, like ActiveRecord's find_rows method, and added a "find" method 
which just takes an sql string.  So, it's pretty like mysql (my desire 
as well, i was amazed to discover that access has no sql command line).

If this has problems then blame me and not David :)  Here's my complete 
class, see the blog post by David above on how to use it.

class AccessDb

  attr_accessor :mdb, :connection, :data, :fields, :rows, :sql



  def initialize(mdb=nil)

    @mdb = mdb

    @connection = nil

    @data = nil

    @fields = nil

    @rows = []

    self.open

  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)

  end



  def query(sql, options = {})

    self.sql = sql

    puts "Querying: #{sql}" if options[:verbose]

    recordset = WIN32OLE.new('ADODB.Recordset')

    recordset.Open(sql, @connection)

    @fields = []

    @rows = []

    recordset.Fields.each do |field|

      @fields << field.Name

    end

    begin

      @data = recordset.GetRows.transpose

      @data.each do |row|

        row_hash = {}

        row.each_with_index do |value, i|

          row_hash[@fields[i]] = value

        end

        @rows << row_hash

      end

    rescue

      @data = []

    end

    recordset.Close

  end



  def find(sql, options = {})

    self.sql = sql

    self.query(sql, options)

    self.rows

  end



  def execute(sql, options = {})

    self.sql = sql

    puts "Executing: #{sql}" if options[:verbose]

    begin

      @connection.Execute(sql)

    rescue

      raise "!!ERROR executing \n#{sql}\n: (#{@mdb.inspect}) #{$!}"

    end

  end



  def close

    @connection.Close

  end

end
-- 
Posted via http://www.ruby-forum.com/.