Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Ruby > Using ADO to connect to an Access database in Ruby

Reply
Thread Tools

Using ADO to connect to an Access database in Ruby

 
 
arobbo
Guest
Posts: n/a
 
      06-04-2007
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

 
Reply With Quote
 
 
 
 
Dave Burt
Guest
Posts: n/a
 
      06-05-2007
arobbo wrote:
> 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
 
Reply With Quote
 
 
 
 
Uma Geller
Guest
Posts: n/a
 
      06-05-2007
> 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

 
Reply With Quote
 
Andy Robbo
Guest
Posts: n/a
 
      06-05-2007
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

--
Posted via http://www.ruby-forum.com/.

 
Reply With Quote
 
ChrisH
Guest
Posts: n/a
 
      06-05-2007
On Jun 5, 4:29 am, Andy Robbo <(E-Mail Removed)> wrote:
....
> 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

 
Reply With Quote
 
Andy Robbo
Guest
Posts: n/a
 
      06-05-2007
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>asldkfjas lkdjfasldfj</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 ?

--
Posted via http://www.ruby-forum.com/.

 
Reply With Quote
 
M. Edward (Ed) Borasky
Guest
Posts: n/a
 
      06-05-2007
Andy Robbo wrote:
> 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
>
>



 
Reply With Quote
 
ChrisH
Guest
Posts: n/a
 
      06-06-2007
On Jun 5, 8:37 am, Andy Robbo <(E-Mail Removed)> wrote:
> 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>asldkfjas lkdjfasldfj</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 ?
>
> --
> Posted viahttp://www.ruby-forum.com/.


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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
Translating Oracle Package Call from ADO to ADO.Net dmiratsky@yahoo.com ASP .Net 0 02-14-2005 10:39 PM
Transfer ADO Code to ADO.NET ronaldlee ASP .Net 1 12-17-2004 04:08 PM
Accessing an ADO Recordset or Record from ADO.NET nita ASP .Net 1 11-20-2004 07:06 AM
Ado sort error-Ado Sort -Relate, Compute By, or Sort operations cannot be done on column(s) whose key length is unknown or exceeds 10 KB. Navin ASP General 1 09-09-2003 07:16 AM



Advertisments