odbc
odbc copied to clipboard
r-dbi
Can't connect to Teradata using DBI + odbc unless I establish an RODBC connection first
Issue Description and Expected Result
Occasionally connecting to my Teradata database fails:
# Fails
ch <- DBI::dbConnect(
odbc::odbc(),
Driver = "Teradata",
DBCName = "my_database",
AUTHENTICATION = "ldap",
UID = "johndoe",
PWD = "1234"
)
# Error: nanodbc/nanodbc.cpp:950: HYT00: [WSock32 DLL] 10060 WSA E TimedOut: No response received when attempting to connect to the Teradata server
However, if I first establish a connection using RODBC, which I know is an entirely different package, and then retry the connection, it works! It is so weird, but it's the only way I've found to make DBI + odbc connect again (except trying later):
# Connect using RODBC
ch <- RODBC::odbcDriverConnect(sprintf("Driver={Teradata};DBCName=WMG;AUTHENTICATION=ldap;AUTHENTICATIONPARAMETER=%s@@%s", "johndoe", "1234"))
# Now it works
ch <- DBI::dbConnect(
odbc::odbc(),
Driver = "Teradata",
DBCName = "my_database",
AUTHENTICATION = "ldap",
UID = "johndoe",
PWD = "1234"
)
Database
Teradata 16.10.04.04
Reproducible Example
In this case it made more sense to include the example in the issue description.
Session Info
- Session info ------------------------------------------------------
setting value
version R version 3.6.0 (2019-04-26)
os Windows 10 x64
system x86_64, mingw32
ui RStudio
language (EN)
collate English_United States.1252
ctype English_United States.1252
tz America/Mexico_City
date 2019-10-23
- Packages ----------------------------------------------------------
package * version date lib source
aiff * 1.0.11 2019-10-23 [1] local
assertthat 0.2.1 2019-03-21 [1] CRAN (R 3.6.0)
backports 1.1.4 2019-04-10 [1] CRAN (R 3.6.0)
BBmisc 1.11 2017-03-10 [1] CRAN (R 3.6.0)
bit 1.1-14 2018-05-29 [1] CRAN (R 3.6.0)
bit64 0.9-7 2017-05-08 [1] CRAN (R 3.6.0)
blob 1.1.1 2018-03-25 [1] CRAN (R 3.6.0)
broom 0.5.2 2019-04-07 [1] CRAN (R 3.6.0)
callr 3.2.0 2019-03-15 [1] CRAN (R 3.6.0)
cellranger 1.1.0 2016-07-27 [1] CRAN (R 3.6.0)
checkmate 1.9.3 2019-05-03 [1] CRAN (R 3.6.0)
cli 1.1.0 2019-03-19 [1] CRAN (R 3.6.0)
colorspace 1.4-1 2019-03-18 [1] CRAN (R 3.6.0)
crayon 1.3.4 2017-09-16 [1] CRAN (R 3.6.0)
data.table 1.12.2 2019-04-07 [1] CRAN (R 3.6.0)
DBI 1.0.0 2018-05-02 [1] CRAN (R 3.6.0)
desc 1.2.0 2018-05-01 [1] CRAN (R 3.6.0)
devtools 2.0.2 2019-04-08 [1] CRAN (R 3.6.0)
digest 0.6.19 2019-05-20 [1] CRAN (R 3.6.0)
dplyr * 0.8.1 2019-05-14 [1] CRAN (R 3.6.0)
fansi 0.4.0 2018-10-05 [1] CRAN (R 3.6.0)
fastmatch 1.1-0 2017-01-28 [1] CRAN (R 3.6.0)
fg * 0.2.6 2019-06-24 [1] local
forcats * 0.4.0 2019-02-17 [1] CRAN (R 3.6.0)
formatR 1.7 2019-06-11 [1] CRAN (R 3.6.0)
fs 1.3.1 2019-05-06 [1] CRAN (R 3.6.0)
futile.logger 1.4.3 2016-07-10 [1] CRAN (R 3.6.0)
futile.options 1.0.1 2018-04-20 [1] CRAN (R 3.6.0)
generics 0.0.2 2018-11-29 [1] CRAN (R 3.6.0)
ggplot2 * 3.2.0 2019-06-16 [1] CRAN (R 3.6.0)
ggthemes * 4.2.0 2019-05-13 [1] CRAN (R 3.6.0)
glue 1.3.1 2019-03-12 [1] CRAN (R 3.6.0)
gtable 0.3.0 2019-03-25 [1] CRAN (R 3.6.0)
haven 2.1.0 2019-02-19 [1] CRAN (R 3.6.0)
hms 0.4.2 2018-03-10 [1] CRAN (R 3.6.0)
httr 1.4.0 2018-12-11 [1] CRAN (R 3.6.0)
jsonlite 1.6 2018-12-07 [1] CRAN (R 3.6.0)
labeling 0.3 2014-08-23 [1] CRAN (R 3.6.0)
lambda.r 1.2.3 2018-05-17 [1] CRAN (R 3.6.0)
lattice 0.20-38 2018-11-04 [1] CRAN (R 3.6.0)
lazyeval 0.2.2 2019-03-15 [1] CRAN (R 3.6.0)
lubridate 1.7.4 2018-04-11 [1] CRAN (R 3.6.0)
magrittr 1.5 2014-11-22 [1] CRAN (R 3.6.0)
Matrix 1.2-17 2019-03-22 [1] CRAN (R 3.6.0)
memoise 1.1.0 2017-04-21 [1] CRAN (R 3.6.0)
mlr 2.14.0 2019-04-25 [1] CRAN (R 3.6.0)
modelr 0.1.4 2019-02-18 [1] CRAN (R 3.6.0)
munsell 0.5.0 2018-06-12 [1] CRAN (R 3.6.0)
nlme 3.1-139 2019-04-09 [1] CRAN (R 3.6.0)
odbc 1.1.6 2018-06-09 [1] CRAN (R 3.6.0)
parallelMap 1.4 2019-05-17 [1] CRAN (R 3.6.0)
ParamHelpers 1.12 2019-01-18 [1] CRAN (R 3.6.0)
pillar 1.4.1 2019-05-28 [1] CRAN (R 3.6.0)
pkgbuild 1.0.3 2019-03-20 [1] CRAN (R 3.6.0)
pkgconfig 2.0.2 2018-08-16 [1] CRAN (R 3.6.0)
pkgload 1.0.2 2018-10-29 [1] CRAN (R 3.6.0)
prettyunits 1.0.2 2015-07-13 [1] CRAN (R 3.6.0)
processx 3.3.1 2019-05-08 [1] CRAN (R 3.6.0)
ps 1.3.0 2018-12-21 [1] CRAN (R 3.6.0)
purrr * 0.3.2 2019-03-15 [1] CRAN (R 3.6.0)
R6 2.4.0 2019-02-14 [1] CRAN (R 3.6.0)
Rcpp 1.0.1 2019-03-17 [1] CRAN (R 3.6.0)
readr * 1.3.1 2018-12-21 [1] CRAN (R 3.6.0)
readxl 1.3.1 2019-03-13 [1] CRAN (R 3.6.0)
remotes 2.0.4 2019-04-10 [1] CRAN (R 3.6.0)
rlang 0.3.4 2019-04-07 [1] CRAN (R 3.6.0)
RODBC 1.3-15 2017-04-13 [1] CRAN (R 3.6.0)
rprojroot 1.3-2 2018-01-03 [1] CRAN (R 3.6.0)
rstudioapi 0.10 2019-03-19 [1] CRAN (R 3.6.0)
rvest 0.3.4 2019-05-15 [1] CRAN (R 3.6.0)
scales 1.0.0 2018-08-09 [1] CRAN (R 3.6.0)
sessioninfo 1.1.1 2018-11-05 [1] CRAN (R 3.6.0)
stringi 1.4.3 2019-03-12 [1] CRAN (R 3.6.0)
stringr * 1.4.0 2019-02-10 [1] CRAN (R 3.6.0)
survival 2.44-1.1 2019-04-01 [1] CRAN (R 3.6.0)
testthat 2.1.1 2019-04-23 [1] CRAN (R 3.6.0)
tibble * 2.1.3 2019-06-06 [1] CRAN (R 3.6.0)
tidyr * 0.8.3 2019-03-01 [1] CRAN (R 3.6.1)
tidyselect 0.2.5 2018-10-11 [1] CRAN (R 3.6.0)
tidyverse * 1.2.1 2017-11-14 [1] CRAN (R 3.6.0)
usethis 1.5.0 2019-04-07 [1] CRAN (R 3.6.0)
utf8 1.1.4 2018-05-24 [1] CRAN (R 3.6.0)
vctrs 0.1.0 2018-11-29 [1] CRAN (R 3.6.0)
withr 2.1.2 2018-03-15 [1] CRAN (R 3.6.0)
xml2 1.2.0 2018-01-24 [1] CRAN (R 3.6.0)
zeallot 0.1.0 2018-01-28 [1] CRAN (R 3.6.0)
[1] C:/Users/f0g00bq/Documents/R/R-3.6.0/library
I don't know, but I notice you are using AUTHENTICATIONPARAMETER=%s@@%s" in your call to RODBC but not using it in your call to odbc perhaps that is causing the difference?
Maybe, I'll try it when the error happens again... I wonder, though, how is it possible that RODBC's establishing a successful connection fixes DBI + odbc's issue?
I have this same issue, haven't resolved it without using RODBC. Didn't use AUTHENTICATIONPARAMETER either in RODBC::odbcConnect()
I am actually able to connect to Teradata only relying on DBI and odbc libraries, both on Windows and Ubuntu 18.04. See the following call (driver option is only one of the two, depending on platform):
# Connect to Teradata
dbcon <- DBI::dbConnect(odbc::odbc(),
driver = "Teradata Database ODBC Driver 16.10", # on Windows 10
driver = "Teradata 17 ODBC Driver", # On Ubuntu 18.04, setup via UnixODBC using Teradata ODBC Driver 17.00.00.18 for Ubuntu
DBCName = "myteradatadb.com",
host = "myteradatadb.com",
database = "mydatabase",
MechanismName = "ldap",
port = 1025, # depends on your Teradata backend settings
uid = "user",
pwd = "pwd",
timeout = 10,
encoding = "UTF-8",
charset = "UTF8" # additional charset parameter for correct encoding, check Teradata Character Data Storage
)
Database
Teradata 16.10.06.04
SessionInfo
sessionInfo()
R version 3.6.2 (2019-12-12)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 17763)
Matrix products: default
locale:
[1] LC_COLLATE=English_United States.1252
[2] LC_CTYPE=English_United States.1252
[3] LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C
[5] LC_TIME=English_United States.1252
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] assertthat_0.2.1 config_0.3 readr_1.3.1 odbc_1.2.2
loaded via a namespace (and not attached):
[1] Rcpp_1.0.3 crayon_1.3.4 R6_2.4.1 DBI_1.1.0
[5] pillar_1.4.3 rlang_0.4.5 blob_1.2.1 vctrs_0.2.4
[9] bit64_0.9-7 bit_1.1-15.2 hms_0.5.3 yaml_2.2.1
[13] compiler_3.6.2 pkgconfig_2.0.3 tibble_2.1.3
Thanks for filing this bug report! Unfortunately because it's hard to reproduce so we don't have the development resources to fix at this time. It's our policy to close such issues to help stay focussed on the biggest problems, but the issue is still indexed by google, so if other people hit it, they'll be able to find it, and we can consider reopen it if it turns out to be a common problem. Thanks for reporting and I'm sorry we couldn't help more 😞.
