ODBC via Ruby on Windows
I just got Ruby to connect to a database via ODBC and the dbi gem (particularly, MSSQL). I ran into a few problems along the way, so here are some of my notes.
DBI
Make sure you have the dbi and dbd-odbc gems installed:
gem install dbi
gem install dbd-odbc
Next, you’ll want to make sure that you have the odbc.so
and odbc_utf8.so
files in your Ruby installation (check your lib\ruby\1.8\i386-mingw32
folder). If you do not have these, go grab i386-msvcrt-ruby-odbc.zip
from here:
http://www.ch-werner.de/rubyodbc/
You can now use dbi like so:
require 'rubygems'
require 'dbi'
DBI.connect('DBI:ODBC:test_dsn') do | dbh |
dbh.select_all('select * from sys.tables') do | row |
p row
end
end
ActiveRecord
You can also use active_record
. First, go grab odbc-rails
from here:
http://odbc-rails.rubyforge.org/
If you go to the download page, you’ll see that there are two versions: 1.5 and 2.0 for Rails 1.x and 2.x, respectively. Doing a gem install odbc-rails resulted in 1.5 being installed on my machine, so I downloaded the “activerecord-odbc-adapter-2.0.gem”http://rubyforge.org/frs/download.php/35985/activerecord-odbc-adapter-2.0.gem file from rubyforge and installed that. Once you’ve installed the gem, you can follow the instructions on the odbc-rails homepage to use it from Rails, or you can use some code like the following if you want to use active_record outside of a Rails project:
require 'rubygems'
require 'active_record'
ActiveRecord::Base.establish_connection(
:adapter => "odbc",
:dsn => "test")
class Person < ActiveRecord::Base
end
p Person.find(:all)
Note: If you’re connecting specifically to SQL Server, it’s probably a better idea to use the activerecord-sqlserver-adapter gem. Here’s a good link on that topic:
http://rubyrailsandwindows.blogspot.com/2008/03/rails-2-and-sql-server-2008-on-windows_24.html
Alternatives
There are also a few other alternatives. Check these out: