RODBC无法识别我的odbc设置 [英] RODBC not recognizing my odbc settings

问题描述

我在Red Hat Linux 6服务器上运行R 2.15.2.我的目标是通过RODBC连接到另一台计算机上的MS SQL Server数据库.我进行了研究，并从 Microsoft支持网站.我已经从源代码构建了unixODBC版本2.3.0，因为Windows驱动程序需要它，并且它不在RHL仓库中(仓库版本是2.2.14).

I'm running R 2.15.2 on a Red Hat Linux 6 server. My goal is to connect to a MS SQL Server Database on another machine via RODBC. I did my research and downloaded and installed the linux version of the MS SQL ODBC driver from the microsoft support website. I to had build unixODBC version 2.3.0 from source, because it is required by the windows driver and is not in the RHL repos yet (the repo version is 2.2.14).

无论如何，经过一些工作，我终于正确安装并配置了驱动程序，并且可以通过 isql 命令成功连接到SQL Server数据库:

Anyhow, after a bit of work, I finally got the driver installed and configured properly, and I can connect successfully to the SQL Server database via an isql command:

$ isql -v test testuser testpass
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

所以我知道我已经正确设置了 odbc.ini 和 odbcinst.ini 文件.

so I know I have my odbc.ini and odbcinst.ini files properly set up.

但是，当我尝试从R中访问ODBC连接时，会发生以下情况:

However, when I try to access the ODBC connection from within R, the following happens:

> test <- odbcDriverConnect('DSN=test;Database=RSQLTest01;Uid=testuser;Pwd=testpass')
Warning messages:
1: In odbcDriverConnect("DSN=test;Database=RSQLTest01;Uid=testuser;Pwd=testpass") :
   [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data 
   source name not found, and no default driver specified
2: In odbcDriverConnect("DSN=test;Database=RSQLTest01;Uid=testuser;Pwd=testpass") :
   ODBC connection failed

这是R中 odbcDataSources 命令的结果:

> odbcDataSources()
named character(0)

我一直在做一些研究，我认为解决方案(尽管我可能错了)可能与正确配置ODBC环境变量有关，以便RODBC知道在哪里可以找到 odbc.ini .根据我的研究，我发现以下可能相关的环境变量: $ ODBCINI，$ ODBCSYSINI，$ ODBC_ROOT $ ODBC_INCLUDE和$ ODBC_LIBS.我对应该将这些变量设置为什么有一个合理的认识.，但我不确定如何永久设置它们，以便RODBC可以识别在哪里可以找到合适的文件.

I have been doing some research and I think the solution (though I could be wrong) may have something to do with properly configuring the ODBC environment variables so that RODBC knows where to go to find odbc.ini. Based on my research, I found the following environment variables that may be relevant: $ODBCINI, $ODBCSYSINI, $ODBC_ROOT $ODBC_INCLUDE, and $ODBC_LIBS. I have a reasonable idea of what these should be set to, but I'm not sure how to set them permanently, and so that RODBC can recognize where to find the appropriate files.

有人可以帮我阐明一下吗?我不确定我是否正确理解了环境变量在Linux中的工作方式，尤其是为什么 isql 可以毫无问题地连接，但是 RODBC 甚至找不到驱动程序/数据源.

Can anyone shed some light on this for me? I'm not sure I properly understand how environment variables work in linux, and particularly why isql has no trouble connecting, but RODBC can't even find the driver/data source.

注意:当我安装了unixODBC的回购版本时，使用R中的 install.packages('RODBC')命令可以很好地安装RODBC.但是，在我安装unixODBC 2.3.0之后从源安装，由于依赖关系问题，RODBC安装将失败，因此我不得不使用

NOTE: when I had the repo version of unixODBC installed, RODBC would install just fine using the install.packages('RODBC') command in R. However, after I installed unixODBC 2.3.0 from source, the RODBC install would fail because of a dependency issue, and I had to install RODBC from source using the guide posted here. Is it possible I failed to configure ODBC properly to begin with and that's why I'm having troubles now?

推荐答案

哇，我以为我是唯一从事此类工作的人.我不得不解决相同的问题，发现最好的解决方案是使用rjdbc.由于在Linux或osx上的ODBC参差不齐，因此配置起来要容易得多.RJDBC性能非常好，因为它使用本机ms sql服务器jar执行查询.这是我们其中一个脚本的示例.您只需要从microsoft下载sqljdbc4.jar，然后将.package("RJDBC")安装到您的环境中即可.我意识到您正在尝试使ODBC工作，并且确实在osx上使它工作，但由于时间有限，我放弃了linux.

Wow, I thought I was the only person working on this kind of stuff. I had to solve the same problem and found the best solution was to use rjdbc. This is much easier to configure as ODBC on linux or osx was very spotty. RJDBC performance is great as it uses the native ms sql server jar to execute the query. Here is an example right out of one of our scripts. You just need to download the sqljdbc4.jar from microsoft and then install.package("RJDBC") into your environment. I realized you are trying to get ODBC to work and I did get it to work on osx but I gave up due to time for linux.

library(RJDBC);  

drv <- JDBC('com.microsoft.sqlserver.jdbc.SQLServerDriver', '/usr/local/project/dataproviders/jdbc/sqljdbc4.jar', identifier.quote="'"); 

ch <- dbConnect(drv, 'jdbc:sqlserver://the.server.address.net;databaseName=DataWarehouse', 'USERNAME', 'PASSWORD'); 

allsupp_allprod_allproc <- dbGetQuery(ch, paste("SELECT  [Date], Sum([Fail]) as Numerator, Sum([Pass] + [Fail]) as Denominator,'' as Annotation,'True'  as 'Use for CL' FROM [PSU_YieldData] Group by [Date] Order by [Date]")); 

这篇关于RODBC无法识别我的odbc设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！