你有过与ActiveRecord的连接到SQL Server？这可能吗？任何人都可以提供一些出发点？

Have you ever had to connect to SQL Server with ActiveRecord? Is this possible? Can anyone provide some starting points?

推荐答案

这个我用了什么：

从这里开始： http://github.com/rails-sqlserver/2000-2005-adapter/tree/master

安装的

首先，需要Ruby DBI和Ruby ODBC。据我所知，ADO DBD的DBI不再支持。下面的安装是不是COM prehensive走通如何获得所有必需的运动部件像安装和/或配置freetds的。它也将承担两个依赖库和适配器本身的创业板安装。

First, you will need Ruby DBI and Ruby ODBC. To my knowledge the ADO DBD for DBI is no longer supported. The installation below is not a comprehensive walk thru on how to get all the required moving parts like FreeTDS installed and/or configured. It will also assume gem installations of both the dependent libraries and the adapter itself.

应当指出的是，这个版本的适配器被同时使用古0.0.23版DBI可达的0.4.0当前的稳定释放开发的。由于更高版本的DBI会改变很多东西，我们强烈建议您最大的安装到0.4.0版本，下面显示其中的例子。暂时，我们不支持DBI的版本比0.4.0更高。好消息是，如果你使用的是很旧的DBI与ADO，技术上此适配器仍然会为你工作，但要警告你的路径是老了，可能不支持长。

It should be noted that this version of the adapter was developed using both the ancient 0.0.23 version of DBI up to the current stable release of 0.4.0. Because later versions of DBI will be changing many things, IT IS HIGHLY RECOMMENDED that you max your install to version 0.4.0 which the examples below show. For the time being we are not supporting DBI versions higher than 0.4.0. The good news is that if you were using a very old DBI with ADO, technically this adapter will still work for you, but be warned your path is getting old and may not be supported for long.

$ gem install dbi --version 0.4.0
$ gem install dbd-odbc --version 0.2.4
$ gem install rails-sqlserver-2000-2005-adapter -s http://gems.github.com

从这里：http://lambie.org/2008/02/28/connecting-to-an-mssql-database-from-ruby-on-ubuntu/

首先，更新你的〜/ .profile文件，包括以下内容：

Firstly, update your ~/.profile to include the following:

export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc
export FREETDSCONF=/etc/freetds/freetds.conf

然后重新装入你的.profile文件，通过注销和重新连接。

Then reload your .profile, by logging out and in again.

其次，在Ubuntu 7.10服务器，我需要安装一些软件包。

Secondly, on Ubuntu 7.10 Server I needed to install some packages.

mlambie@ubuntu:~$ sudo aptitude install unixodbc unixodbc-dev freetds-dev sqsh tdsodbc

通过安装freetds的，我可以配置它是这样的：

With FreeTDS installed I could configure it like this:

mlambie@ubuntu:/etc/freetds$ cat freetds.conf
[ACUMENSERVER]
  host = 192.168.0.10
  port = 1433
  tds version = 7.0

最重要的事情在这里是ACUMENSERVER，这是连接到数据库时，我将使用DSN。主机和端口是不言自明，这是值得一提的是，我不得不使用7.0专门为TDS版本。

The important thing here is ACUMENSERVER, which is the DSN that I’ll use when connecting to the database. The host, and port are self-explanatory, and it’s worth noting that I had to use 7.0 specifically as the tds version.

测试freetds的是不是太难了：

Testing FreeTDS is not too hard:

mlambie@ubuntu:~$ sqsh -S ACUMENSERVER -U username -P password
sqsh: Symbol `_XmStrings' has different size in shared object, consider re-linking
sqsh-2.1 Copyright (C) 1995-2001 Scott C. Gray
This is free software with ABSOLUTELY NO WARRANTY
For more information type '\warranty'
1> use acumen
2> go
1> select top 1 firstname, lastname from tblClients
2> go

[record returned]

(1 row affected)
1> quit

接下来有必要配置ODBC：

Next up it’s necessary to configure ODBC:

mlambie@ubuntu:/etc$ cat odbcinst.ini
[FreeTDS]
Description     = TDS driver (Sybase/MS SQL)
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so
CPTimeout       =
CPReuse         =
FileUsage       = 1

mlambie@ubuntu:/etc$ cat odbc.ini
[ACUMENSERVER]
Driver          = FreeTDS
Description     = ODBC connection via FreeTDS
Trace           = No
Servername      = ACUMENSERVER
Database        = ACUMEN

然后，我测试了ISQL连接：

I then tested the connection with isql:

mlambie@ubuntu:~$ isql -v ACUMENSERVER username password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> use ACUMEN
[][unixODBC][FreeTDS][SQL Server]Changed database context to 'Acumen'.
[ISQL]INFO: SQLExecute returned SQL_SUCCESS_WITH_INFO
SQLRowCount returns -1
SQL> select top 1 firstname from tblClients;

[record returned]

SQLRowCount returns 1
1 rows fetched
SQL> quit

好了，我们已经使用freetds的连接到远程服务器MSSQL了ODBC。剩下的就是添加红宝石混进去。

OK, so we’ve got ODBC using FreeTDS to connect to a remote MSSQL server. All that’s left is to add Ruby into the mix.

mlambie@ubuntu:~$ sudo aptitude install libdbd-odbc-ruby

最后要测试的是红宝石可以使用DBI和ODBC击中实际的数据库，这很容易测试：

The last thing to test is that Ruby can use DBI and ODBC to hit the actual database, and that’s easy to test:

mlambie@ubuntu:~$ irb
irb(main):001:0> require "dbi" 
=> true
irb(main):002:0> dbh = DBI.connect('dbi:ODBC:ACUMENSERVER', 'username', 'password')
=> #<DBI::DatabaseHandle:0xb7ac57f8 @handle=#<DBI::DBD::ODBC::Database:0xb7ac5744
@handle=#<odbc::database:0xb7ac576c>, @attr={}>, @trace_output=#</odbc::database:0xb7ac576c><io:0xb7cbff54>,
@trace_mode=2>
irb(main):003:0> quit

和一个更完整的测试（仅适用于SQL SELECT，请注意）：

And a more complete test (only with SQL SELECT, mind you):

#!/usr/bin/env ruby

require 'dbi'
db = DBI.connect('dbi:ODBC:ACUMENSERVER', 'username', 'password')
select = db.prepare('SELECT TOP 10 firstname FROM tblClients')
select.execute
while rec = select.fetch do
  puts rec.to_s
end
db.disconnect
</io:0xb7cbff54>

从这里（固定ODBC的lib在错误的地点）： http://ubuntuforums.org/showthread.php?t=433435&page=2

From here (to fix the odbc lib being in the wrong place): http://ubuntuforums.org/showthread.php?t=433435&page=2

libtdsodbc.so
with freeTDS (freetds-dev, tdsodbc), you can either edit the path in the odbcinst.ini file for the [FreeTDS] driver section OR cp the /usr/lib/odbc/libtdsodbc.so into /usr/lib/libtdsodbc.so.

从提示访问MSSQL时两种方法都可行。

either way works when accessing mssql from the prompt

isql -v $dsn $user $passwd

我发现这是非常有用

i found this to be useful

http://www.unixodbc.org/doc/FreeTDS.html#Configuration

然后在database.yml文件：

And then in the database.yml file:

development:
  adapter: sqlserver
  mode: odbc
  dsn: dsn_name
  username: my_username
  password: my_password