Accessing MSSQL with Ruby

1. unixODBC

wget http://www.unixodbc.org/unixODBC-2.3.0.tar.gz
tar vxfz unixODBC-2.3.0.tar.gz;cd unixODBC-2.3.0
./configure; make; make install

2. freedts

wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
tar vxfz freetds-stable.tgz; cd freetds-0.82
./configure --with-unixodbc=/usr/local ; make; make install

Database information

Server: 10.0.1.70
Database Type: MSSQL 2000
Database Name: dbintegration
Username: epres_amir
Password: epres_amir8899

Configuration File
1. /usr/local/etc/freetds.conf

[dbintegration]
host = 10.0.10.73
tds version = 7.0
port = 1433

2 /usr/local/etc/odbcinst.ini

[FreeTDS]
Description = FreeTDS unixODBC Driver
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/local/lib/libtdsodbc.so
UsageCount = 1

3. /usr/local/etc/odbc.ini

[dbintegration]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = dbintegration
Database = dbintegration

Testing Connection to DB

isql -v dbintegration epres_amir epre_amir8899

or

TDSVER=7.0 tsql -H 10.0.1.70 -p 1433 -U epres_amir -P epres_amir8899

Getting ruby to work with freeTDS

Install dbi & dbd-odbc/unixODBC

gem install dbi
gem install dbd-odbc

Sample ruby code

#!/usr/bin/env ruby
require 'rubygems'
require 'dbi'
db = DBI.connect('dbi:ODBC:dbintegration', 'epres_amir', 'epres_amir8899')
select = db.prepare('SELECT TOP 100 * FROM ref_course')
#return schema select * from information_schema.columns where table_name = 'ref_course' order by ordinal_position

select.execute
while rec = select.fetch do
puts rec.to_s
end
db.disconnect

Sample output

["OAD011", "OAD011", "MENAIP", "KEYBOARDING", #, "Y", nil, "1", "OM", "N", "Y", "60", "160", "KI", nil, "PEP", #]
["MAT225", "MAT225", "KEJURUTERAAN MATEMATIK III", "ENGINEERING MATHEMATICS III", #, "Y", nil, "1", "EM", "N", "Y", "180", "0", "KI", nil, nil, #]
["HRM551", "HRM551", "PENGURUSAN PERUBAHAN", "MANAGEMENT OF CHANGE", #, "Y", nil, "1", "BM", "N", "Y", "180", "160", "KI", nil, "PEP", #]
["ICM358", "ICM358", "INST FOOD SERVICE MGMT", "INST FOOD SERVICE MGMT", #, nil, nil, "0", "HM", "N", "N", nil, nil, "KI", nil, nil, #]
["IDE256-2", "IDE256", "STUDIO PRESENT .II", "STUDIO PRESENT .II", #, nil, nil, "0", "AD", "N", "N", nil, nil, "KI", nil, nil, #]
["IDE256-3", "IDE256", "STUDIO PRESENT.II", "STUDIO PRESENT.II", #, nil, nil, "0", "AD", "N", "N", nil, nil, "KI", nil, nil, #]
["LAW333", "LAW333", "SUPERVISED RESEARCH", "SUPERVISED RESEARCH", #, nil, nil, "0", "AL", "N", "N", nil, nil, "KI", nil, nil, #]
["LAW354", "LAW354", "CIV PROC DRAFTING II", "CIV PROC DRAFTING II", #, nil, nil, "0", "AL", "N", "N", nil, nil, "KI", nil, nil, #]