ActiveRecord, ODBC and SQL Server

I was required to use MS SQL Server with my Ruby while my application is running on Linux.
I do not like such things, because like always, stuff goes wrong, and when the close golden gates of Microsoft closes on you, well – you are theirs, and they decide what will work, and who can play …

In order to make things work on Linux, I had first to install unixODBC and FreeTDS .After installing them I was required to configure them, and that's a bit of a pain …

The people who are responsible for your database must create a user name and password that belongs to the database and not to Windows itself. Make them test the connection by creating their own ODBC connection and make sure it worked for them. It's the only test I found to actually make them understand that they did things wrong, instead of blaming Linux etc…

When their work is done, make sure you know the SQL Server version (2000,2005 etc..) for making FreeTDS works properly. Then configure it like so:

freetds.conf

[MyConncection]
host=MyServer
port=1433
tds version=9.0

odbcinst.init:

[FreeTDS]
Driver=/usr/lib/libtdsodbc.so
UsageCount=1
FileUsage=1
tds version=9.0

libtdsodbc.so might be located elsewhere like /usr/lib64/ or named differently (full version for example).

odbc.init:

[MyConnection]
Driver=FreeTDS
Server=MyServer
User=MyUserName
Password=MyGoodPassword
Database=MyDB
Trace=Yes
TraceFile=/tmp/odbc.trace
TDS_Version=9.0
Port=1433

In order to make things works, in CLI we should do the following:

$ isql -v MyConnection MyUserName MyGoodPassword

If everything goes well, you will have a nice prompt to use.

In order to use ruby with SQL Server and ActiveRecord you should install the following:

$ gem install activerecord-sqlserver-adapter ruby-odbc
My stand alone Ruby code:

#!/usr/bin/env ruby
#

require 'rubygems'
require 'active_record'

ActiveRecord::Base.establish_connection(
  :adapter  => 'sqlserver',
  :mode     => 'odbc',
  :dsn      => 'MyConnection',
  :database => 'MyDB',
  :username => 'MyUserName',
  :password => 'MyGoodPassword'
)

class MyTable < ActiveRecord::Base
  set_table_name 'MyTable'
end

rec = MyTable.first
puts rec.myfield

Please change every test that start with "My" to your own configuration.

Another important tip: Make sure that the fields that are created in each table are lower case, because some create them in upper case, and well, Ruby consider something with upper case, a constant or a mistake (in the middle of the method).

I hope this will help you to get started …

3 מחשבות על “ActiveRecord, ODBC and SQL Server

  1. artyom

    Few additional points:

    a. FreeTDS does not support "wide characters" basically saying that you can't use Unicode with MS SQL Server
    b. It is not something explicitly FreeTDS related it is just ODBC… It is CRAP – and I mean total crap the average quality of ODBC drivers is way below any reasonable quality for any DB not just MS SQL Server
    c. Recently Microsoft created their own ODBC driver for Linux. See: http://mateusz.loskot.net/2011/10/28/microsoft-announces-sql-server-odbc-driver-for-linux/ maybe you can give it a try and tell us?
    d. AFAIK there some commercial ODBC drivers that should be better for this – supporting UTF-8 properly

  2. a

    אגב, מה לא היה טוב?
    אני הצלחתי אפילו לעשות כמה דברים שלא הצלחתי לעשות בחלונות… אולי הייתי מצליח אם הייתי מתאמץ, אבל לשם מה, אם יש פיתרון פתוח ופשוט?

להשאיר תגובה

הזינו את פרטיכם בטופס, או לחצו על אחד מהאייקונים כדי להשתמש בחשבון קיים:

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s