Using ADO to connect to an Access database in Ruby

Discussion in 'Ruby' started by arobbo, Jun 4, 2007.

  1. arobbo

    arobbo Guest

    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
    arobbo, Jun 4, 2007
    #1
    1. Advertising

  2. arobbo

    Dave Burt Guest

    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
    Dave Burt, Jun 5, 2007
    #2
    1. Advertising

  3. arobbo

    Uma Geller Guest

    > 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
    Uma Geller, Jun 5, 2007
    #3
  4. arobbo

    Andy Robbo Guest

    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/.
    Andy Robbo, Jun 5, 2007
    #4
  5. arobbo

    ChrisH Guest

    On Jun 5, 4:29 am, Andy Robbo <> 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
    ChrisH, Jun 5, 2007
    #5
  6. arobbo

    Andy Robbo Guest

    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>
    <aws:DataUrl type='canonical'>rbstardynamic.co.uk/</aws:DataUrl>
    <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/.
    Andy Robbo, Jun 5, 2007
    #6
  7. 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
    >
    >
    M. Edward (Ed) Borasky, Jun 5, 2007
    #7
  8. arobbo

    ChrisH Guest

    On Jun 5, 8:37 am, Andy Robbo <> 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>asldkfjaslkdjfasldfj</aws:RequestId></aws:OperationRequest><aws:UrlInfoResult><aws:Alexa>
    > <aws:TrafficData>
    > <aws:DataUrl type='canonical'>rbstardynamic.co.uk/</aws:DataUrl>
    > <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
    ChrisH, Jun 6, 2007
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. nita
    Replies:
    1
    Views:
    856
    Saravana
    Nov 20, 2004
  2. ronaldlee

    Transfer ADO Code to ADO.NET

    ronaldlee, Dec 17, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    445
    Kevin Spencer
    Dec 17, 2004
  3. Replies:
    0
    Views:
    1,302
  4. Navin
    Replies:
    1
    Views:
    674
    Ken Schaefer
    Sep 9, 2003
  5. Replies:
    2
    Views:
    272
Loading...

Share This Page