I'm trying to use the SQL engine within knitr: http://rmarkdown.rstudio.com/authoring_knitr_engines.html#sql
The documentation states that "to use the knitr SQL engine you first need to establish a DBI connection to a database"
And it provides the following example:
db <- dbConnect(RSQLite::SQLite(), dbname = "sql.sqlite")
drv = JDBC("com.teradata.jdbc.TeraDriver","ClasspathForTeradataJDBCDriverFiles")
conn = dbConnect(drv,"jdbc:teradata://DatabaseServerName/ParameterName=Value","User","Password")
You cannot connect with the DBI package - that just defines an interface (think template) that DBI compliant packages have to implement. What you should be doing is using the RJDBC package - which implements the DBI methods. Don't use RODBC since it does not implement the DBI methods - and I am guessing you need DBI based on your question. To create a connection to Teradata using RJDBC you need to follow a few steps.
You need to the JDBC driver from Teradata here. You will need to be a customer of Teradata to get this.
If you download and look inside the zip or tar file you will find two jar files:
Extract these files to a known location on your system.
Now you need to install install.packages('RJDBC') package and dependencies.
Since JDBC is Java based make sure you have the latest Java Runtime installed. You can pick this up at java.com - just watch out that you get the same version of Java (32 or 64 bit) as you have for R. Last time I checked the java site did not try and figure this out for you. If you have to install Java make sure you restart your R session.
Now you should be able to load the RJDBC library and create your connection - you will need to know the ip/url of the teradata system you want to connect to, as well as your username and password. You will also need to specify the database you want to connect to:
library(RJDBC) jars <- c("[path to jar]/terajdbc4.jar", "[path to jar]/tdgssconfig.jar") drv <- JDBC("com.teradata.jdbc.TeraDriver",jars) db <- "jdbc:teradata://[ip of teradata system]/TMODE=TERADATA,charset=UTF8" conn <- dbConnect(drv, db, username, password, database )
With this connection you can now use the implemented DBI methods to query the database - for example dbGetQuery(conn, sql) or dbSendQuery().
Check out the RJDBC docs for more information.