R - Database (JDBC, ...)

> Procedural Languages > R

1 - About

It will return a R - Data frame Object

2 - Specification

3 - Implementation

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

4 - 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)

5 - Documentation / Reference

lang/r/database.txt · Last modified: 2017/02/12 21:02 by gerardnico