brant interactive

We’re converting a legacy administrative app from ColdFusion, which runs SQL Server, to Ruby on Rails. Generally speaking, it’s all going very well. There are few important adjustments that have to be made, though, both becasue a legacy app, by nature won’t likely conform to the Rails conventions (table, column names) and also because you can’t connect to sql server right out of the box. Some of this stuff is in the rails wiki, but I tend to blog this stuff, so I don’t loose it, or forget what I had to do 2 months from now when I need to do it again.

basic connection

Get the latest source distribution of Ruby-DBI and copy the file:

src/lib/dbd_ado/ADO.rb

to:

X:/ruby/lib/ruby/site_ruby/1.8/DBD/ADO/ADO.rb 

(you will need to create the ADO directory).

Then simply set up your railsapp/config/database.yml

Here’s mine as a reference:

development:
adapter: sqlserver
database: database_name
host: DBI:ADO:Provider=SQLOLEDB;Data Source=server_name;Initial Catalog=database_name;User Id=user_name;Password=your_pw_here;
username: user_name
password: your_pw_here

has and belong to many relationships require a little extra love and are also case sensative.

Here’s an example of the detail:

  has_and_belongs_to_many :companies,
    :class_name => 'Company',
    :join_table => 'EngagementCompany',
    :foreign_key => 'EngagementID',
    :association_foreign_key => 'CompanyID'

meta-data case-sensitivity
Our SQL Servers are full of mixed-case field names, and are not entirely consistent in their various forms(local, staging,production, etc). I haven’t actually done this, but it would probably make things easier to just lowercase it all. Here’s how:

sqlserver_adapter.rb (found at – x:\Ruby\lib\ruby\gems\1.8\gems\activerecord-1.10.1\lib\active_record\connection_adapters\sqlserver_adapter.rb) to lowercase all the column names. In the columns(table_name, name=nil) method, I turned

SELECT COLUMN_NAME as…

into

SELECT LOWER(COLUMN_NAME) as…

(around line 178), and in the private method select(sql, name, @connection) has a line

record[col] = row[col]

that became

record[col.downcase] = row[col]

Now all the attributes look lowercase.

I’ll add more to this as more fun challenges come up..