RubyGarden Archives: Scripting Access

Editor's Note: Once upon a time, there was a website named RubyGarden.org, which contained many helpful links and articles. That website has recently disappeared. The following "Scripting Access" article was salvaged from the Google cache and is provided here in its entirety.

---

Here is a quick example on getting data from a Microsoft Access database.

Recordset with a connection, using JET. This works, but for some reason the memory requirements were 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 were 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:\data\calldata.mdb'

rs.open(qry,constr)

rs.getrows.each do |row|
    puts row
end

Recordset without connection using an ODBC source. My program took 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 a function you can drop at the top of your program that alters the definition of Object. It will work for loading access data into any object class that supports "<<" and "transpose" - such as Array. Transpose is required since the recordset comes back with the rows "rotated".

public
def readMDB(qry,dsn)
    rs = WIN32OLE.new("ADODB.recordset")
    rs.open(qry,"DSN=#{dsn};")
    rs.getrows.each do |row|
        self << row
    end
    self.transpose
end

Usage example

qry = "select date, dialednumber, extension, cost from callrecords"
dsn = "DSN=calldata;"

a = []
a.readMDB(qry, dsn)
p a

Question: Is there a way to use paramters on the query like the ones from DBI? E.g.: (note the '?')

select * from callrecords where call = ?

---