Teradata Configuration Help Needed #92

happyshows · 2017-07-19T13:41:02Z

Hi Jim,

I was following Aaron's advice and start switching to odbc. However I do have problems in making connection work. Below are my configurations, but it doesn't work nor give me enough info to figure out why. I also tried to escape the space between application and support, but it triggers a file not found error.

odbc.ini
[Teradata]
Driver = Teradata Driver
Database = xxxxxx
Servername = xxxxxx
UserName = xxxxxx
Password = xxxxxx
Port = 1025

odbcinst.ini
[Teradata Driver]
Driver = /Library/Application Support/Teradata/Client/16.00/lib/tdata.dylib

dbConnect(odbc::odbc(), "Teradata")
Error: nanodbc/nanodbc.cpp:950: 00000:

The text was updated successfully, but these errors were encountered:

jimhester · 2017-07-19T14:05:02Z

Please follow the provided issue template, in particular specify what version of the odbc package you are using.

The error is coming from a call to SQLDriverConnect() so is likely an error in the server connection settings. Are you able to connect with RODBC using the above settings?

happyshows · 2017-07-19T16:56:48Z

Issue Description and Expected Result

Not able to connect

Database

Teradata 15

Reproducible Example

library(odbc)
library(DBI)
dbConnect(odbc::odbc(), "Teradata")
Error: nanodbc/nanodbc.cpp:950: 00000:

-->

Session Info

> devtools::session_info()
Session info --------------------------------------------------------------------------------------------------
 setting  value                       
 version  R version 3.3.1 (2016-06-21)
 system   x86_64, darwin13.4.0        
 ui       RStudio (1.0.143)           
 language (EN)                        
 collate  en_US.UTF-8                 
 tz       America/Chicago             
 date     2017-07-19                  

Packages ------------------------------------------------------------------------------------------------------
 package  * version    date       source                           
 bit        1.1-12     2014-04-09 CRAN (R 3.3.0)                   
 bit64      0.9-7      2017-05-08 cran (@0.9-7)                    
 blob       1.1.0      2017-07-07 Github (hadley/blob@9dd54d9)     
 DBI      * 0.7        2017-06-18 cran (@0.7)                      
 devtools   1.12.0     2016-06-24 CRAN (R 3.3.0)                   
 digest     0.6.12     2017-01-27 cran (@0.6.12)                   
 hms        0.3        2016-11-22 CRAN (R 3.3.2)                   
 memoise    1.0.0      2016-01-29 CRAN (R 3.3.0)                   
 odbc     * 1.1.1.9000 2017-07-19 Github (rstats-db/odbc@dc42360)  
 Rcpp       0.12.12    2017-07-15 cran (@0.12.12)                  
 rlang      0.1.1.9000 2017-06-26 Github (hadley/rlang@8594edf)    
 tibble     1.3.3      2017-05-29 Github (tidyverse/tibble@b2275d5)
 withr      1.0.2      2016-06-20 CRAN (R 3.3.0)

happyshows · 2017-07-19T17:02:23Z

I just tried RODBC

library(RODBC)
ch <- odbcConnect(dsn="Teradata",uid="XXXXX",pwd="XXXXX")

Warning messages:
1: In RODBC::odbcDriverConnect("DSN=Teradata;UID=XXXXX;PWD=XXXXX") :
[RODBC] ERROR: state IM002, code 45083136, message [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded

I have previously uninstalled iODBC and reinstalled it, not sure why RODBC is still pointing to it.

happyshows · 2017-07-19T17:36:06Z

Update, this may not be directly related to odbc issue, since odbc suggests to use unixodbc (which may not work well with Teradata?)

I looked online about iODBC and found the linked ini are under /Library/ODBC folder. so I went there and configured both files:

[edwops]
Driver = Teradata Database ODBC Driver 16.00
Description = edwops
DBCName = XXXXXXX
DefaultDatabase = XXXXXX
MechanismName = TD2
Username = XXXXXX

[Teradata Database ODBC Driver 16.00]
Driver = /Library/Application Support/teradata/client/16.00/lib/tdata.dylib

Then I was able to connect

> odbcConnect(dsn="edwops",uid=names(cred),pwd=cred)
RODBC Connection 4
Details:
  case=nochange
  DSN=edwops
  UID=xxxxx
  PWD=******

After that, I copied exact content to the unixodbc linked inis, still seeing the same error.

jimhester · 2017-07-19T18:00:57Z

It looks to me like you need a recent version of unixODBC (2.3.0 >) to be compatible with the teradata driver.

If you do want to use iODBC you can do so by installing odbc with options INCLUDE_DIR=/xyz/lib LIB_DIR=/xyz/include where xyz is where the iodbc libraries are installed. The package should work with both driver managers, however iODBC has some other issues with other databases, which is why it is recommended to use unixodbc instead.

happyshows · 2017-07-19T18:15:27Z

Hey Jim,

I started off with the guide on landing page, and my unixodbc is the latest version - 2.3.4.

happyshows · 2017-07-20T13:33:38Z

@jimhester could you also guide me how to find the iodbc libraries? what search string should I use?

jimhester · 2017-08-08T21:50:14Z

The teradata driver and odbc need to be compiled against the same driver manager. You can check which one the driver is expecting using otool -L /Library/ApplicationSupport/Teradata/Client/16.00/lib/tdata.dylib. One of the lines will either be libodbc.2.dylib (if it is linked to unixODBC) or libiodbc.2.dylib if it is linked to iODBC.

If the driver is compiled against iODBC you will need to make sure odbc is also compiled against iODBC rather than unixODBC. The easiest way to do this is to make sure that iodbc-config is in your PATH, and odbc_config is not, then run devtools::install_github().

Alternatively you can specify the PKG_LIBS, INCLUDE_DIR and LIB_DIR paths explicitly in the configure arguments. Here is what it would look like on my system, with libiodbc installed by homebrew.

devtools::install_github("rstats-db/odbc", args = "--configure-vars=PKG_LIBS=-liodbc INCLUDE_DIR=/usr/local/Cellar/libiodbc/3.52.12/include LIB_DIR=/usr/local/Cellar/libiodbc/3.52.12/lib/")

happyshows · 2017-08-10T12:56:32Z

Hi Jim,

I tried both approaches, but somehow it's still pointing to unixodbc automatically. My libodbc path is identical to yours

> devtools::install_github("rstats-db/odbc", args = "--configure-vars=PKG_LIBS=-liodbc INCLUDE_DIR=/usr/local/Cellar/libiodbc/3.52.12/include LIB_DIR=/usr/local/Cellar/libiodbc/3.52.12/lib/", force = T)
Downloading GitHub repo rstats-db/odbc@master
from URL https://api.github.com/repos/rstats-db/odbc/zipball/master
Installing odbc
'/Library/Frameworks/R.framework/Resources/bin/R' --no-site-file --no-environ --no-save --no-restore --quiet CMD  \
  INSTALL  \
  '/private/var/folders/72/j1qmrjt11cg7tsg0byvjkjh00000gp/T/RtmpYJJyA6/devtoolsc8654dd9d334/rstats-db-odbc-1e4a329'  \
  --library='/Library/Frameworks/R.framework/Versions/3.3/Resources/library' --install-tests  \
  --configure-vars=PKG_LIBS=-liodbc INCLUDE_DIR=/usr/local/Cellar/libiodbc/3.52.12/include  \
  LIB_DIR=/usr/local/Cellar/libiodbc/3.52.12/lib/ 

Warning: invalid package ‘INCLUDE_DIR=/usr/local/Cellar/libiodbc/3.52.12/include’
Warning: invalid package ‘LIB_DIR=/usr/local/Cellar/libiodbc/3.52.12/lib/’
* installing *source* package ‘odbc’ ...
Found pkg-config cflags and libs!
PKG_CFLAGS=-DHAVE_UNISTD_H -DHAVE_PWD_H -DHAVE_SYS_TYPES_H -DHAVE_LONG_LONG -DSIZEOF_LONG_INT=8 -I/usr/local/Cellar/unixodbc/2.3.4/include 
PKG_LIBS=-L/usr/local/Cellar/unixodbc/2.3.4/lib -lodbc

Still same msg when I try to connect
Error: nanodbc/nanodbc.cpp:950: 00000:

SHOWS:etc showsle$ otool -L /Library/Application\ Support/teradata/client/16.00/lib/tdata.dylib
/Library/Application Support/teradata/client/16.00/lib/tdata.dylib:
	tdata.dylib (compatibility version 0.0.0, current version 0.0.0)
	/System/Library/Frameworks/CoreFoundation.framework/Versions/A/CoreFoundation (compatibility version 150.0.0, current version 635.21.0)
	/usr/lib/libiodbcinst.2.dylib (compatibility version 4.0.0, current version 4.18.0)
	/usr/lib/libstdc++.6.dylib (compatibility version 7.0.0, current version 52.0.0)
	/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 159.1.0)

SHOWS:~ showsle$ echo $PATH
/usr/local/iODBC/bin:/Library/Application Support/teradata/client/16.00/bin:/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/iODBC/bin:/opt/X11/bin:/Library/TeX/texbin

SHOWS:~ showsle$ locate libiodbc
/Applications/Tableau.app/Contents/Frameworks/libiodbc.2.dylib
/Applications/Tableau.app/Contents/Frameworks/libiodbcinst.2.dylib
/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX.sdk/usr/lib/libiodbc.2.1.18.tbd
/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX.sdk/usr/lib/libiodbc.2.tbd
/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX.sdk/usr/lib/libiodbc.tbd
/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX.sdk/usr/lib/libiodbcinst.2.1.18.tbd
/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX.sdk/usr/lib/libiodbcinst.2.tbd
/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX.sdk/usr/lib/libiodbcinst.tbd
/usr/lib/libiodbc.2.1.18.dylib
/usr/lib/libiodbc.2.dylib
/usr/lib/libiodbc.dylib
/usr/lib/libiodbcinst.2.1.18.dylib
/usr/lib/libiodbcinst.2.dylib
/usr/lib/libiodbcinst.dylib
/usr/lib/pkgconfig/libiodbc.pc
/usr/local/Homebrew/Library/Taps/homebrew/homebrew-core/Formula/libiodbc.rb
/usr/local/iODBC/lib/libiodbc.dylib
/usr/local/iODBC/lib/libiodbcinst.dylib

jimhester · 2017-08-10T13:10:04Z

The relevant message there is

Warning: invalid package ‘INCLUDE_DIR=/usr/local/Cellar/libiodbc/3.52.12/include’
Warning: invalid package ‘LIB_DIR=/usr/local/Cellar/libiodbc/3.52.12/lib/’

You need to tweak the quoting slightly (see the single quotes around the configure-vars value), I was using devel devtools which handles this, but can reproduce your behavior with CRAN devtools.

devtools::install_github("rstats-db/odbc", args = "--configure-vars='PKG_LIBS=-liodbc INCLUDE_DIR=/usr/local/Cellar/libiodbc/3.52.12/include LIB_DIR=/usr/local/Cellar/libiodbc/3.52.12/lib/'", force = T)

happyshows · 2017-08-10T14:20:22Z

Thanks for the help.

After many attempts, I was able to connect (only) via the connect string style

dbConnect(odbc::odbc(),.connection_string = 
                   "Driver={Teradata Database ODBC Driver 16.00};
                 DBCName=dbserverdomain;uid=xxxxx;pwd=xxxx;
                 Host=dbserverdomain;Port=1025;Database=xxxx;"
                 )

However, when I try to use the more traditional style

 dbConnect(odbc::odbc(),
                 driver = "{Teradata Database ODBC Driver 16.00}",
                 DBCName="dbserverdomain",
                 database = "xxxxx",
                 uid =  xxxx,
                 pwd = xxxxx,
                 host = "dbserverdomain",
                 port = 1025)

It told me

Error: nanodbc/nanodbc.cpp:950: 28000: [Teradata][ODBC Teradata Driver][Teradata Database] The UserId, Password or Account is invalid.

Any idea why?

jimhester · 2017-08-10T14:24:48Z

The parameters are case sensitive for some drivers.

happyshows · 2017-08-10T14:31:15Z

oh correction, I was using keyringr for the 2nd case, like below:

cred <- decrypt_kc_pw("sandbox")
names(cred) <- get_kc_account('sandbox')
dbConnect(odbc::odbc(),
          driver = "Teradata Database ODBC Driver 16.00",
          database = "xxxxx",
          uid = names(cred),
          pwd = cred,
          host = "xxxxx",
          port = 1025)

once I swap in the hardcoded string, it works fine. So I guess there's nothing related to odbc package.

Last question before I close the issue, what's the default odbc.ini / odbcinst.ini location for iodbc?
I have a couple of them scattered everywhere, but I don't know which one is really linked. The odbcinst -j command seems to be applicable to unixodbc only...

jimhester · 2017-08-10T14:56:57Z

http://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/FAQ#What%27s%20an%20odbc.ini%20and%20what%20do%20I%20put%20in%20it%3F has information what what search path iODBC uses to find the config files. I don't think there is an equivalent to odbcinst for iODBC.

happyshows · 2017-08-10T15:20:23Z

Appreciate the help along the way!

happyshows mentioned this issue Jul 19, 2017

Connection issue to Teradata using DBI and odbc packages #81

Closed

happyshows closed this as completed Aug 10, 2017

jimhester mentioned this issue Oct 3, 2017

Allow specifying dbms.name #115

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Teradata Configuration Help Needed #92

Teradata Configuration Help Needed #92

happyshows commented Jul 19, 2017

jimhester commented Jul 19, 2017

happyshows commented Jul 19, 2017

happyshows commented Jul 19, 2017

happyshows commented Jul 19, 2017

jimhester commented Jul 19, 2017

happyshows commented Jul 19, 2017

happyshows commented Jul 20, 2017

jimhester commented Aug 8, 2017

happyshows commented Aug 10, 2017

jimhester commented Aug 10, 2017

happyshows commented Aug 10, 2017

jimhester commented Aug 10, 2017

happyshows commented Aug 10, 2017 •

edited

jimhester commented Aug 10, 2017

happyshows commented Aug 10, 2017

Teradata Configuration Help Needed #92

Teradata Configuration Help Needed #92

Comments

happyshows commented Jul 19, 2017

jimhester commented Jul 19, 2017

happyshows commented Jul 19, 2017

Issue Description and Expected Result

Database

Reproducible Example

happyshows commented Jul 19, 2017

happyshows commented Jul 19, 2017

jimhester commented Jul 19, 2017

happyshows commented Jul 19, 2017

happyshows commented Jul 20, 2017

jimhester commented Aug 8, 2017

happyshows commented Aug 10, 2017

jimhester commented Aug 10, 2017

happyshows commented Aug 10, 2017

jimhester commented Aug 10, 2017

happyshows commented Aug 10, 2017 • edited

jimhester commented Aug 10, 2017

happyshows commented Aug 10, 2017

happyshows commented Aug 10, 2017 •

edited