SQLData.R

GetSqlQuery <- function(SQL_QUERY_FILEPATH,
                        DEBUG = TRUE){
 
    if(DEBUG) message("GetSqlQuery: Script Initialized  \n")
      
    FUNCTION_OUTPUT <- list()
  
    con = file(SQL_QUERY_FILEPATH, "r")
    
    sql_string <- ""
  
    if(DEBUG) message("GetSqlQuery: Import SQL query  \n")
    
    while(TRUE){
        line <- readLines(con, n = 1)
    
        if(length(line) == 0){
            break
        }
    
        line <- gsub("\\t", " ", line)
    
        if(grepl("--",line) == TRUE){
            line <- paste(sub("--","/*",line),"*/")
        }
    
        sql_string <- paste(sql_string, line)
    }
  
    FUNCTION_OUTPUT[["SQL_FILEPATH"]] <- SQL_QUERY_FILEPATH
    
    if(DEBUG) message("GetSqlQuery: Collecting final output  \n")
    
    FUNCTION_OUTPUT[["SQL_STRING"]] <- sql_string
  
    if(nchar(sql.string) >= 10){ 
        if(DEBUG) message("GetSqlQuery: SQL string acquisition completed  \n")
     
        return(FUNCTION_OUTPUT)
    } else {
        stop("GetSqlQuery: Empty SQL string returned  \n")    
    }

}





GetSqlData <- function(DRIVER = "SQL Server",
                         SERVER = "sdl02-vm161",
                         DB_NAME = "OpsDW",
                         QUERY_PATH = NULL,
                         DEBUG = TRUE){
     
      if(DEBUG) message("GetSqlData: Function Initialized  \n")

      if(!is.null(QUERY_PATH)){
          stop("GetSqlData: SQL Query path is missing  \n")
      }
        
      start_time = Sys.time()
  
      FUNCTION_OUTPUT <- list()

      if(DEBUG) message("GetSqlData: Connect to database  \n")    
      
      con = dbConnect(odbc(),
                      Driver = DRIVER,
                      Server = SERVER,
                      Database = DB_NAME,
                      Trusted_Connection = "True")
      
      if(DEBUG) message("GetSqlData: Get SQL query  \n")    
      
      which_query <- GET_SQL_QUERY(QUERY_PATH,
                                   DEBUG = TRUE)
      
      if(DEBUG) message("GetSqlData: Pull data from database  \n")    
      
      temp <- dbSendQuery(con, which_query[["SQL_String"]])
          
      query_dat <- dbFetch(temp)

      elapsed_time <- Sys.time() - start_time
      
      FUNCTION_OUTPUT[["DRIVER"]] <- DRIVER
      FUNCTION_OUTPUT[["SERVER"]] <- SERVER
      FUNCTION_OUTPUT[["DB_NAME"]] <- DB_NAME 
      FUNCTION_OUTPUT[["QUERY_PATH"]] <- QUERY_PATH
      FUNCTION_OUTPUT[["QUERY_TEXT"]] <- which_query[["SQL_STRING"]]
      FUNCTION_OUTPUT[["ELAPSED_TIME"]] <- elapsed_time
      
      if(DEBUG) message("GetSqlData: Collecting final output  \n")
      
      FUNCTION_OUTPUT[["FULL_DATA"]] <- query_dat
    
      if(!is.null(query_dat) || !nrow(query_dat) == 0){
          if(DEBUG) message("GetSqlData: Data acquisition completed  \n")
    
          return(FUNCTION_OUTPUT)
      } else {
          stop("GetSqlData: Query returned an empty data set.  \n")
      }
          
      close(con)

}