使用R + DBI库+ ODBC连接到Teradata数据库

问题描述：

我正在尝试使用DBI库连接到R中的Teradata数据库(如果重要的话，我在Windows上).我可以使用RODBC库成功连接，因此我知道我的凭据等是正确的.

I'm trying to connect to a Teradata Database in R using the DBI library (if it matters I'm on Windows). I can successfully connect using the RODBC library so I know my credentials etc. are correct.

我怀疑问题是:

我没有正确指定身份验证机制
错误的驱动程序:也许我应该使用JDBC，而不是使用ODBC?

背景:

首先，如果我看这是ODBC数据库源管理器，这是我的ODBC信息:

First here's my ODBC info if I look it the ODBC Database Source Administrator:

名称= name_name
驱动程序= Teradata
名称或IP地址= address.here.ok
机制= ldap
用户名=我的用户名

使用R 3.5.1(2018-07-02)，RStudio，Windows 10.

Using R 3.5.1 (2018-07-02), RStudio, Windows 10.

有效方法:

library(RODBC) 
con = odbcConnect(dsn = "name_name"
                  ,uid = rstudioapi::askForPassword("Username")
                  ,pwd = rstudioapi::askForPassword("Password")
                  )

我尝试过的DBI失败了:

What I've tried with DBI and fails:

library(DBI)

con <- DBI::dbConnect(odbc::odbc()
                      ,driver = "Teradata"
                      ,DBCName = "name_name"
                      ,host   = "address.here.ok"
                      ,uid    = rstudioapi::askForPassword("Database user")
                      ,pwd    = rstudioapi::askForPassword("Database password"))

错误:Error: nanodbc/nanodbc.cpp:950: 28000: [Teradata][ODBC Teradata Driver][Teradata Database] The UserID, Password or Account is Invalid

con <- DBI::dbConnect(odbc::odbc()
                      ,driver = "Teradata"
                      ,DBCName = "name_name"
                      ,host   = "address.here.ok"
                      ,uid    = rstudioapi::askForPassword("Database user")
                      ,pwd    = rstudioapi::askForPassword("Database password")
                      ,MechanismName = "ldap")

错误:Error: nanodbc/nanodbc.cpp:950: 28000: [Teradata][ODBC Teradata Driver][Teradata Database] The UserID, Password or Account is Invalid

在类似的情况下，如果未正确指定机制(ldap)，我会看到此错误，因此MechanismName可能是错误的吗?

I've seen this error in similar situations when the mechanism is not specified correctly (ldap), so maybe MechanismName is wrong?

我知道我可以在连接字符串中说我喜欢的任何内容，并且不会引发任何错误，因此，如果MechanismName不是指定身份验证机制的正确方法，我将不会返回错误.例如:

I know I can say whatever I like in the connection string and no error is raised so if MechanismName is not the correct way to specify the authentication mechanism I will not have an error returned. For example:

con <- DBI::dbConnect(odbc::odbc()
                      ,driver = "Teradata"
                      ,DBCName = "name_name"
                      ,host   = "address.here.ok"
                      ,uid    = rstudioapi::askForPassword("Database user")
                      ,pwd    = rstudioapi::askForPassword("Database password")
                      ,MechanismName = "ldap")
                      ,made_up_input = "I like cats"

给出错误:Error: nanodbc/nanodbc.cpp:950: 28000: [Teradata][ODBC Teradata Driver][Teradata Database] The UserID, Password or Account is Invalid

谢谢.

从Git中提出的问题中开始想到使用MechanismName 的想法

got idea to use MechanismName from an issue raised in Git

答

像R一样，DBI是基于C的并且讲ODBC.如果它是基于Java的，它将使用JDBC.

Like R, DBI is C-based and speaks ODBC. If it were Java-based, it would speak JDBC.

我建议您在使用askForPassword函数之前，对硬编码的UID和PWD进行测试.

I would suggest you test with hard-coded UID and PWD, before using the askForPassword function.

也就是说，我相信您会成功-

That said, I believe you will succeed with --

DBI::dbConnect(odbc::odbc() 
    ,dsn = "name_name" 
    ,uid = rstudioapi::askForPassword("Username") 
    ,pwd = rstudioapi::askForPassword("Password")
   )

使用R + DBI库+ ODBC连接到Teradata数据库

相关推荐