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
}
)
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.