mssql-RODBC.R

library(RODBC)
library(RODBCext)

# this will connect using "ScienceOps" credentials if the system user is "root"
# and will conenct using Windows credentials otherwise
connectToMSDb <- function() {
  write("reconnecting to database...", stderr())
  odbcConnect(Sys.getenv("DB_URL")) # using the Database URL stored as a environment variable (see http://sandbox.yhathq.com/models/{modelname}/settings - Environment Variables)
  # odbcConnect("Server=FOO;Database=BAR;Trusted_Connection=True;") # using a hard-coded DB URL
}

# safeMSQuery now takes 2 arguments:
#   1) the query we're executing
#   2) the parameterized variables for the query
# *** note that this will only work for writes. for reads, use sqlQuery
safeMSQuery <- function(query, data) {
  tryCatch({
    sqlExecute(MSSQL.db.con, query, data)
  }, error = function(e) {
    print(e)
    MSSQL.db.con <<- connectToMSDb()
    sqlExecute(MSSQL.db.con, query, data)
  })
}

writeDataToDB <- function(field1, field2) {
  # SQL insert statement for decision data. we're using RODBCext's SQL parameterization features
  # which will automatically escape any data we pass to the query. for example, if I was trying
  # to insert a field with the value "Greg's pizza", RODBCext will automatically handle the single-quote (')
  # safely.
  record_creation <- Sys.time()
  q <- "
    INSERT INTO my_table_name(column_name1, column_name2)
    VALUES (?, ?);"
  data <- data.frame(
    column_name1=field1,
    column_name2=field2
  )
  safeMSQuery(q, data)
}