sql - RODBC:执行包含多个语句的查询

Question

我有一个返回 varbinary 输出参数的存储过程。我想从 R 调用它并捕获返回变量。我试过:

qq <- "declare @mm varbinary(max); exec spTrain_df_to_op @mm_outer = @mm output; select @mm as model;"
conStr <- "Driver={SQL Server};Server=.;Database=airline_new;Trusted_Connection=TRUE;"
dbhandle <- odbcDriverConnect(conStr, rows_at_time = 1)
result <- sqlQuery(dbhandle, qq)

这失败了，结果以 character(0) 出现。可能是 RODBC 不想执行本质上是一系列查询的内容。我将如何解决这个问题？

谢谢!

存储过程如下:

ALTER PROCEDURE [dbo].[spTrain_df_to_op]
  @mm_outer varbinary(max) output
AS
  BEGIN TRY
    exec sp_execute_external_script
      @language = N'R',
      @script = N'
        func <- function() {
          in_df[,"DayOfWeek"] <- factor(in_df[,"DayOfWeek"], levels=as.character(1:7))
          # The model formula
          formula <- ArrDelay ~ CRSDepTime + DayOfWeek + CRSDepHour:DayOfWeek
          # Train the model
          rxSetComputeContext("local")
          mm <- rxLinMod(formula, data=in_df, transformFunc=NULL, transformVars=NULL)
          mm <<- serialize(mm, connection=NULL)
        }
        result = func()
      ',
      @input_data_1 = N'select top 10000 ArrDelay,CRSDepTime,DayOfWeek,CRSDepHour from cleanData',
      @input_data_1_name = N'in_df',
      @params = N'@mm varbinary(max) output',
      @mm = @mm_outer output
  END TRY
  BEGIN CATCH
    THROW;
  END CATCH;

对于踢球，我尝试了以下方法:

qq = "declare @mm varbinary(max); select 2 as hello; select 1 as model"
conStr <- "Driver={SQL Server};Server=.;Database=airline_new;Trusted_Connection=TRUE;"
dbhandle <- odbcDriverConnect(conStr, rows_at_time = 1)
result <- sqlQuery(dbhandle, qq)

这只返回第一个查询的结果:

  hello
1     2

我还尝试将查询设置为

qq = paste0("SET NOCOUNT ON; declare @mm varbinary(max); ",
           "exec spTrain_df_to_op @mm_outer = @mm output; ",
           "SET NOCOUNT OFF; select @mm as model;")

那仍然产生了字符(0)。

从查询中创建存储过程不是一种选择。

Answer 1

诚然，我没有像在您的用例中那样对 PL/SQL 进行过尝试，但我认为它应该可以工作，如果不能的话，希望它对其他人运行存储在单个脚本中的多个查询有用。

您应该能够通过首先将脚本拆分为单独的查询来完成此操作。如果您使用分号作为分隔符(无论如何设置了多少个查询，以及您的第二次尝试是如何构造的)，您可以将查询拆分为一个查询向量并通过循环单独运行每个查询。如果某些查询有您希望之后访问的结果，您可以返回它们并将它们存储在列表中。

library(RODBC)

# an example SQL Script containing multiple queries, seperated by semi-colon
example_script <- 
"select sysdate from dual;
commit;
select sysdate from dual;"

# split the string into a character vector of queries to run
split_queries <- strsplit(example_script, ";",)[[1]]

#prepeare a list to store results
list_of_results <- list() 


ch <- odbcConnect("XX",uid="XX", pwd="XX")

# loop through and run the queries, storing results (if any) in the list
for (i in 1:length(split_queries)) {

  list_of_results[[i]] <- sqlQuery(ch,
                                   split_queries[i],
                                   errors = FALSE)

  Sys.sleep(2)

}

odbcClose(ch)

# show list of results, of course these elements could be anything from strings to data.frames
list_of_results

[[1]]
              SYSDATE
1 2018-03-28 17:15:26

[[2]]
[1] -2

[[3]]
              SYSDATE
1 2018-03-28 17:15:30

• 我已提交“提交”； in 来说明一个命令不有结果只会在结果列表中返回一个负整数位置。

• 我在 sqlQuery 中包含了 errors = FALSE，因为这将允许像这样的命令当表不存在时“删除表”不停止循环。

我的大型脚本有多个drop table create table 查询对。这种方法让我可以将整个脚本一次性放入 R 中并一次性运行。通常我只是在最终查询的结果之后，所以我不会在我去的时候将东西存储在列表中，而只是有一个输出变量，它在循环运行时被覆盖，直到最终的 select 语句将结果转储到变量。