Tuesday, February 1, 2011

Rails 3 with SQLServer 2000

I've been working on a new front-end to our legacy system and was disappointed to find that ActiveRecord 3 would not support SQLServer 2000 due to some limitations with the Arel gem. So after trying without much success to find a patch that would make this work, I wrote my own.

One big change is that the table reference for :set_table_name is now case sensitive. I don't believe it was in the past. So either make sure those names match or you will see cryptic messages like:

NoMethodError: undefined method 'eq' for nil:NilClass (yeah that was fun to track down)

There are a couple of other basic changes that need to happen so make it work at all. You need to change the SUPPORTED_VERSIONS constant and also change the TOP definition in the Arel SQLServer Visitor ...

module ActiveRecord
  module ConnectionAdapters
    class SQLServerAdapter
      SUPPORTED_VERSIONS = [2000,2005,2008].freeze

module Arel
  module Nodes
    module Visitors
      class SQLServer
        def visit_Arel_Nodes_Limit(o)
          "TOP #{visit o.expr}"

This will get the basics to work. You still won't be able to use some of the advanced features like offsets without further modification but this did the job for us. Well almost, there were a few other changes that needed to be made in our case since our column names are mixed-case. If you would like more info on these, let me know in the comments.

All the code here is found within the activerecord-sqlserver-adapter gem. I created overrides for all the methods and placed them inside the Rails app so that gem updates wouldn't cause any issues.


spinks.a said...

thanks for the post!

Thomas W. Shelton said...

I've done a little more work on it over the last year and have Rails 3.2 connecting to SQLServer 2000 if anyone is interested.

Phil said...

Thanks for your post. We're still running a Rails 2 app because of the SQL Server 2000 limitation. I'd be very interested to see what else you've done if you have time to share.

Thomas W. Shelton said...

I have a cloned Activerecord-sqlserver-adapter repository at github/twshelton that gets pretty close to solving the problem. The one thing we ran into was the date conversion the new adapter uses and you may need to modify ruby-odbc code to get it to work correctly.

spinks.a said...

Again! You keep saving me!

Rafa said...

Hi Thomas. We are using Rails 4.0.4. We spend many time trying using sqlserver 2000. We try with ODBC, with Freetds without success. I tried to use your gem but we have problems.

First one is:
ArgumentError: wrong number of arguments (2 for 1)
from /home/fi4/rails/bolero31srv/shared/bundle/ruby/2.1.0/bundler/gems/activerecord-sqlserver-adapter-340f3d696bdc/lib/arel/visitors/sqlserver.rb:93:in `visit_Arel_Nodes_SelectStatement'
from /home/fi4/rails/bolero31srv/shared/bundle/ruby/2.1.0/gems/arel-4.0.2/lib/arel/visitors/visitor.rb:22:in `visit'
from /home/fi4/rails/bolero31srv/shared/bundle/ruby/2.1.0/gems/arel-4.0.2/lib/arel/visitors/visitor.rb:5:in `accept'
from /home/fi4/rails/bolero31srv/shared/bundle/ruby/2.1.0/gems/activerecord-4.0.4/lib/active_record/connection_adapters/abstract/database_statements.rb:13:in `to_sql'
from /home/fi4/rails/bolero31srv/shared/bundle/ruby/2.1.0/gems/activerecord-4.0.4/lib/active_record/connection_adapters/abstract/database_statements.rb:32:in `select_all'
from /home/fi4/rails/bolero31srv/shared/bundle/ruby/2.1.0/gems/activerecord-4.0.4/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
from /home/fi4/rails/bolero31srv/shared/bundle/ruby/2.1.0/gems/activerecord-4.0.4/lib/active_record/querying.rb:36:in `find_by_sql'
from /home/fi4/rails/bolero31srv/shared/bundle/ruby/2.1.0/gems/activerecord-4.0.4/lib/active_record/relation.rb:585:in `exec_queries'
from /home/fi4/rails/bolero31srv/shared/bundle/ruby/2.1.0/gems/activerecord-4.0.4/lib/active_record/relation.rb:471:in `load'
from /home/fi4/rails/bolero31srv/shared/bundle/ruby/2.1.0/gems/activerecord-4.0.4/lib/active_record/relation.rb:220:in `to_a'
from /home/fi4/rails/bolero31srv/shared/bundle/ruby/2.1.0/gems/activerecord-4.0.4/lib/active_record/relation.rb:573:in `inspect'
from /home/fi4/rails/bolero31srv/shared/bundle/ruby/2.1.0/gems/railties-4.0.4/lib/rails/commands/console.rb:90:in `start'
from /home/fi4/rails/bolero31srv/shared/bundle/ruby/2.1.0/gems/railties-4.0.4/lib/rails/commands/console.rb:9:in `start'
from /home/fi4/rails/bolero31srv/shared/bundle/ruby/2.1.0/gems/railties-4.0.4/lib/rails/commands.rb:62:in `'
from script/rails:6:in `require'
from script/rails:6:in `'

It seem that this release is not compatible with Rails 4.0.4. Have you any version Rails 4.x that run with sqlserver 2000?

Thanks for your help.

expect {your_answer}.to succesfull_answer