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
] = 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