R - Database (JDBC, …)

About

It will return a R - Data frame Object

Specification

• Standard: DBI: Database Interface Definition

Implementation

• JDBC: RJDBC
• ODBC: RODBC
• ROracle: With OCI

RJDBC Example against Oracle

The below script:

• create a database connection
• get a data.frame the query from a file query.sql located in the same directory than the script
• and print a summary.

Prerequisites:

• The following environment variable are needed:
  • DB_URL_FULL: The Java URL with the username and password. Example: jdbc:oracle:thin:user/password//host:1521/ServiceName
  • JAVA_LIB_PATH: The path where the “ojdbc-6.jar” is located.
• The script must be run in R Studio to be able to set the working Directory

## Set enough memory
options(java.parameters = "-Xmx8048m")

## Load the library
library(RJDBC)

## Environment
DB_URL_FULL = Sys.getenv("DB_URL_FULL")
JAVA_LIB_PATH = Sys.getenv("JAVA_LIB_PATH") 
CLASSPATH = paste(JAVA_LIB_PATH, "ojdbc-6.jar", sep="\\" )

## Working path
## Works only in Studio
SCRIPT_PATH=dirname(rstudioapi::getActiveDocumentContext()$path) 
setwd(SCRIPT_PATH)

## Get a connection
drv <- JDBC("oracle.jdbc.OracleDriver", classPath=CLASSPATH, " ")
con <- dbConnect(drv, DB_URL_FULL)

## Get the data
query <- paste(readLines("query.sql"), collapse="\n") # Paste to Collapse From a vector[n] to vector[1]

## Modify the date string format
## because the parameters can be send only in a string format
## Below is an example for Oracle
dbSendUpdate(con, "ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'")

## Get the data with a parametrized query (with binding variable)
## params are parameters of the rmd file (Knit with parameters)
res = dbGetQuery(con, query, params$test_user, params$start_date, params$end_date)

## Close the database
dbDisconnect(con)

## Show a summary
summary(res)

Documentation / Reference

• https://blogs.oracle.com/R/entry/r_to_oracle_database_connectivity