R/dbUpdateTable.R

library(RSQLServer)
library(RJDBC)
library(magrittr)
library(assertthat)


setGeneric("dbUpdateTable", 
           valueClass = "logical",
           signature = c("conn", "name", "value", "key_var"),
           function(conn, name, value, key_var, ...) {
             standardGeneric("dbUpdateTable")
           }
)


setMethod(
  "dbUpdateTable",
  signature("SQLServerConnection"),
  definition = function(conn, name, value, key_var, verbose = TRUE) {
    # https://github.com/imanuelcostigan/RSQLServer/blob/master/R/dbi-methods.R#L332
    name <- toupper(as.character(name))
    key_var <- as.character(key_var)
    
    assertthat::assert_that(
      length(key_var) && is.character(key_var),
      msg = sprintf("key_var = %s", key_var)
    )
    assertthat::assert_that(
      length(key_var) == 1L
    )
    assertthat::assert_that(
      key_var %in% names(value),
      msg = paste0('Cannot find "', key_var, '" in names of value')
    )
    assertthat::assert_that(
      dbExistsTable(conn, name),
      msg = paste0("Table not found: ", name)
    )
    
    res_txn <- dbWithTransaction(conn, {
      value <- as.data.frame(value)
      fields <- setdiff(colnames(value), key_var)
      
      res <- dbSendStatement(
        conn, 
        sprintf('update %s set %s WHERE %s',
                name,
                paste(paste0('"', fields, '"=?'), collapse = ", "),
                sprintf("%s=?", key_var)), 
        params = value[, c(fields, key_var)],
        batch = FALSE
      )
      dbClearResult(res)
    })
    res_txn
  }
)
leoluyi/DBIutils documentation built on May 11, 2019, 7:23 p.m.