Doug L. wrote:
Cheers, Craig – what I was needing there was a little hope, and you
just provided it! I’ll see what crops up here, and get searching.
Thanks again,
Doug.
I connect to SQL Server, from both a Mac, and a redhat box.
Been awhile since I wrote this, but it worked for the last person who
tried it.
How to get Ruby on Rails (unix / mac) to work with SQL Server
*
{*} The bulk of this page is a variation of this Wiki
o
http://wiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQLServerFromRailsOnLinux
This page is getting a bit out of date.
*
sudo gem install activerecord-sqlserver-adapter
–source=http://gems.rubyonrails.org Currently server27 is setup with
SQL Server and Ruby on Rails
Four pieces of software need to be installed (aside from Ruby and Rails)
- Unix ODBC
- FreeTDS
- Ruby ODBC Driver
- Ruby DBI
Download Software
* Download software from above link.
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
[assurantivr]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = assurantivr
Database = AssurantIVR
[aqm]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = aqm
Database = AQM
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
*
#Assurant IVR
[assurantivr]
host = orcxsdb001
port = 1433
tds version = 8.0
#AQM (Aspect Managment)
[aqm]
host = orcxpdb0aa
port = 1433
tds version = 8.0
#Aspect (Aspect Reporting Database)
[aspect]
host = orcxpdb1c2
instance = SQL
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 aspect -U 'workgroups\dsmith' -P fakepassword -I
/etc/freetds.conf
Test 2
* isql aspect 'workgroups/dsmith' '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:aspect’,‘workgroups\dsmith’,‘fakepassword’)
=> #<DBI::DatabaseHandle:0xb7c3fb60
@trace_output=#IO:0xb7f5b024, @trace_mode=2,
@handle=#<DBI::DBD::ODBC::Database:0xb7c3fac0 @attr={},
@handle=#ODBC::Database:0xb7c3fae8>>
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 < ActiveRecord::Base
set_table_name "cnxrpt_CallCenterCallDetail"
self.primary_key="CallCenterCallDetail_id"
self.establish_connection(:adapter => "sqlserver",
:mode => "odbc",
:dsn => "aspect",
:username => 'workgroups\dsmith',
: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
*
<h1>First row </h1>
<table bgcolor="#000000" cellspacing=1>
<% Aspect.names.each do |name| -%>
<tr>
<th bgcolor="#DDDDDD"><%=name -%>
<td bgcolor="#FFFFCC"><%[email protected](name) -%>
<% end -%>
</table>
ruby script/server -p 5312 -d
Vist http://server27:5312/aspect