Ruby Programming Language :: Using ADO to connect to an Access database in Ruby


	Using ADO to connect to an Access database in Ruby 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 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 > 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 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/. 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 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:Respons e></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/. - Hide quoted text - 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". - Hide quoted text - > + > "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 On Jun 5, 8:37 am, Andy Robbo <arobb@hotmail.com> wrote: - Hide quoted text - > 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:Respons e></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