Thedwick

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'

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!

Want to know the difference between Java (or more precisely, Struts) and Ruby (or more precisely, Rails)? Here is an example that says it all.

New version of MyStats!. In this version, when you add new people to your group, they actually get an email asking them to sign up. Also, there are many other usability improvements.

So, a long time ago I promised to describe a problem I was having with a many-to-many relationship. You’ll need to strap on your seatbelt, because this one takes some explanation…

I’ve got this many-to-many relationship from a RegisteredUser to a Person that basically says “this registered user is allowed to report the status of these people.” Why do I have different classes for a registered user and a plain, old person? Because I want registered users to be able to keep track of other people who aren’t necessarily users of my application (like, say, my 18 month old daughter). You can think of RegisteredUser as a specialization of Person and, in fact, if I were doing things over again I would have made that literally so (my failure here will become important).

So anyway, I wanted to make this relationship bi-directional, so that for each member of this:

a_registered_user.authorized_reportees

the following would be true:

a_person.authorized_reporters.include?(a_registered_user)

Sounds pretty easy, right? I just had to model it like this:

class RegisteredUser < ActiveRecord::Base
  belongs_to :person
  has_and_belongs_to_many :authorized_reportees, :class_name => 'Person',
        :join_table => 'authorized_reporters'
end

class Person < ActiveRecord::Base
  has_and_belongs_to_many :authorized_reporters, :class_name =>'RegisteredUser',
        :join_table => 'authorized_reporters'
  has_one :registered_user
end

Turns out that didn’t work and it all hinges (I think) on the fact that I’ve got two relationships between RegisteredUser and Person:
1. RegisteredUser.person/Person.registered_user which is where RegisteredUser acts as a specialization of Person, and
2. RegisteredUser.authorized_reportees/Person.authorized_reporters which is the many-to-many.

With this setup, what would you predict the first name in this code is? The first name of “@person” or the first name of “reporter”?:

for reporter in @person.authorized_reporters
	reporter.person.first_name
end

What I meant it to be was the first name of “reporter” but through the magic that is ActiveRecord what it ended up being was the first name of “@person”. I still don’t know why, but I do know that once I changed the code to this, I got what I wanted:

class RegisteredUser < ActiveRecord::Base
  belongs_to :person
  has_and_belongs_to_many :authorized_reportees, :class_name => 'Person',
        :join_table => 'authorized_reporters',
        :foreign_key => 'authorized_registered_user_id',
        :association_foreign_key => 'authorized_person_id'
end

class Person < ActiveRecord::Base
  has_and_belongs_to_many :authorized_reporters, :class_name =>'RegisteredUser',
        :join_table => 'authorized_reporters',
        :foreign_key => 'authorized_person_id',
        :association_foreign_key => 'authorized_registered_user_id'
  has_one :registered_user
end

I had to explicitly specify the foreign keys and change the names of the database columns from person_id to authorized_person_id. Then everything worked fine. Go figure.