R/qry_eds.R

#' Query EDS Database from 32bit R
#'
#'@description Using a 32bit ODBC connection to the EDS Database, this
#'function will run a SQL script using your systems 32bit version of R.
#'It will return the results of that query.
#'
#'@param SQL A character vector that contains appropriate SQL for the EDS database.
#'
#'@return A dataframe
#'
#'@details You need to have an ODBC connection to the
#'EDS Database, named "Research". You also need the \code{DBI} package installed.
#'
#'This function creates a temporary directory in the current working directory, and
#'then saves the result of the 32bit query to EDS database in that temp directory.
#'It then reads in that data, and cleans up the temporary files.
#'
#'@examples
#'
#'# Do not run
#'SQL <- "SELECT TERM_BASIC.TERM_ID,
#'FROM TERM_BASIC
#'WHERE ( TERM_BASIC.SCHOOL  IN  ( 'CC'  ) )"
#'# qry_eds(SQL)
#'
#'@export
qry_eds <- function(SQL, network = NULL){

tmpdir <- create_temp_dir()
datapath <- paste0(tmpdir,"/data.csv")
r_code_path <- paste0(tmpdir, "/r_code.R")

code_qry <- paste0("SQL <- \"", SQL,"\"")

call_qry <- '
cn <- DBI::dbConnect(odbc::odbc(), "Research")

return <- DBI::dbGetQuery(cn, SQL)

DBI::dbDisconnect(cn)

'

network <- is_network()

if(network == TRUE){
  code_write_csv <- paste0("write.csv(return,'", paste0("\\\\", datapath), "', row.names = FALSE)")
} else {
  code_write_csv <- paste0("write.csv(return,'", datapath, "', row.names = FALSE)")
}

code <- paste(as.character(code_qry),
              as.character(call_qry),
              as.character(code_write_csv),
              collapse = "\n")

write.table(code,
            file = r_code_path,
            quote = FALSE,
            col.names = FALSE,
            row.names = FALSE)

system(paste0(Sys.getenv("R_HOME"),
              "/bin/i386/Rscript.exe ",
              r_code_path),
       wait = TRUE)

data <- read.csv(datapath, stringsAsFactors = FALSE)

remove_temp_dir(tmpdir)

return(data)

}
christian-million/researchR documentation built on May 15, 2019, 12:45 p.m.