executeStoredProcedure: Jalankan Prosedur Tersimpan SQL

Artikel
05/23/2023

executeStoredProcedure: Menjalankan prosedur tersimpan yang terdaftar di database

Penggunaan

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

Argumen

`sqlSP`

Objek StoredProcedure yang valid

`...`

Parameter input dan output opsional untuk prosedur tersimpan. Semua parameter yang tidak memiliki kueri atau nilai default yang ditetapkan untuknya harus disediakan

`connectionString`

String karakter (harus disediakan jika objek StoredProcedure dibuat tanpa string koneksi). Fungsi ini memerlukan penggunaan driver ODBC yang mendukung fungsionalitas ODBC 3.8.

`verbose`

Boolean. Apakah akan mencetak perintah yang digunakan untuk menjalankan prosedur tersimpan

Nilai

TRUE pada keberhasilan, FALSE pada kegagalan

Catatan

Fungsi ini mengandalkan bahwa driver ODBC yang digunakan mendukung fitur ODBC 3.8. Jika tidak, itu akan gagal.

Contoh


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

Bagikan melalui