[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

Using ADO to connect to an Access database in Ruby

Andy

6/4/2007 5:47:00 PM

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
5 Answers

Dave Burt

6/5/2007 4:56:00 AM

0

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.ruby...

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

6/5/2007 6:41:00 AM

0

> 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

6/5/2007 8:30:00 AM

0

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-....

Chris Hulan

6/5/2007 12:08:00 PM

0

On Jun 5, 4:29 am, Andy Robbo <arobb...@hotmail.com> 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

M. Edward (Ed) Borasky

6/5/2007 12:39:00 PM

0

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 "C:Documents 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
>
>