R/odbc.R

In AnotherSamWilson/helperFuncs: Helper Functions for common manipuations

#' @title sendTable
#' @description Upload a table to SQL Server
#' @param server Placeholder
#' @param database Placeholder
#' @param schema Placeholder
#' @param tableName Placeholder
#' @param dt Placeholder
#' @param append Placeholder
#' @return Frequency Encoded Object
#' @importFrom odbc odbc
#' @importFrom DBI dbWriteTable Id dbDisconnect dbConnect
#' @importFrom data.table data.table
#' @export
sendTable <- function(
    driver = "ODBC Driver 13 for SQL Server"
  , server
  , database
  , schema
  , tableName
  , dt
  , append = TRUE
) {

  con <- dbConnect(
      odbc::odbc()
    , driver = driver
    , server = server
    , database = database
    , trusted_connection = "Yes"
  )

  DBI::dbWriteTable(
      conn = con
    , name = DBI::Id(schema = schema, name = tableName)
    , value = dt
    , append = append
  )

  dbDisconnect(con)                   # Disconnect

}


#' @title getQuery
#' @description Placeholder
#' @param driver Placeholder
#' @param server Placeholder
#' @param database Placeholder
#' @param sql Placeholder
#' @return data.table results from sql
#' @importFrom DBI dbConnect dbSendQuery dbFetch dbClearResult dbDisconnect
#' @importFrom data.table as.data.table
#' @importFrom odbc odbc
#' @export
getQuery <- function(
  driver = "ODBC Driver 13 for SQL Server"
  , server
  , database
  , sql
  ) {

  # Create odbcConnection object
  con <- dbConnect(
      odbc::odbc()
    , driver = driver
    , server = server
    , database = database
    , trusted_connection = "Yes"
  )

  # Create odbcResult object. This executes the query in SQL server but does not return any results.
  rs <- dbSendQuery(con, sql)

  # Run fetch result object and store result in fetchedrows
  fetchedrows <- dbFetch(rs)

  # Delete result object
  dbClearResult(rs)

  # Disconnect
  dbDisconnect(con)

  return(as.data.table(fetchedrows))

}