Rails and SQL Server Update
November 28th, 2006 by papillon
After a couple of weeks working with Ruby on Rails and SQL Server 2005, certain aspects slowly crystalize:
1. The ADO DB driver (which uses win32ole) does not work on Ruby 1.8.4 and 1.8.5. I can not help but wonder who besides me actually uses Rails and SQL Server 2005 ? It justs segfaults on the very first access. So: Do not use it.
2. The ODBC driver of ActiveRecord 1.14.4 does not like the way the SQLServer Adapter creates the requests of the paginator class (SELECT TOP 20 (SELECT …)). It just stops with an “argument out of range” error.
3. What actually does work is this combination: Ruby 1.8.5 + Rails 1.1.6.5618 (Release Candidate 1 of Rails 1.2), using the ODBC driver. The ODBC connection seems to be preferred over the ADO one, and with the latest changes to the SQL Server Adaptor in ActiveRecord 1.14.4.5618, the argument out of range error is gone as well.
4. Decimal columns seem to work fine now. Prior to the update, I had the problem that “1,5″ always got casted into “1.0″, maybe because the separator is a comma and not a dot in my case.
5. What still does not work: DateTime columns with dates prior to the year that the Ruby Time class accepts, so this change is necessary:
In \ruby\lib\ruby\gems\1.8\gems\activerecord-1.14.4.56.18\
lib\active_record\connection_adapters\sqlserver_adapter.rb around line 105 (cast_to_datetime) and line 116 (string_to_time):
change
return Time.mktime(value.year, value.mon, value.day, value.hour, value.min, value.sec)
to
return DateTime.new(value.year, value.mon, value.day, value.hour, value.min, value.sec)
If the column is a DateTime, ActiveRecord should return a DateTime, IMHO.
Dates like 1754-01-01 14:00:00.000 work with this change. Note: 1753 is the earliest date accepted by SQL Server, and ActiveRecord really should accept it.
6. Since I have to work with a legacy database which uses ISO-8859-1 as encoding, the whole rails application has to use the same. Rails 1.2 seems to default to UTF-8, which e.g. breaks the truncate function when it encounters an Umlaut.
Fix: in environment.rb, add the following lines
...
# Include your application configuration below
# Set multibyte encoding to none.
$KCODE = "NONE"
Bottom line: Update to Rails 1.2 and use the ODBC connection for SQL Server 2005.