当前位置：文江博客话题详情

从 OS X 上的 Ruby 连接到 SQL Server 的最佳方式是什么？

发布于 2024-09-28 10:30:35 字数 2311 浏览 0 评论 0原文

我需要让我的 Rails 应用程序连接到 MS SQL Server 数据库并执行简单的查询来进行查找。该应用程序的主要数据库是 MySQL。它只需要兼职做 SQL Server 的事情。

最好的方法是什么？

我可以用 Java 编写一个完全独立的应用程序，连接到数据库并将一些 XML 数据转储到文件系统以供我的 Ruby 应用程序获取。

或者我可以搞乱 Ruby ODBC 连接。我在 Gemcutter 上进行了搜索，发现了这些...

dbd-odbc (0.2.5) 11141 下载
ruby-odbc (0.99992) 6390 下载
activerecord-odbc-adapter (2.0) 2333 下载
odbc-rails (1.5) 2167

下载不过，这意味着从一个 Rails 应用程序连接到两个不同的数据库。我什至不知道该怎么做。

有人有在 Ruby 中使用 SQL Server 的经验吗？关于哪种方法最实用有什么想法吗？

更新——让它工作

感谢您的帮助。根据答案和一些研究，我解决了这个问题。我将在下面粘贴我的笔记。抱歉用了随意的语气。我只是把它们复制过来。

在 OS X 上使用 Ruby 连接到 SQL Server 所采取的步骤

下载、配置、构建 FreeTDS 库

这些文档大部分都非常详细且写得清晰。关键地方有一些棘手的地方，花了我几个小时进行故障排除。

默认情况下，它安装到 /usr/local/freetds/lib 中。

这为您提供了一堆实用程序和东西。他们可以帮助进行测试和故障排除。

例如，文档中：“tsql 实用程序作为 FreeTDS 的一部分提供，专门用于故障排除。”

将其添加到 /etc/profile：

# 2010-10-19
# To support the FreeTDS library for connecting Ruby to SQL Server.

PATH=$PATH:/usr/local/freetds/bin

# Have FreeTDS to log some output.
#export TDSDUMP=/tmp/freetds.log
#export TDSDUMPCONFIG=/tmp/freetdb_config.log

export PATH

FreeTDS 配置文件

需要为 FreeTDS 创建配置文件。该文档列出了几个可以存储的位置。唯一对我有用的是 ~/.freetds.conf ...

[DATA_SERVER_NAME]
    host = hostname
    port = 1433
    tds version = 8.0

这就是整个配置。对于[DATA_SERVER_NAME]，您不能使用主机名，否则它将不起作用。使用一些直观的逻辑名称。当您在 Ruby 代码中使用 tiny_tds gem 连接到它时，您将使用该名称。

不需要设置 ODBC

无论 FreeTDS 文档中如何规定，都不需要创建 odbc.ini 或 odbcinst.ini 文件。没有它它也能工作。

已安装 tiny_tds gem

tiny_tds gem 将 Ruby 与 FreeTDS 连接到 SQL Server 数据库。在tiny_tds 文档中，它说...'...

请确保编译带有 libiconv 支持的 FreeTDS，以便编码能够发挥最佳性能。在控制台中运行“tsql -C”并检查“iconv 库：是”。

我能够做到这一点并且成功了。

它应该像这样工作：

require 'tiny_tds'
client = TinyTds::Client.new(:username => '...username...', :password => '...password...', :dataserver => 'DATA_SERVER_NAME')
sql = '... whatever ...'
result = client.execute(sql)
client.close

ActiveRecord

假设在通过安装activerecord-sqlserver-adapter gem 完成所有设置后，您可以将 ActiveRecord 与 SQL Server 一起使用。我不需要它，所以我没有安装它。

原文

I need to let my Rails app connect to a MS SQL Server database and do a simple query to do a lookup. The main DB for the app is MySQL. It just needs to do this SQL Server thing on the side.

What's the best way to do that?

I could write an entirely separate app in Java that connects to the DB and dumps some XML data to the filesystem for my Ruby app to pick up.

Or I could mess around with Ruby ODBC connectivity. I did a search on Gemcutter and found these...

dbd-odbc (0.2.5) 11141 downloads
ruby-odbc (0.99992) 6390 downloads
activerecord-odbc-adapter (2.0) 2333 downloads
odbc-rails (1.5) 2167 downloads

It would mean connecting to two different DBs from one Rails app, though. I'm not even sure how to do that.

Does anyone have experience working with SQL Server in Ruby? Any thoughts on which approach is most practical?

UPDATE -- got it working

Thanks for the help. Based on the answers and some research I worked it out. I'm going to paste my notes below. Sorry for the casual tone. I just copied them over.

Steps taken to connect to SQL Server with Ruby on OS X

Download, configure, build FreeTDS library

The docs are pretty detailed and clearly written, mostly. There are a few rough spots in crucial places that cost me a few hours of troubleshooting.

By default it installs into /usr/local/freetds/lib.

That gives you a bunch of utilities and stuff. They can help with testing and troubleshooting.

For example, from the docs: "The tsql utility is provided as part of FreeTDS expressly for troubleshooting."

Added this to /etc/profile:

# 2010-10-19
# To support the FreeTDS library for connecting Ruby to SQL Server.

PATH=$PATH:/usr/local/freetds/bin

# Have FreeTDS to log some output.
#export TDSDUMP=/tmp/freetds.log
#export TDSDUMPCONFIG=/tmp/freetdb_config.log

export PATH

FreeTDS config file

Need to create a config file for FreeTDS. The docs list several places where that can be stored. The only one that worked for me was ~/.freetds.conf...

[DATA_SERVER_NAME]
    host = hostname
    port = 1433
    tds version = 8.0

That's the whole config. For the [DATA_SERVER_NAME] you can't use the hostname or it won't work. Use some intuitive logical name. You'll use that name when you hook up to it inside Ruby code with the tiny_tds gem.

Don't need to set up ODBC

Despite what it says in the FreeTDS docs, there's no need to create a odbc.ini or a odbcinst.ini file. It works without it.

Installed tiny_tds gem

The tiny_tds gem connects Ruby to FreeTDS to an SQL Server database. In the tiny_tds docs it says...

'...please make sure to compile FreeTDS with libiconv support for encodings to work at their best. Run "tsql -C" in your console and check for "iconv library: yes".'

I was able to do that and it worked.

It's supposed to work like this:

require 'tiny_tds'
client = TinyTds::Client.new(:username => '...username...', :password => '...password...', :dataserver => 'DATA_SERVER_NAME')
sql = '... whatever ...'
result = client.execute(sql)
client.close

ActiveRecord

Supposedly you can use ActiveRecord with SQL Server after you get all that set up by installing the activerecord-sqlserver-adapter gem. I didn't need that so I didn't install it.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

故人如初 2024-10-05 10:30:35

目前，连接 MS SQL 所需的 gem 正在进行大量开发。

简短摘要：

您不再需要 dbi 或 dbd。（c0r0ner 发布的指南太旧了）
您需要的只是 freetds、ruby-odbc 和 activerecord-sqlserver-adapter
（遵循 wuputah 发布的 Xavier Shay 的指南）
留意即将推出的 tiny_tds，作为 ruby-odbc 的替代品
它使配置更加简单，访问数据库更加快捷
( http://github.com/rails-sqlserver/tiny_tds )

回复收藏 0 原文

沫尐诺 2024-10-05 10:30:35

您可以使用与我在此答案中解释的策略相同，但使用 ODBC 适配器，如 database.yml 文件中的此处所述。所以基本上你会这样做：

class SQLServerThing < ActiveRecord::Base
  establish_connection :sqlserver
  set_table_name 'things'
end

现在你可以这样做：

SQLServerThing.find(1)

或者在数据库上运行通用查询，执行：

SQLServerThing.connection.select_all("SELECT * FROM table ...")

如果您不想使用常规的 ActiveRecord 方法，则设置表名是可选的。

除非您想从同一模型内连接到多个数据库，否则不需要额外的 gem (magic_multi_connections)。通过使用单独的模型，您可以避免引入单独的库。

You can use the same strategy as I explained in this answer but use the ODBC adapter as explained here in your database.yml file. So basically you would do:

class SQLServerThing < ActiveRecord::Base
  establish_connection :sqlserver
  set_table_name 'things'
end

Now you can do:

SQLServerThing.find(1)

Or run generic queries on the database, do:

SQLServerThing.connection.select_all("SELECT * FROM table ...")

If you have no desire to use regular ActiveRecord methods, setting a table name is optional.

An additional gem (magic_multi_connections) is not needed unless you want to connect to multiple database from within the same model. By using a separate model, you can avoid bringing in a separate library.

回复收藏 0 原文