MsAccess for Rails: Here's your rough connector.
Ok, after using this for quite a hefty project and it worked for all that I needed it for, I thought it has enough potential to be beneficial to someone, and I might as well let it out of the bag for that person.
Download the file at http://gist.github.com/raw/102271/40dc4d1de9135a94d7a8961d8f75907ad7b95de8/msaccess_adapter.rb. To install, plop it into your rails' connection_adapters directory (vendor / rails / activerecord / lib / active_record / connection_adapters).
Config should look like this:
development:Of course, using Microsoft Access files from legacy (non-rails) apps often includes several database files. I recommend using the 'use_db' plugin. I made a modification to make it easier to use -- open up lib/use_db.rb within the plugin and replace this method: http://pastie.caboo.se/78705. Then simply put in your model:
adapter: msaccess
database: C:\path\to\access_file.mdb
use_db :database => 'C:\path\to\other.mdb'
Remember also, when using Access databases, most of the time you will be using a database that was created for another purpose - probably with a different table naming scheme and a primary key that is not named 'id'. Therefore, you'll have to make good use of set_table_name and set_primary_key in order to have the right handles on things. Another couple points: Sometimes a table may not have a good primary key - as long as you're just reading data from it you can get by without any primary key at all - just don't reference a primary key and rails is happy as a clam! And if your primary key ends up being something other than an integer (for example, part numbers that aren't quite autoincrementing, and include characters), Rails and the msaccess_adapter are okay with that, just be careful to set your id before you create records. (That reminds me, I think I made a couple modifications to AR::Base to better accomodate these id things - if anyone comes up with problems related to this, I'll remember what those are and add them in)
This only works on Windows, because it uses ADO through WIN32OLE. I guess I must say, it requires the win32ole gem too. Just "sudo gem install win32ole" if you don't have it already. It comes prepackaged with InstantRails (a nice prepackaged rails environment for windows).
Last words on the adapter:
PLEASE PLEASE contribute. If you fix the binary_to_string / string_to_binary functions, let me know and I will integrate your fix. That goes for anything else. Let me know what's broken, but don't rely on me to fix it. I will fix things as I need them fixed for my own applications, and if you fix something, please submit it to me (patches@behindlogic.com) for the good of everyone who wants to use this adapter. Thanks!
19 comments:
Dan, you forgot to mention this only works on windows boxes... You should update this post to note that..
Any change you'll open source an a restful application that utilizes this plugin, so we could put it on a windows box with the db and then access it from linux or osx?
Hmm, well Jon, you know I made that too, if you want, I might just open that up too. I just can't guarantee it conforms to ActiveResource standards, as I'm still learning how to do that all correctly.
I think I'll plan on opening that up -- what it will be is the dynamic API that I made, basically an automatic drop-in ActiveResource API for any Rails application. You know, the 100-line ApplicationController that does it all, along with the dynamically-generated routes. Manna from heaven is what it is.
"I think I'll plan on opening that up -- what it will be is the dynamic API that I made, basically an automatic drop-in ActiveResource API for any Rails application."
That would save what little hair I have left! I know time is always scarce, but that sure would be appreciated.
Anonymous and anyone else: The "automatic drop-in API for rails" that I was talking about has been posted at http://blog.behindlogic.com/2007/06/how-i-wrote-entire-rails-api-in-100.html, and the corresponding automatic routes at http://blog.behindlogic.com/2007/06/automatic-routes-for-my-api.html.
rename_table wasn't working for me, so I changed it to:
def rename_table(name, new_name)
ole_table(name.to_s).Name= new_name.to_s
#execute "EXEC sp_rename '#{name}', '#{new_name}'"
end
also, remove_column wasn't working when their were no special tables like: CONSTRAINT_COLUMN_USAGE so columns_from_catalog looks like:
def columns_from_catalog(table_name)
return self.catalog.Tables.Item(self.table_names_from_catalog.index(table_name)).Columns ole_table(table_name)
return []
end
You said ... That reminds me, I think I made a couple modifications to AR::Base to better accomodate these id things - if anyone comes up with problems related to this, I'll remember what those are and add them in .... I am having these problems. I'm not sure what the appropriate fix for insert is though.
Thanks Justin, it'd be cool if you email me at daiqsiolmo@kriocoudek.mailexpire.com and I'll email you back from my real address. I'd like to get in touch to integrate these additions you have!
Two things:
1) Have you thought about submitting this to the Rails-core people? Most likely they'd reject it, as MS Access isn't really good for this sort of thing, but might be worth a try.
2) Have you thought about making this a plugin if the above doesn't work? Looking over your code, everything seems in order.. but I hesitated to use this connector as I didn't feel like mucking about in the AR lib files. I will probably try it out anyways, but a plugin would have been the instance satisfaction I normally look for..
My $0.02.
-Rob
Farley, the other 98cents here:
1) I came to the same conclusion about submitting it as you did, I don't think msaccess is worth being in the core. Apparently they don't think oracle, firebird or sqlserver belong there anymore either, because they've just moved them all out into gems for Rails 2.0. Looks like I need to make this into a gem too. :)
2) Pluginizing... can't really pluginize an adapter, as it's needed before the plugin initializing sequence. Another vote for a gem.
Just waiting for some spare time to learn how to make a gem and do it to the adapter.
Thanks for the $.02!
i got nonexistent adapter error then I follow instruction from this link to fix it
http://wiki.rubyonrails.org/rails/pages/New+database+adapter
Add the expected name of your adapter into this line in activerecord-x.x.x\lib\active_record.rb:
68 unless defined?(RAILS_CONNECTION_ADAPTERS)
69 RAILS_CONNECTION_ADAPTERS = %w(mysql postgresql sqlite firebird sqlserver db2 oci jet)
70 end
You might have Rails 2.0 or later, which runs adapters as gems... I need to package this into a gem. :P
Other than that, yes, you might have to put in that line. :) Did that work?
Very interesting, will play around with this as I'm trying to grab a lot of data from a legacy (Access) database and handle information from old apps sending such data. I'll see where this wonderful info leads and post back results. The goal is to move it all to MySQL/PosstgreSQL, so this sounds very useful.
Hopefully someone's still reading. I'm running into difficulty when running a query with a condition on a hyphenated column. Like: select * from some_table where sr-st1 = 'blah'.
The query works fine when conditional on an unhyphenated column.
I end up with the ole 'No value given for one or more required parameters error'
I'll have a look under the covers later, but wondering if anybody has seen this already.
marc, if it was any other adapter I'd just leave it at "you shouldn't use fields with dashes in them!" but since this is primarily geared toward legacy systems (nobody should be developing a new application with msaccess as its data source), you have a valid problem. That said...
My biggest fear of stepping into that problem is that I believe ActiveRecord will have more trouble with it than the adapter will. Since my use for the MsAccess Adapter for Rails is pretty much complete, I'm not really developing further. However, if you do some poking around and find a way that seems to work, Please let me know so I can work it in if I think it's robust! Also, ANYONE feel free to put this on rubyforge as a gem and take over development.
Download link not responding
Link fixed, thanks.
I'm trying to install this "gem" of sorts lol. GREAT job by the way, this is the only place to find this kind of interaction.
So I create a project and a DB.mdb ( maybe we can get Accdb soon?) then I create a migration
ruby script/generate model Pet name:string description:text
then I rake db:migrate...My error is
rake aborted!
This database does not yet support migrations
(See full trace by running task with --trace)
So I'm researching how to fix this.. I wanted to share this for other who may get stuck, or for the author who may help. thanks..
Also I found a "gem" to do scaffolding for existing DBs, once I can get this Connector to work..
Later Guys.
http://github.com/ahe/reverse_scaffold
rhali786, thanks for the note; While this adapter is useful for connecting to MSAccess databases, its original purpose is pretty much out of necessity - to *get the data out* of those MSAccess db's! No plans have been made to make it easy to create new db's or support migrations. You'll have to do your migrations manually, or else figure out how to add it to this "plugin". If you end up adding it programmatically, let me know and I'll put it in.
Post a Comment