executeStoredProcedure: Execute a SQL Stored Procedure
executeStoredProcedure
: Executes a stored procedure registered with the database
executeStoredProcedure(sqlSP, ..., connectionString = NULL)
a valid StoredProcedure Object
Optional input and output parameters for the stored procedure. All of the parameters that do not have default queries or values assigned to them must be provided
A character string (must be provided if the StoredProcedure object was created without a connection string). This function requires using an ODBC driver which supports ODBC 3.8 functionality.
Boolean. Whether to print out the command used to execute the stored procedure
TRUE on success, FALSE on failure
This function relies that the ODBC driver used supports ODBC 3.8 features. Otherwise it will fail.
## Not run:
# See ?StoredProcedure for creating the "cleandata" table.
############# Example 1 #############
# Create a linear model and store in the "rdata" table.
train <- function(in_df) {
factorLevels <- c("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")
in_df[,"DayOfWeek"] <- factor(in_df[,"DayOfWeek"], levels=factorLevels)
# The model formula
formula <- ArrDelay ~ CRSDepTime + DayOfWeek + CRSDepHour:DayOfWeek
# Train the model
mm <- rxLinMod(formula, data = in_df, transformFunc = NULL, transformVars = NULL)
# Store the model into the database
# rdata needs to be created beforehand
conStr <- paste0("Driver={ODBC Driver 13 for SQL Server};Server=.;",
"Database=RevoTestDB;Trusted_Connection=Yes;")
out.table = "rdata"
# write the model to the table
ds = RxOdbcData(table = out.table, connectionString = conStr)
rxWriteObject(ds, "linmod.v1", mm, keyName = "key",
valueName = "value")
return(NULL)
}
conStr <- "Driver={ODBC Driver 13 for SQL Server};Server=.;Database=RevoTestDB;Trusted_Connection=Yes;"
# create an InputData object for the input data frame in_df
indata <- InputData("in_df",
defaultQuery = paste0("select top 10000 ArrDelay,CRSDepTime,",
"DayOfWeek,CRSDepHour from cleanData"))
# create the sql server stored procedure object
trainSP1 <- StoredProcedure('train', "spTrain_df_to_df", indata,
dbName = "RevoTestDB",
connectionString = conStr,
filePath = ".")
# spRegisterSp and executeStoredProcedure do not require a connection string since we
# provided one when we created trainSP1
registerStoredProcedure(trainSP1)
executeStoredProcedure(trainSP1, verbose = TRUE)
############# Example 2 #############
# score1 makes a batch prediction given clean data(indata),
# model object(model_param), and the new name of the variable
# that is being predicted
score1 <- function(in_df, model_param, predVarNameInParam) {
factorLevels <- c("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")
in_df[,"DayOfWeek"] <- factor(in_df[,"DayOfWeek"], levels=factorLevels)
mm <- rxReadObject(as.raw(model_param))
# Predict
result <- rxPredict(modelObject = mm,
data = in_df,
outData = NULL,
predVarNames = predVarNameInParam,
extraVarsToWrite = c("ArrDelay"),
writeModelVars = TRUE,
overwrite = TRUE)
return(list(result = result, pvOutParam = mm$f.pvalue))
}
# create an InputData object for the input data frame in_df
indata <- InputData(name = "in_df", defaultQuery = "SELECT top 10 * from cleanData")
# create InputParameter objects for model_param and predVarNameInParam
model <- InputParameter("model_param", "raw",
defaultQuery = paste("select top 1 value from rdata",
"where [key] = 'linmod.v1'"))
predVarNameInParam <- InputParameter("predVarNameInParam", "character")
# create OutputData object for the data frame inside the return list
outData <- OutputData("result")
# create OutputParameter object for non data frame variable inside the return list
pvOutParam <- OutputParameter("pvOutParam", "numeric")
scoreSP1 <- StoredProcedure(score1, "spScore_df_param_df", indata, model, predVarNameInParam, outData, pvOutParam,
filePath = ".")
conStr <- "Driver={ODBC Driver 13 for SQL Server};Server=.;Database=RevoTestDB;Trusted_Connection=Yes;"
# connection string necessary for registrations and execution
# since we did not pass it to StoredProcedure
registerStoredProcedure(scoreSP1, conStr)
model <- executeStoredProcedure(scoreSP1, predVarNameInParam = "ArrDelayEstimate", connectionString = conStr, verbose = TRUE)
model$data
model$params[[1]]
## End(Not run)