connecting R to database

There are several ways to do this, but the following are what worked for me.

Connections 

Connecting to a database using ODBC:

N.B. 32-bit ODBC will only work with 32-bit R, so for 64-bit R, 64-bit ODBC needs to be installed

library(RODBC)
  con <- odbcConnect(dsn = "database server name as recorded in ODBC", uid = username, pwd = password) 

N.B For Microsoft SQL Server, I have been told to use odbcDriverConnect instead of odbcConnect.

  

Connecting to Teradata database using JDBC:

library(RJDBC)

drv <- JDBC("com.teradata.jdbc.TeraDriver", "C:/your path to jar file/terajdbc4.jar; C:/your path to jar file/tdgssconfig.jar") 

con <- dbConnect(drv, "jdbc:teradata://host ip details e.g. 000.00.000.00/", user = "username", password = "password", port = port e.g. 00)  

 



Connecting to Oracle database:

library(ROracle)

drv <- dbDriver("Oracle")   

con <- dbConnect(drv, "username", "password", dbname="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host ip details e.g. 000.000.0.000)(PORT= port e.g. 00))(CONNECT_DATA=(SID=sid name)))")  

Working in Database

To run SQL script from R:

sqlQuery(con, your SQL query)

For example, 

sqlQuery(con, "Select * from XYZ where rownum < 10")

To create and write to table:

sqlSave(con, R object name to write, tablename = "schema.table name")

  

To create and write to table without the rownames of the R object:

sqlSave(con, R object name to write, tablename = "schema.table name", rownames=FALSE)

  

To create and write to table by adding to the existing contents of the table:

sqlSave(con, R object name to write, tablename = "schema.table name", append = TRUE)

To disconnect from the database:

odbcClose(con)           #for RODBC

dbDisconnect(con)          #for RJDBC

For Oracle database or using RJDBC:

To run SQL script from R:

dbGetQuery(con, your SQL query)

For example, 

dbGetQuery(con, "Select * from XYZ where rownum < 10")

To read table:

Dat <- dbReadTable(con, "table name")  

To write to table:

dbWriteTable(con, "table name", object name)  

To write to table without the rownames of the R object:

dbWriteTable(con, "table name", object name, row.names = FALSE)  

  

To write to table by replacing the existing contents of the table:

dbWriteTable(con, "table name", object name, overwrite = TRUE)  

To write to table by adding to the existing contents of the table:

dbWriteTable(con, "table name", object name, append = TRUE)  

  

Sometime writing a table with date-time field can create an error when time zones are set differently in the R environment and Oracle. The below is the easiest to fix the error.

1. To change time zone of the R environment:

Sys.setenv(TZ="GMT")

  

2. To change time zone of the Oracle:

Sys.setenv(ORA_SDTZ="GMT")

  

To remove/delete a table:

dbRemoveTable(con, "table name", object name)  

To list all the tables:

dbListTables(con, schema = "schema name (if applicable)")  

To list all fields in a table:

dbListFields(con, "table name", schema = "schema name (if applicable)")  

To disconnect from the database:

dbDisconnect(con)