Using Ruby to get data from an Access Database

Ruby
1

Hi guys, I’m very new to this Ruby language.

I only have basic experience of creating Intranet applications using ASP
and an MS Access Database.

I’m trying to adapt some code from the www.Alexa.com 's example code but
don’t know if what I’m trying to attempt is out of my depth.

In its simplicisty this is what i’m trying to achieve,

connecting to an access database to return the web addresses for 1,000
firms, using the following Ruby code to query Alexa statistics and then
insert the result back into the access database.

#/usr/bin/ruby

require “cgi”
require “base64”
require “openssl”
require “digest/sha1”
require “uri”
require “net/https”
require “rexml/document”
require “time”

ACCESS_KEY_ID = “— Replace with your access key id —”
SECRET_ACCESS_KEY = “— Replace with your secret access key —”

action = “UrlInfo”
responseGroup = “Rank”
url = “yahoo.com”

timestamp = ( Time::now ).utc.strftime(“%Y-%m-%dT%H:%M:%S.000Z”)

signature = Base64.encode64( OpenSSL::HMAC.digest(
OpenSSL::Digest::Digest.new( “sha1” ), SECRET_ACCESS_KEY, action +
timestamp)).strip

url = URI.parse(

      "http://awis.amazonaws.com/onca/xml?" +
      {
        "Action"       => action,
        "AWSAccessKeyId"  => ACCESS_KEY_ID,
        "Signature"       => signature,
        "Timestamp"       => timestamp,
        "ResponseGroup"   => responseGroup,
        "Url"           => url
      }.to_a.collect{|item| item.first + "=" +

CGI::escape(item.last) }.join(“&”) # Put key value pairs into http
GET format
)

print “\n\nRequest:\n\n”
print url

xml = REXML::Document.new( Net::HTTP.get(url) )

print “\n\nResponse:\n\n”

xml.write

Any help would be greatly appreciated …

Cheers Andy

2

Andy wrote:

connecting to an access database to return the web addresses for 1,000
require “net/https”
timestamp = ( Time::now ).utc.strftime(“%Y-%m-%dT%H:%M:%S.000Z”)
“AWSAccessKeyId” => ACCESS_KEY_ID,
print “\n\nRequest:\n\n”

Cheers Andy

There are lots of ways to get data to and from an Access database,
depending on whether the Access database is on the same machine as the
Ruby script and whether the Ruby script is running on a Windows box or
not. The simplest case would be the database and Ruby script co-resident
on a Windows box. Then it can be done either with the OLE interface or
ODBC – ODBC is preferable since it’s more portable.

If you need the data from the Access database for a Ruby script running
on another box, the simplest thing is probably to write a small server
using distributed Ruby on the Windows box and access it over the
network. But there are other ways to skin the cat, ranging from an open
source package called “mdbtools” that reads and writes Access files
(http://mdbtools.sourceforge.net/) to more complicated things like
“unixODBC”.

3

Set rsHouse = Server.CreateObject(“ADODB.Recordset”)
adoCon.Open “DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=” &
Server.MapPath(“Rent_Management_System_V2.mdb”)
strSQL = “Select * From House”
rsHouse.Open strSQl, adoCon

Any idea how this sort of thing would work with Ruby (or
where I could go to find out)

I use DBI with the ODBC DBD and do stuff like this:

DBI.connect(“DBI:ODBC:driver=Microsoft Access Driver (*.mdb);
dbq=#{filename}”) do |conn|
conn.select_all(‘SELECT * FROM Pools’).collect {|row| row.to_h}.each
do |values|
pools[values[‘Key’]] = values
end
end

  • donald
4

There are lots of ways to get data to and from an Access database,
depending on whether the Access database is on the same machine as the
Ruby script and whether the Ruby script is running on a Windows box or
not. The simplest case would be the database and Ruby script co-resident
on a Windows box. Then it can be done either with the OLE interface or
ODBC – ODBC is preferable since it’s more portable.

The script would be running from the same machine as the database. I’ve
used ADO before with Access Databases so i’d probably stick with what I
know, bit stuck as to how I’d get the right syntax for Ruby, can’t find
exactly what i’m looking for out there on the web…

Dim adoCon
Dim rsHouse
Dim strSQL
Set adoCon = Server.CreateObject(“ADODB.Connection”)
Set rsHouse = Server.CreateObject(“ADODB.Recordset”)
adoCon.Open “DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=” &
Server.MapPath(“Rent_Management_System_V2.mdb”)
strSQL = “Select * From House”
rsHouse.Open strSQl, adoCon

Any idea how this sort of thing would work with Ruby (or where I could
go to find out)

Many thanks for your help

Andy

5

On 6/4/07, Andy R. [email protected] wrote:

I’ve used ADO before with Access Databases so i’d probably stick with
what I know, bit stuck as to how I’d get the right syntax for Ruby,

The win32ole library is your friend. This should work (almost direct
copy of VBS code):

require ‘win32ole’
ado_con = WIN32OLE.new(‘adodb.connection’)
rs_house = WIN32OLE.new(‘adodb.recordset’)
ado_con.open “DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=” +
base_dir = File.dirname(FILE) + “Rent_Management_System_V2.mdb”
str_sql = “Select * From House”
rs_house.open( str_sql, ado_con )