It will return a R - Data frame Object
The below script:
Prerequisites:
## 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)