There are many excellent posts out there about how to connect Ruby to Microsoft SQLServer. The problem I had is that none of them actually worked for me! So, I’m going to add my list of instructions to the noise in hopes it helps someone out there
The Setup
I’m using Windows 7 32-bit. The Ruby I have installed I got from http://rubyforge.org/frs/download.php/72075/rubyinstaller-1.9.1-p430.exe and looks like this:
ruby -v
ruby 1.9.1p430 (2010-08-16 revision 28998) [i386-mingw32]
Secondly, and this is very important, I have DevKit installed. I downloaded http://github.com/downloads/oneclick/rubyinstaller/DevKit-4.5.0-20100819-1536-sfx.exe and installed it according to the instructions on the DevKit wiki. Many of the tutorials out there neglect to mention that you must have DevKit installed in order to install some of the gems required.
The instructions I’m giving you here only work with *exactly* the setup I described. If you have a slightly different setup then this might not work. It’s very finicky. Even having a different one click installer can make it not work.
Step By Step From a Fresh Ruby Install
I installed my ruby to d:\Ruby191 with the one click installer. Then I extracted DevKit to d:\Ruby191\devkit and ran:
cd d:\Ruby191\devkit
ruby dk.rb init
ruby dk.rb review
ruby dk.rb install
When running “ruby dk.rb review” make sure it says it’s pointing to your Ruby install.
Next I downloaded dbi-0.4.3.gem, dbd-odbc-0.2.5.gem and ruby-odbc-0.99992.gem to my local file system.
Next, I ran exactly these commands. The versions and extra arguments matter!
set RUBY_HOME=D:\Ruby191
set LIBS=..\..\libs\ruby
gem install rake
gem install rubygems-update
gem install --include-dependencies rails
gem install deprecated --version=2.0.0 @rem the version matters. get exactly this one
gem install --local %LIBS%\dbi-0.4.3.gem
gem install --local %LIBS%\dbd-odbc-0.2.5.gem
gem install --local %LIBS%\ruby-odbc-0.99992.gem @rem this requires devkit
Lastly, I wrote this sanity check script and it worked:
require 'dbi'
# Replace MY_DSN with the name of your ODBC data
# source. Replace and dbusername with dbpassword with
# your database login name and password.
DBI.connect('dbi:ODBC:MY_DSN_NAME', 'username', 'password') do | dbh |
# Replace mytable with the name of a table in your database.
p "selecting getdate"
dbh.select_all('select getdate()') do | row |
p row
end
p "done"
end
So that’s it. Another of the many tutorials out there that will work on exactly one setup and probably not much more. Have fun!