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)