Connecting to SQL Server from Unix with Ruby

How to get Ruby on Rails (unix / mac) to work with SQL Server

The bulk of this page is a variation of this Wiki

Install ActiveRecord SQLServer adapater.

• sudo gem install activerecord-sqlserver-adapter –source=http://gems.rubyonrails.org

Four pieces of software need to be installed (aside from Ruby and Rails)

1. Unix ODBC
   http://www.unixodbc.org/unixODBC-2.2.12.tar.gz

2. FreeTDS
   http://www.freetds.org/

3. Ruby ODBC Driver
   http://www.ch-werner.de/rubyodbc/

4. Ruby DBI
   http://rubyforge.org/projects/ruby-dbi

   Download Software

   Download software from above link.
   Or get a copy of everything from David Harkness

   As installed on a Mac and Redhat Enterprise

   Env Variables

   run in the console
   
export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc
export FREETDSCONF=/etc/freetds.conf


   Add the following to /etc/profile
   
ODBCINI=/etc/odbc.ini
ODBCSYSINI=/etc
FREETDSCONF=/etc/freetds.conf


   Unix ODBC

   
./configure --prefix=/usr/local --sysconfdir=/etc --disable-gui
make
make install

   Add the following to /etc/odbc.ini
   
[aspect]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = aspect
Database = cnxrpt_Aspect

   [callreporting]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = reportingdb
Database = MyReportingDatabase


   FreeTDS

   
./configure --with-unixodbc=/usr/local --sysconfdir=/etc # Mac's and most Unix Systems
./configure --with-odbc=/usr/local/lib --sysconfdir=/etc # If RedHat
make
make install


   Add the following to /etc/freetds.conf

[reportingdb]
host = Reportingdb.mycompany.com
port = 1433
tds version = 8.0


   Add the following to /etc/odbcinst.ini
   
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/local/lib/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1

   ---

   ''' Test 1 '''
* tsql -S reportingdb -U 'username' -P fakepassword -I /etc/freetds.conf
''' Test 2 '''
* isql aspect 'username' 'fakepassword'


   Ruby ODBC Driver

   
tar -xzf ruby-odbc-0.9995.tar.gz
cd ruby-odbc-0.9995
ruby extconf.rb
make
make install


   Ruby DBI

   
tar -xzvf dbi-0.1.1.tar.gz
cd ruby-dbi
ruby setup.rb config --with=dbi,dbd_odbc
ruby setup.rb setup
ruby setup.rb install

   Test Everything

   
irb
# irb1.8
irb(main):001:0> require "dbi"
=> true
irb(main):004:0> db = DBI.connect('dbi:ODBC:reportingdb','username','fakepassword')
=> #<DBI::DatabaseHandle:0xb7c3fb60 @trace_output=#, @trace_mode=2, @handle=#<DBI::DBD::ODBC::Database:0xb7c3fac0 @attr={}, @handle=#>>
irb(main):005:0> quit

   Ruby can now talk to SQL Server
   ----

   Testing Rails
   
rails deleteme
cd deleteme
ruby script/generate model Aspect
ruby script/generate controller aspect index


   vim app/models/aspect.rb
   
class Aspect "sqlserver",
:mode => "odbc",
:dsn => "callreporting",
:username => 'username',
:password => 'fakepassword')

   def self.names
   self.content_columns.collect {|x| x.name}
   end

   end


   vim app/controllers/aspect_controller.rb
   
class AspectController < ApplicationController
def index
@data = Aspect.find(:first)

   end
   end

   
   vim app/views/aspect/index.rhtml
   

   First row

   
   ruby script/server -p 5312 -d

   Vist http://myserver:5312/aspect