executeStoredProcedure: SQL ストアド プロシージャを実行する

executeStoredProcedure: データベースに登録されているストアド プロシージャを実行します

使用方法

  executeStoredProcedure(sqlSP, ..., connectionString = NULL)

引数

sqlSP

有効な StoredProcedure オブジェクト

...

ストアド プロシージャの省略可能な入力パラメーターと出力パラメーター。 既定のクエリまたは値が割り当てられていないすべてのパラメーターを指定する必要があります

connectionString

文字列 (接続文字列なしで StoredProcedure オブジェクトが作成された場合に指定する必要があります)。 この関数では、ODBC 3.8 機能をサポートする ODBC ドライバーを使用する必要があります。

verbose

ブール型。 ストアド プロシージャの実行に使用されたコマンドを出力するかどうか

値

成功した場合は TRUE、失敗した場合は FALSE

メモ

この関数は、使用される ODBC ドライバーが ODBC 3.8 の機能をサポートしている必要があります。 そうでない場合は失敗します。

使用例

 ## 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)