Have you ever had to connect to SQL Server with ActiveRecord? Is this possible? Can anyone provide some starting points?
您是否曾经使用ActiveRecord连接到SQL服务器?这是可能的吗?有人能提供一些出发点吗?
3 个解决方案
#1
11
This what I used:
这是我使用:
From here: http://github.com/rails-sqlserver/2000-2005-adapter/tree/master
从这里:http://github.com/rails sqlserver/2000 - 2005 adapter/tree/master
Installation
安装
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.
首先，您需要Ruby DBI和Ruby ODBC。据我所知，DBI的ADO DBD不再被支持。下面的安装并不是关于如何安装和/或配置FreeTDS等所有必需的移动部件的全面介绍。它还将假设相关库和适配器本身都安装了gem。
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.
应该注意的是，这个版本的适配器是使用了传统的0.0.23版本的DBI，直到当前稳定的0.4.0版本。因为DBI的后续版本将会改变很多东西，所以强烈建议您将安装的最大版本设置为0.4.0，如下面的示例所示。目前我们不支持高于0.4.0的DBI版本。好消息是，如果您使用的是一个非常旧的DBI与ADO，技术上来说，这个适配器仍然适用于您，但是要注意，您的路径正在变老，并且可能不会支持很长时间。
$ 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
From here: http://lambie.org/2008/02/28/connecting-to-an-mssql-database-from-ruby-on-ubuntu/
在这里:http://lambie.org/2008/02/28/connecting-to-an-mssql-database-from-ruby-on-ubuntu/
Firstly, update your ~/.profile to include the following:
首先,更新你的~ /。资料包括以下内容:
export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc
export FREETDSCONF=/etc/freetds/freetds.conf
Then reload your .profile, by logging out and in again.
然后重新加载.profile，退出和再次登录。
Secondly, on Ubuntu 7.10 Server I needed to install some packages.
其次，在Ubuntu 7.10服务器上，我需要安装一些包。
mlambie@ubuntu:~$ sudo aptitude install unixodbc unixodbc-dev freetds-dev sqsh tdsodbc
With FreeTDS installed I could configure it like this:
安装了FreeTDS后，我可以这样配置它:
mlambie@ubuntu:/etc/freetds$ cat freetds.conf
[ACUMENSERVER]
host = 192.168.0.10
port = 1433
tds version = 7.0
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.
这里重要的是ACUMENSERVER，它是在连接数据库时使用的DSN。主机和端口是不言自明的，值得注意的是我必须使用7.0作为tds版本。
Testing FreeTDS is not too hard:
测试FreeTDS并不难:
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
Next up it’s necessary to configure ODBC:
接下来需要配置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
I then tested the connection with isql:
然后，我测试了与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
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.
好的，我们已经有了使用FreeTDS连接到远程MSSQL服务器的ODBC。剩下的就是将Ruby添加到混合中。
mlambie@ubuntu:~$ sudo aptitude install libdbd-odbc-ruby
The last thing to test is that Ruby can use DBI and ODBC to hit the actual database, and that’s easy to test:
最后要测试的是Ruby可以使用DBI和ODBC访问实际的数据库，这很容易测试:
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
And a more complete test (only with SQL SELECT, mind you):
还有一个更完整的测试(只有SQL SELECT，请注意):
#!/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>
From here (to fix the odbc lib being in the wrong place): http://ubuntuforums.org/showthread.php?t=433435&page=2
从这里(修复odbc lib位于错误的位置):http://ubuntuforums.org/showthread.php?
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.
either way works when accessing mssql from the prompt
在从提示符访问mssql时，两种方法都可以工作
isql -v $dsn $user $passwd
i found this to be useful
我发现这很有用
http://www.unixodbc.org/doc/FreeTDS.html#Configuration
http://www.unixodbc.org/doc/FreeTDS.html配置
And then in the database.yml file:
然后在数据库中。yml文件:
development:
adapter: sqlserver
mode: odbc
dsn: dsn_name
username: my_username
password: my_password
#2
2
These are the steps i've compiled for Centos 5.3. It took me a lot of trial and error to get this working. It's from a completely clean Centos installation.
这些是我为Centos 5.3编写的步骤。我费了很大的周折才使这个工作成功。它来自一个完全干净的Centos装置。
Install EPEL:
安装EPEL:
rpm -Uvh http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-3.noarch.rpm
Install ruby, rubygems, freetds, unixODBC, development tools:
安装ruby、rubygems、freetds、unixODBC、开发工具:
yum install gcc
yum install freetds
yum install ruby-devel
yum install unixODBC-devel
yum install ruby rubygems
Install rails:
安装rails:
gem install rails
Install DB related gems:
安装数据库相关的宝石:
gem install dbd-odbc
gem install rails-sqlserver-2000-2005-adapter -s http://gems.github.com
Download, build and install ruby-odbc:
下载、构建和安装ruby-odbc:
wget http://www.ch-werner.de/rubyodbc/ruby-odbc-0.9997.tar.gz
tar zxvf ruby-odbc-0.9997.tar.gz
cd ruby-odbc-0.9997
ruby extconf.rb
make
make install
Final gem list:
最后的宝石列表:
# gem list
*** LOCAL GEMS ***
actionmailer (2.3.2)
actionpack (2.3.2)
activerecord (2.3.2)
activeresource (2.3.2)
activesupport (2.3.2)
dbd-odbc (0.2.4)
dbi (0.4.1)
deprecated (2.0.1)
rails (2.3.2)
rails-sqlserver-2000-2005-adapter (2.2.17)
rake (0.8.7)
You can use various tools like isql to test your ODBC connection. Brian's post covers this in nice detail so I won't repeat. In rails you need a database.yml that looks something like this:
可以使用isql之类的各种工具来测试ODBC连接。布莱恩的文章详细地介绍了这一点，所以我就不重复了。在rails中，您需要一个数据库。yml看起来是这样的:
development:
adapter: sqlserver
mode: odbc
dsn: dsnName
username: username
password: password
#3
1
On Ubuntu, I use FreeTDS and the activerecord-sqlserver-adapter gem.
在Ubuntu上，我使用FreeTDS和activerecord-sqlserver-adapter gem。
You can install FreeTDS through apt:
您可以通过apt安装FreeTDS:
sudo apt-get install freetds
And add this to your Gemfile:
把它添加到你的Gemfile:
gem 'activerecord-sqlserver-adapter'
I had to change the configured FreeTDS version number in /etc/freetds/freetds.conf to 8.0 in order to get things working correctly.
我必须更改/etc/freetds/freetds中配置的FreeTDS版本号。从conf到8.0，以使事情正常运行。
[global]
tds version = 8.0
activerecord-sqlserver-adapter on GitHub
activerecord-sqlserver-adapter GitHub上
FreeTDS项目
#1
11
This what I used:
这是我使用:
From here: http://github.com/rails-sqlserver/2000-2005-adapter/tree/master
从这里:http://github.com/rails sqlserver/2000 - 2005 adapter/tree/master
Installation
安装
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.
首先，您需要Ruby DBI和Ruby ODBC。据我所知，DBI的ADO DBD不再被支持。下面的安装并不是关于如何安装和/或配置FreeTDS等所有必需的移动部件的全面介绍。它还将假设相关库和适配器本身都安装了gem。
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.
应该注意的是，这个版本的适配器是使用了传统的0.0.23版本的DBI，直到当前稳定的0.4.0版本。因为DBI的后续版本将会改变很多东西，所以强烈建议您将安装的最大版本设置为0.4.0，如下面的示例所示。目前我们不支持高于0.4.0的DBI版本。好消息是，如果您使用的是一个非常旧的DBI与ADO，技术上来说，这个适配器仍然适用于您，但是要注意，您的路径正在变老，并且可能不会支持很长时间。
$ 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
From here: http://lambie.org/2008/02/28/connecting-to-an-mssql-database-from-ruby-on-ubuntu/
在这里:http://lambie.org/2008/02/28/connecting-to-an-mssql-database-from-ruby-on-ubuntu/
Firstly, update your ~/.profile to include the following:
首先,更新你的~ /。资料包括以下内容:
export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc
export FREETDSCONF=/etc/freetds/freetds.conf
Then reload your .profile, by logging out and in again.
然后重新加载.profile，退出和再次登录。
Secondly, on Ubuntu 7.10 Server I needed to install some packages.
其次，在Ubuntu 7.10服务器上，我需要安装一些包。
mlambie@ubuntu:~$ sudo aptitude install unixodbc unixodbc-dev freetds-dev sqsh tdsodbc
With FreeTDS installed I could configure it like this:
安装了FreeTDS后，我可以这样配置它:
mlambie@ubuntu:/etc/freetds$ cat freetds.conf
[ACUMENSERVER]
host = 192.168.0.10
port = 1433
tds version = 7.0
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.
这里重要的是ACUMENSERVER，它是在连接数据库时使用的DSN。主机和端口是不言自明的，值得注意的是我必须使用7.0作为tds版本。
Testing FreeTDS is not too hard:
测试FreeTDS并不难:
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
Next up it’s necessary to configure ODBC:
接下来需要配置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
I then tested the connection with isql:
然后，我测试了与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
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.
好的，我们已经有了使用FreeTDS连接到远程MSSQL服务器的ODBC。剩下的就是将Ruby添加到混合中。
mlambie@ubuntu:~$ sudo aptitude install libdbd-odbc-ruby
The last thing to test is that Ruby can use DBI and ODBC to hit the actual database, and that’s easy to test:
最后要测试的是Ruby可以使用DBI和ODBC访问实际的数据库，这很容易测试:
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
And a more complete test (only with SQL SELECT, mind you):
还有一个更完整的测试(只有SQL SELECT，请注意):
#!/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>
From here (to fix the odbc lib being in the wrong place): http://ubuntuforums.org/showthread.php?t=433435&page=2
从这里(修复odbc lib位于错误的位置):http://ubuntuforums.org/showthread.php?
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.
either way works when accessing mssql from the prompt
在从提示符访问mssql时，两种方法都可以工作
isql -v $dsn $user $passwd
i found this to be useful
我发现这很有用
http://www.unixodbc.org/doc/FreeTDS.html#Configuration
http://www.unixodbc.org/doc/FreeTDS.html配置
And then in the database.yml file:
然后在数据库中。yml文件:
development:
adapter: sqlserver
mode: odbc
dsn: dsn_name
username: my_username
password: my_password
#2
2
These are the steps i've compiled for Centos 5.3. It took me a lot of trial and error to get this working. It's from a completely clean Centos installation.
这些是我为Centos 5.3编写的步骤。我费了很大的周折才使这个工作成功。它来自一个完全干净的Centos装置。
Install EPEL:
安装EPEL:
rpm -Uvh http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-3.noarch.rpm
Install ruby, rubygems, freetds, unixODBC, development tools:
安装ruby、rubygems、freetds、unixODBC、开发工具:
yum install gcc
yum install freetds
yum install ruby-devel
yum install unixODBC-devel
yum install ruby rubygems
Install rails:
安装rails:
gem install rails
Install DB related gems:
安装数据库相关的宝石:
gem install dbd-odbc
gem install rails-sqlserver-2000-2005-adapter -s http://gems.github.com
Download, build and install ruby-odbc:
下载、构建和安装ruby-odbc:
wget http://www.ch-werner.de/rubyodbc/ruby-odbc-0.9997.tar.gz
tar zxvf ruby-odbc-0.9997.tar.gz
cd ruby-odbc-0.9997
ruby extconf.rb
make
make install
Final gem list:
最后的宝石列表:
# gem list
*** LOCAL GEMS ***
actionmailer (2.3.2)
actionpack (2.3.2)
activerecord (2.3.2)
activeresource (2.3.2)
activesupport (2.3.2)
dbd-odbc (0.2.4)
dbi (0.4.1)
deprecated (2.0.1)
rails (2.3.2)
rails-sqlserver-2000-2005-adapter (2.2.17)
rake (0.8.7)
You can use various tools like isql to test your ODBC connection. Brian's post covers this in nice detail so I won't repeat. In rails you need a database.yml that looks something like this:
可以使用isql之类的各种工具来测试ODBC连接。布莱恩的文章详细地介绍了这一点，所以我就不重复了。在rails中，您需要一个数据库。yml看起来是这样的:
development:
adapter: sqlserver
mode: odbc
dsn: dsnName
username: username
password: password
#3
1
On Ubuntu, I use FreeTDS and the activerecord-sqlserver-adapter gem.
在Ubuntu上，我使用FreeTDS和activerecord-sqlserver-adapter gem。
You can install FreeTDS through apt:
您可以通过apt安装FreeTDS:
sudo apt-get install freetds
And add this to your Gemfile:
把它添加到你的Gemfile:
gem 'activerecord-sqlserver-adapter'
I had to change the configured FreeTDS version number in /etc/freetds/freetds.conf to 8.0 in order to get things working correctly.
我必须更改/etc/freetds/freetds中配置的FreeTDS版本号。从conf到8.0，以使事情正常运行。
[global]
tds version = 8.0
activerecord-sqlserver-adapter on GitHub
activerecord-sqlserver-adapter GitHub上
FreeTDS项目