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, #]

Rails on windows

1. Installing ruby

http://rubyforge.org/frs/download.php/71078/rubyinstaller-1.9.1-p378.exe

2. Installing rails

C:\Users\amir>gem install rails
Successfully installed rake-0.8.7
Successfully installed activesupport-2.3.8
Successfully installed activerecord-2.3.8
Successfully installed rack-1.1.0
Successfully installed actionpack-2.3.8
Successfully installed actionmailer-2.3.8
Successfully installed activeresource-2.3.8
Successfully installed rails-2.3.8
8 gems installed
Installing ri documentation for rake-0.8.7...
Installing ri documentation for activesupport-2.3.8...
Installing ri documentation for activerecord-2.3.8...
Installing ri documentation for rack-1.1.0...
Installing ri documentation for actionpack-2.3.8...
Installing ri documentation for actionmailer-2.3.8...
Installing ri documentation for activeresource-2.3.8...
Installing ri documentation for rails-2.3.8...
Updating class cache with 0 classes...
Installing RDoc documentation for rake-0.8.7...
Installing RDoc documentation for activesupport-2.3.8...
Installing RDoc documentation for activerecord-2.3.8...
Installing RDoc documentation for rack-1.1.0...
Installing RDoc documentation for actionpack-2.3.8...
Installing RDoc documentation for actionmailer-2.3.8...
Installing RDoc documentation for activeresource-2.3.8...
Installing RDoc documentation for rails-2.3.8...

3. Installing SQLite3

First you will need to download two files from the Sqlite web site http://www.sqlite.org/download.html:

- http://www.sqlite.org/sqlite-3_6_23_1.zip
- http://www.sqlite.org/sqlitedll-3_6_23_1.zip

The first file is the Sqlite command line program used for modifing the Sqlite database. You may or may not use this.

The second file is the Windows DLL library file and Ruby uses this when Rails makes Sqlite database calls.

When both these ZIP files have been extracted you should have the following files:

• sqlite3.exe
• sqlite3.def
• sqlite3.dll

Copy these file to the bin directory of your Ruby installation

gem install sqlite3-ruby

4. create a project

rails test
cd test
ruby script/server

Now you test page running on http://127.0.0.1:3000

Yeah