HowtoconnecttoMicrosoftsqlserver2005fromrailsonlinux（centos5)

为什么80%的码农都做不了架构师&＃xff1f;>>>

Assumed:

ruby 1.8.6

rails : 2.2.2

centos5 64bit

sqlserver2005

Required:

unixODBC >&＃61; 2.2.11 (http://www.unixodbc.org/)

FreeTDS >&＃61; 0.63 (http://www.freetds.org/)

Ruby ODBC >&＃61; 0.996 (http://www.ch-werner.de/rubyodbc/)

Ruby DBI >&＃61; 0.0.23 (http://ruby-dbi.sourceforge.net/)

First:

Use ~/.bashrc for your local profile or /etc/profile to set this for all users.

export ODBCINI&＃61;/etc/odbc.ini

export ODBCSYSINI&＃61;/etc

export FREETDSCONF&＃61;/etc/freetds/freetds.conf

install unixODBC:

apt-get install unixODBC or yum install unixODBC

but I am use rpm,like this: rpm -ivh unixODBC-2.2.11-7.1.x86_64.rpm

you can install unixODBC manually,like this:

# cd /usr/local/src

# wget http://www.unixodbc.org/unixODBC-2.2.11.tar.gz

# tar -xzvf unixODBC*.tar.gz

#cd unixODBC*

# ./config --prefix&＃61;/usr/local -sysconfdir&＃61;/etc --disable-gui

#make

#make install

install FreeTDS:

you can install freetds by apt-get or yum.

rpm -ivh freetds-0.64-11.el5.centos.x86_64.rpm

or install freetds manually,like this:

wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz

tar -xzvf freetds-stable*.tgz

cd freetds-stable*

./configure —with-unixodbc&＃61;/usr/local

make

make install

configure FreeTDS:

add a section to /etc/freetds/freetds.conf or /etc/freetds.conf:

[your_db_name]

host &＃61; 192.168.1.101 (change this to the right one for you)

port &＃61; 1433

tds version &＃61; 8.0 #(might be different, check "here":http://www.freetds.org/userguide/choosingtdsprotocol.htm.) #8.0 is the minimum for some important features of SQL server

Test FreeTDS:

tsql -S your_db_name -U name -P pw

you should see and do the following to make sure all went well:

locale is "zh_CN"
locale charset is "GB2312"
1>use your_actual_db

2> go

1> select * from user (do this for a table that exists in your db)

2> go

id login

1 user1

2 user2

(make sure you get correct output)

1>quit

create db definition

edit: /etc/odbc.ini:

[your_db_name]

Driver &＃61; FreeTDS

Description &＃61; ODBC connection via FreeTDS

Trace &＃61; No

Servername &＃61; your_db_name

Database &＃61; your_actual_db

edit: /etc/odbcinst.ini:

[FreeTDS]
Description     &＃61; TDS driver (Sybase/MS SQL)
Driver              &＃61; /usr/lib64/libtdsodbc.so
Setup               &＃61; /usr/lib64/libtdsS.so
CPTimeout       &＃61;
CPReuse           &＃61;
FileUsage         &＃61; 1
Socket              &＃61; 4096

Test unixODBC:

isql -v your_db_name username pw

You should see:

&＃43;---------------------------------------&＃43;
| id | login |
&＃43;---------------------------------------&＃43;
| 1 | user1 |
| 3 | user2 |
&＃43;---------------------------------------&＃43;
2 rows affected
2 rows returned

SQL> quit

ruby odbc:

gem install dbi

gem install dbd-odbc

Test everything:

# irb1.8 irb(main):001:0> require "dbi"

&＃61;> true

irb(main):004:0> dbh &＃61; DBI.connect(&＃39;dbi:ODBC:your_db_name&＃39;, &＃39;username&＃39;, &＃39;pw&＃39;)

&＃61;> #, &＃64;trace_mode&＃61;2, &＃64;handle&＃61;#>> irb(main):005:0> quit

Setup rails:

database.yml

development:

adapter: sqlserver

mode: odbc

dsn: your_db_name

username: USERNAME

password: PASSWORD

Last:

gem install activerecord-odbc-adapter

Everything is ok,now test the rails:

[root&＃64;b03 ruby]# ruby script/console
Loading development environment (Rails 2.2.2)
Tue Feb 23 14:59:18 &＃43;0800 2010 [Info] [Module] Copy plugin app_frame resources to Rails.public
Tue Feb 23 14:59:18 &＃43;0800 2010 [Info] [Module] Copy plugin endless_page resources to Rails.public
Tue Feb 23 14:59:18 &＃43;0800 2010 [Info] [Module] Copy plugin sfp_skin resources to Rails.public
>> ActiveRecord::Base.connection.execute(&＃39;select * from message&＃39;)
&＃61;> #
>>

参考&＃xff1a;http://oldwiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQLServerFromRailsOnLinux/versions/140