R - Database (JDBC, )

Card Puncher Data Processing

About

It will return a R - Data frame Object

Specification

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







Share this page:
Follow us:
Task Runner