executeStoredProcedure: SQL 저장 프로시저를 실행합니다.

executeStoredProcedure: 데이터베이스에 등록된 저장 프로시저를 실행합니다.

사용

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

인수

sqlSP

유효한 StoredProcedure 개체

...

저장 프로시저의 선택적 입력 및 출력 매개 변수입니다. 기본 쿼리 또는 값이 할당되지 않은 모든 매개 변수를 제공해야 합니다.

connectionString

문자열입니다(연결 문자열 없이 StoredProcedure 개체가 생성된 경우 제공해야 함). 이 함수를 사용하려면 ODBC 3.8 기능을 지원하는 ODBC 드라이버를 사용해야 합니다.

verbose

Boolean입니다. 저장 프로시저를 실행하는 데 사용되는 명령을 출력할지 여부

값

성공하면 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)