The RJDBC package enables an R application to use the Teradata JDBC Driver to access the Teradata Database.
DBI is the standard database interface for R, and RJDBC is an implementation of DBI that serves as an adapter to translate DBI actions into the corresponding JDBC actions. This combination enables R to use a JDBC Driver, such as the Teradata JDBC Driver.
R application ⇒ RJDBC ⇒ Teradata JDBC Driver ⇒ Teradata Database
Configuring R to use the Teradata JDBC Driver
NOTE: Installing RJDBC will also install the dependencies “DBI” and “rJava”.
Connect to the Teradata Database using RJDBC
NOTE: Loading RJDBC will also load the required packages “DBI” and “rJava”.
The Teradata JDBC driver is ready to connect to the Teradata database.
Using the R Console, enter the following steps below to make a Teradata connection, submit a SQL query, and disconnect:
drv = JDBC("com.teradata.jdbc.TeraDriver","c:\terajdbc\terajdbc4.jar;c:\terajdbc\tdgssconfig.jar")
NOTE: A path on a UNIX machine would use forward slashes to separate its components and a semicolon between files.
conn = dbConnect(drv,"jdbc:teradata://system/TMODE=ANSI","user1","password1")
NOTE: Connection parameters are optional. The first ParameterName is separated from the DatabaseServerName by a forward slash character.
dbGetQuery(conn,"select * from dbc.dbcinfo")
Below is an example of an R script using the Teradata JDBC Driver to connect to a Teradata Database and submit queries.
dbSendUpdate(conn,"drop table employee")
dbSendUpdate(conn,"create table employee
empID INTEGER NOT NULL,
empName VARCHAR(30) NOT NULL,
empDept VARCHAR(50) NOT NULL,
dbSendQuery(conn,"INSERT INTO employee VALUES(100001,'Mike Smith','Product Development','QA engineer')")
dbSendQuery(conn,"INSERT INTO employee VALUES(100002,'James Parker','Marketing','Manager')")
dbGetQuery(conn, "select * from employee order by 1")
The output from the R Console shows:
I am trying RJDBC but getting error on below command:
Error in .jcall(drv@jdrv, "Ljava/sql/Connection;", "connect", as.character(url), :
java.lang.NoClassDefFoundError: Could not initialize class com.teradata.tdgss.jtdgss.TdgssManager
I have verified that the system has TDGss installed already.
Any help you can provide with this error would be appreciated. Thanks.
The Teradata JDBC Driver does not use the C-based TDGSS or TeraGSS packages, so it is not relevant whether or not they are installed.
The exception that you got is due to tdgssconfig.jar not being available on the classpath.
Please refer to the following links for more information, and a remedy.
When you created the "drv" object, did you concatenate paths to both terajdbc4.jar and tdgssconfig.jar in the classPath specification?
The code from the link: ttps://github.com/s-u/RJDBC/issues/35 worked!! Thanks guys. Here is the code snipped we used:
library(RJDBC) library(DBI) library(rJava) JAR_DIR <- "path/to/drivers/" drv <- try(JDBC("com.teradata.jdbc.TeraDriver", paste0(JAR_DIR, 'terajdbc4.jar'))) .jaddClassPath(paste(JAR_DIR, "terajdbc4.jar", sep="")) .jaddClassPath(paste(JAR_DIR, "tdgssconfig.jar", sep=""))
drv <- JDBC(driverClass="com.teradata.jdbc.TeraDriver") databse <- dbConnect(drv,"jdbc:teradata://tdsystemname/TMODE=ANSI,charset=UTF8,USER=sysuser,PASSWORD=pass123")
Now nxt question. Where do I specify the connection mechanism as LDAP? How do i make it use my LDAP password instead of my Teradata password?
Documentation for the Teradata JDBC Driver connection parameters is available here:
You must specify the LOGMECH=LDAP connection parameter in order to use LDAP.
You must specify your LDAP password as the value for the PASSWORD= connection parameter.
Thanks for the quick response Tom but unfortunately I am getting error when i use LOGMECH=LDAP in my jdbc url. here is my code:
databse <- dbConnect(drv,"jdbc:teradata://sysname/TMODE=ANSI,charset=UTF8,LOGMECH=LDAP,USER=sysuser,PASSWORD=myldappwd123")
2017-08-09.11:39:42.741 TERAJDBC4 ERROR [main] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5158b42f com.teradata.tdgss.jtdgss.TdgssCredential.<init>(Unknown Source) (Thread # main) : Invalid name provided (Mechanism level: [LDAPV3_ERR_INVALID_ARGUMENT] An argument was not valid.)
The format of your call is correct. Could you please add the LOG=DEBUG parameter to the JDBC URL and provide the output it generates. We need the more detailed infromation to determine what is failing. This article has information on collecting that information in R:
i have the debug log but it won't fit as is in my reply and i can't figure out a way to attach it as a file to the forum. can you share your email address so that i can email it to you? Alternatively you can email me at firstname.lastname@example.org and I'll be glad to share the file with you. Thanks.