Using ADO to connect to an Access database in Ruby

[arobbo]

Hi guys

I'm trying to get Alexa web stats on 1,000 firms websites. I have the
website addresses etc in an MS Access database.

I've used the example Ruby code from Alexa Web Information Service to
get web stats manually using Ruby but am a little bit stuck as to the
next two steps to achieve my goal.

Step 2. How do I connect to an Access Database to gather my URLs ?
In the past i've used ADO to connect to an MS Access database

and

Step 3. How would I insert the results back into the database ?

Any ideas on how i'd go about steps 2 and 3 would be greatly
appreciated

Cheers

Andy

 

[Dave Burt]

Step 2. How do I connect to an Access Database to gather my URLs ?
In the past i've used ADO to connect to an MS Access database

Step 3. How would I insert the results back into the database ?

The simple way is to export the list from the database into a text file
(CSV would be an obvious choice), get Ruby to read that text file and
write a new one, then import that back in.

The direct way is to use DBI to connect to the database from Ruby; see
http://ruby-dbi.rubyforge.org/

Your code might look something like this:

db = DBI.connect(
"DBI:ODBC:driver=Microsoft Access Driver (*.mdb); dbq=my.mdb")

list = db.select_all("select * from firms")

list.each do |row|
ranking = get_ranking_for row['url']
db.execute \
"update rankings set ranking='#{ranking}' where id=#{row['id']}"
end

Cheers,
Dave

 

[Uma Geller]

Step 2. How do I connect to an Access Database to gather my URLs ?

In the past i've used ADO to connect to an MS Access database

begin
require 'win32ole'
rescue LoadError
puts 'no win32ole available'
exit(1)
end
conn = WIN32OLE.new('adodb.connection')
table = WIN32OLE.new('adodb.recordset')
conn.open " DBQ=mydatabase.mdb; DRIVER={Microsoft Access Driver (*.mdb)};"

sql = "SELECT URL FROM ADDRESSBOOK"
table.open( sql, conn )

while not table.EOF
rows = table.GetRows(1)
p rows
end

# or, if you prefer, retrieve all rows in a single pass
#table.MoveFirst
#p table.GetRows

Step 3. How would I insert the results back into the database ?

same as before, but using INSERT instead of SELECT

best regards,

UG

 

[Andy Robbo]

Thanks very much for all the imput guys, seems like I inadvertantly
posted this question twice in this forum (appologies for that!!!)

I'm making progress ..........

#/usr/bin/ruby

begin
require 'win32ole'
rescue LoadError
puts 'no win32ole available'
exit(1)
end

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("C:\Documents and Settings\Andy\Desktop") +
"Alexa.mdb"

str_sql = "Select * From sample"

rs_house.open( str_sql, ado_con )

print rs_house("sample_URL")

..................................................................

Given my freshness to Ruby at this stage I just want to make sure I can
get access to the database and get something showing on screen.

I have the OneClick windows installer version of Ruby on my machine, I'm
currently getting this error message when running the code...

dbtest.rb:15:in `method_missing': open (WIN32OLERuntimeError)
OLE error code:80004005 in Microsoft OLE DB Provider for ODBC
Drivers
[Microsoft][ODBC Microsoft Access Driver] Could not find file
'(unknown)'.
HRESULT error code:0x80020009
Exception occurred. from dbtest.rb:15

.......................................................

Can anyone see anything I'm doing which is obviously wrong at this stage
?

Many thanks

Andy

 

[ChrisH]

ado_con.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" +
base_dir = File.dirname("C:\Documents and Settings\Andy\Desktop") +
"Alexa.mdb"

....
Inside double-quotes, the '\' is the escape character.
You can switch to single-quotes, or use the '/' as path separator

You use File.dirname, but this will strip off the last path element so
File.dirname("C:\Documents and Settings\Andy\Desktop")
returns
C:\Documents and Settings\Andy

and need to ensure a path separator is placed between 'Desktop' and
'Alexa.mdb'

I'd probably use:

base_dir = "C:/Documents and Settings/Andy/Desktop/"
ado_con.open("DRIVER={Microsoft Access Driver (*.mdb)};
DBQ=#{base_dir}Alexa.mdb")

Cheers
Chris

 

[Andy Robbo]

Right , very close to achieving my objective , thanks very much for
everyones input so far , its really helped me along the learning curve
with Ruby.

I'm down to one last problem ...

So far , I've connected to my Access database and pulled out a list of
URLs and there ID's , i've used the URL in the code from Alexa to obtain
the page rank.

Now all I have to do is somehow parse the XML from the Alexa data back
into Ruby to insert it back into my database...

This is the XML that i'm trying to parse

Response:

<?xml version='1.0'?>
<aws:UrlInfoResponse
xmlns:aws='http://alexa.amazonaws.com/doc/2005-10-05/'><aws:Response
xmlns:aws='http://awis.amazonaws.com/doc/2005-07-11'><aws:OperationRequest><aws:RequestId>asldkfjaslkdjfasldfj</aws:RequestId></aws:OperationRequest><aws:UrlInfoResult><aws:Alexa>
<aws:TrafficData>
<awsataUrl type='canonical'>rbstardynamic.co.uk/</awsataUrl>
<aws:Rank>5976859</aws:Rank>
</aws:TrafficData>
</aws:Alexa></aws:UrlInfoResult><aws:ResponseStatus
xmlns:aws='http://alexa.amazonaws.com/doc/2005-10-05/'><aws:StatusCode>Success</aws:StatusCode></aws:ResponseStatus></aws:Response></aws:UrlInfoResponse>"5"
"essex-electrical.co.uk"

I'm trying to parse the rank # (in this case "5976859")

any clues ?

 

[M. Edward (Ed) Borasky]

Thanks very much for all the imput guys, seems like I inadvertantly
posted this question twice in this forum (appologies for that!!!)

I'm making progress ..........

#/usr/bin/ruby

begin
require 'win32ole'
rescue LoadError
puts 'no win32ole available'
exit(1)
end

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("C:\Documents and Settings\Andy\Desktop")

That should be "C:\\Documents and Settings\\Andy\\Desktop" ... '\' is an
escape character inside the Ruby double-quoted string, so to ask for one
backslash you need to use two of them. What Windows is seeing the way
you coded it is "Cocuments and SettingsAndyDesktop".

+
"Alexa.mdb"

str_sql = "Select * From sample"

rs_house.open( str_sql, ado_con )

print rs_house("sample_URL")

..................................................................

Given my freshness to Ruby at this stage I just want to make sure I can
get access to the database and get something showing on screen.

I have the OneClick windows installer version of Ruby on my machine, I'm
currently getting this error message when running the code...

dbtest.rb:15:in `method_missing': open (WIN32OLERuntimeError)
OLE error code:80004005 in Microsoft OLE DB Provider for ODBC
Drivers
[Microsoft][ODBC Microsoft Access Driver] Could not find file
'(unknown)'.
HRESULT error code:0x80020009
Exception occurred. from dbtest.rb:15

.......................................................

Can anyone see anything I'm doing which is obviously wrong at this stage
?

Many thanks

Andy

 

[ChrisH]

Right , very close to achieving my objective , thanks very much for
everyones input so far , its really helped me along the learning curve
with Ruby.

I'm down to one last problem ...

So far , I've connected to my Access database and pulled out a list of
URLs and there ID's , i've used the URL in the code fromAlexato obtain
the page rank.

Now all I have to do is somehow parse the XML from theAlexadata back
into Ruby to insert it back into my database...

This is the XML that i'm trying to parse

Response:

<?xml version='1.0'?>
<aws:UrlInfoResponse
xmlns:aws='http://alexa.amazonaws.com/doc/2005-10-05/'><aws:Response
xmlns:aws='http://awis.amazonaws.com/doc/2005-07-11'><aws:OperationRequest><aws:RequestId>asldkfjaslkdjfasldfj</aws:RequestId></aws:OperationRequest><aws:UrlInfoResult><aws:Alexa>
<aws:TrafficData>
<awsataUrl type='canonical'>rbstardynamic.co.uk/</awsataUrl>
<aws:Rank>5976859</aws:Rank>
</aws:TrafficData>
</aws:Alexa></aws:UrlInfoResult><aws:ResponseStatus
xmlns:aws='http://alexa.amazonaws.com/doc/2005-10-05/'><aws:StatusCode>Success</aws:StatusCode></aws:ResponseStatus></aws:Response></aws:UrlInfoResponse>"5"
"essex-electrical.co.uk"

I'm trying to parse the rank # (in this case "5976859")

any clues ?

I think the simplest way would be to store this XML in an String and
then:
start = xml.index('<aws:Rank>') + '<aws:Rank>'.length
fin = xml.index('</aws:Rank>')
rank = xml[start...fin]

Of course you could explore RegEx or an XML lib (Hpricot, ReXML,...)

Cheers
Chris