Hey everyone
I struggled a bit on trying to get data out of an Access database using
Ruby, so I thought I’d share the
results here in case someone else needs this. I found OLEDB automation
to be the easiest method.
Excel Automation is also extremely easy and you can use the macro writer
to create much of the
VBA code that you can then adapt directly to Ruby.
One more thing - getrows returns an array that’s “rotated” - I added a
“rotate” routine to my Array
class to sort that out - see the end. If anyone can improve on the
routine, please post! It looks
very much like C right now…
Les
##################################################################
Recordset with a connection, using JET
This works, but for some reason the memory requirements are large.
Using an ODBC source for the same data seems to use almost no memory!
require “win32ole”
conn = WIN32OLE.new(“ADODB.Connection”)
conn[“Provider”] = “Microsoft.Jet.OLEDB.4.0”
conn.Open(‘c:\ruby\dev\calldata.mdb’)
rs = conn.Execute(“select date, dialednumber, extension, cost from
callrecords where call = 2 and date >=#01-jan-2005#”)
rs.getrows.each do |row|
puts row
end
conn.Close
##################################################################
Recordset without a connection, using JET
This works, but for some reason the memory requirements are large.
Using an ODBC source for the same data seems to use almost no memory!
require “win32ole”
rs = WIN32OLE.new(“ADODB.recordset”)
qry = “select date, dialednumber, extension, cost from callrecords where
call = 2 and date >=#01-jan-2005#”
constr = ‘Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\ruby\dev\calldata .mdb’
rs.open(qry,constr)
rs.getrows.each do |row|
puts row
end
##################################################################
Recordset without connection using an ODBC source
Program takes about 28MB mem vs. 39MB for the JET version
The difference is massive when working with a lot of data
Requires that you create the ODBC data source!
require “win32ole”
rs = WIN32OLE.new(“ADODB.recordset”)
qry = “select date, dialednumber, extension, cost from callrecords where
call = 2 and date >=#01-jan-2005#”
rs.open(qry,“DSN=calldata;”)
rs.getrows.each do |row|
puts row
end
##################################################################
Here’s what I used in my application - an array that can get data from
the database.
Can you improve on this code, or offer suggestions?
class Array
def readMDB(qry,dsn)
rs = WIN32OLE.new(“ADODB.recordset”)
rs.open(qry,“DSN=#{dsn};”)
rs.getrows.each do |row|
self << row
end
end
def rotate
tmp = self.clone
self.clear
j = 0
tmp[0].length.times do
tmp.each_index do |i|
self[j] = [] if not self[j]
self[j][i] = tmp[i][j]
end
j += 1
end
end
end