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

    Advertisements

One Response to Connecting to SQL Server from Unix with Ruby

  1. Pingback: Ruby talking to SQL Server on Unix / Mac « Ways of Ruby

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s