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 = ?
1 comment:
require "win32ole"
conn = WIN32OLE.new("ADODB.Connection")
conn["Provider"] = "Microsoft.Jet.OLEDB.4.0"
conn.Open('c:\access\person.mdb')
rs = conn.Execute("select name, age, address, phoneno from
persons")
rs.getrows.each do |row|
#here i want it(row) to convert into hash
end
conn.Close
I want to convert the rows i got from the table into hash.
How do i do this?
Post a Comment