R/callSQL.R

In blazickjoe/DataScienceLibrary: DKS R Functions

#' Call SQL Query
#'
#' Call to 32bit R for SQL Query. Handles both with specified external Query to
#' fix for legacy code that had this dependency and new code where queryLoc does not
#' need to be specified.
#'
#' @param query String that is the query to be exectuted.
#' @param queryLoc DEPRICATED Specify the location of the RScript with SQL function to be executed.
#' @param tempPath Set to tempdir but can be any directory, defaults to AppData tempdir.
#' @param db Database being queried.
#' @param username Username for the database being queried.
#' @param password Password for the database being queried.
#' @return Returns table from SQL query.
#'
#' @importFrom utils write.csv
#'
#' @export

callSQL <- function(query, queryLoc = NA, tempPath = NA,
                    db = NA, username = NA, password = NA) {

  #check for user defined path, use default if none provided
  if(is.na(tempPath)) {
    tempPath <- tempdir()
  }

  #error handling for no defined params
  if(is.na(queryLoc) & (is.na(db) | is.na(username) | is.na(password))) {
    stop("queryLoc and db/username/password cannot both be NA.")

  } else if (is.na(queryLoc)){
    #function contained version of SQL Call

    tempPath <- gsub("\\\\", "\\/", tempPath)
    tempPath <- ifelse(grepl("\\/$", tempPath), tempPath, paste0(tempPath,"/"))
    system(paste0(Sys.getenv("R_HOME"), "/bin/i386/Rscript.exe -e ",
                  shQuote(paste0("
                          library(RODBC)
                          myconn <- odbcConnect('",db,"','",username,"','",password,"'",")

                          sqlData <- sqlQuery(myconn,\"",query,"\")

                          write.csv(sqlData, paste0('",tempPath,"','sqlData.csv'),
                                    row.names = F)
                                 ", sep = ""))),
           wait = TRUE,
           invisible = FALSE)

  } else {
    #this version of SQL Call is for Legacy code, and should no longer
    #be used in new development.

    queryLoc <- ifelse(grepl("(\\\\$|\\/$)", queryLoc),
                       queryLoc, paste0(queryLoc, "\\"))
    system(paste0(Sys.getenv("R_HOME"), "/bin/i386/Rscript.exe ",
                  shQuote(paste0(queryLoc,"SQLCall.R"))," ", shQuote(tempPath),
                  " ", shQuote(query)),
           wait = TRUE,
           invisible = FALSE)
  }

  #load the SQL data from the temp directory into environment.
  sqlData <- read.csv(
    list.files(tempPath, full.names = T)[grep("sqlData", x = list.files(tempPath, full.names = T))],
    stringsAsFactors = F
    )
  unlink(
    list.files(tempPath, full.names = T)[grep("sqlData", x = list.files(tempPath, full.names = T))]
    )

  return(sqlData)
}