R，使用RJDBC调用Server存储过程

Question

我想从R中的一个函数调用一个存储过程，参见下面的代码。不幸的是，这段代码只生成一个没有值的数据。我想用RJDBC&DBI来解决这个问题，因为RODBC似乎有问题。

 RPT_09_Hourly_Connected_v3<- function(Year, Month="NULL",State = "NULL",Region="NULL", City="NULL", District="NULL", Subdistrict="NULL" ,Address='NULL'){
  drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver", "/opt/sqljdbc_3.0/sqljdbc4.jar") 
  conn <- DBI::dbConnect(drv, "jdbc:sqlserver://***;databaseName=***;user=***;password=***")
  sqlText <- paste("exec [dbo].[RPT_09_Hourly_Connected_v3]@Year=",Year, 
                                                   ",@Month=",Month,
                                                   ",@State=",State,"",
                                                   ",@Region=",Region,"",
                                                   ",@City=N'",City,"'",
                                                   ",@District=",District,"",
                                                   ",@Subdistrict=",Subdistrict,"",
                                                   ",@Address=N'",Address,"'",
                                                   sep="")
  data <- RJDBC::dbGetQuery(conn,sqlText)
}
a<- RPT_09_Hourly_Connected_v3(Year = 2016)

> str(a)
'data.frame':   0 obs. of  9 variables:
 $ Regio          : chr 
 $ Stad           : chr 
 $ Stadsdeel      : chr 
 $ Buurtcombinatie: chr 
 $ Adres          : chr 
 $ Jaar           : num 
 $ Maand          : num 
 $ hourNR         : num 
 $ HoursConnected : num

在RODBC坠毁之前，这对我起了作用。RODBC和RJDBC有什么区别吗？

RPT_09_Hourly_Connected_v3<- function(Year, Month="NULL",State = "NULL",Region="NULL", City="NULL", District="NULL", Subdistrict="NULL" ,Address='NULL'){
  dbhandle <- odbcConnect("***;DATABASE=***;UID=***;PWD=***")
  data <- sqlQuery(dbhandle,paste("exec [ dbo].[RPT_09_Hourly_Connected_v3]@Year=",Year,
                                  ",@Month=",Month,
                                  ",@State=",State,"",
                                  ",@Region=",Region,"",
                                  ",@City=N'",City,"'",
                                  ",@District=",District,"",
                                  ",@Subdistrict=",Subdistrict,"",
                                  ",@Address=N'",Address,"'",
                                  sep=""))
  odbcCloseAll()
  data
}

如果我手动执行Server中的存储过程，它将如下所示：

EXEC    @return_value = [dbo].[RPT_09_Hourly_Connected_v3]
        @Year = 2016,
        @Month = NULL,
        @State = NULL,
        @Region = NULL,
        @City = N'Amsterdam',
        @District = NULL,
        @Subdistrict = NULL,
        @Address = NULL

你能解释一下出什么问题了吗?怎么解决？

Answer 1

我找到了一个很简单的解决办法，我希望我以前知道这一点！也许我能帮别人解决我的问题。

FACT_CHARGESESSION<- function (username, password, country = "NULL",state = "NULL", region = "NULL",city = "NULL",
                           district  = "NULL",subdistrict = "NULL", provider= "NULL",startDateView = "NULL",endDateView = "NULL") {



InstallCandidates <-c("DBI","rJava","RJDBC","dplyr")
  toInstall<-InstallCandidates[!InstallCandidates %in% library()$results[,1]]
  if(length(toInstall) !=0){install.packages(toInstall,repos="http://cran.r-project.org")}
  lapply(InstallCandidates,library,character.only=TRUE)
  rm("InstallCandidates","toInstall")

  NAME <- "dbo.R_00_ValidTransactions_ID_PW_v4"
  options(java.parameters = "- Xmx1024m")
  drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver", "/opt/sqljdbc_3.0/sqljdbc4.jar")
  conn <- dbConnect(drv, "jdbc:sqlserver://***.**.***.***;databaseName=****;user=***;password=***")
  # Make a SQL text 
  sqlText <- paste(NAME, paste(username,password, country,state,region,city,district,subdistrict,provider,startDateView,endDateView,sep=",")) 
  data <- dbGetQuery(conn,sqlText)

  return(data)
}

sqlText的输出：

"dbo.R_00_ValidTransactions_ID_PW_v4 M.Kooi , Stackoverflow , NULL , NULL , Amsterdam , NULL , NULL , NULL , NULL , NULL , NULL "

不是使用SP执行窗口，而是在新的查询窗口中执行带有paremters的SP。

Answer 2

我发现RODBCext很容易使用，因为它使用了参数绑定。它还使使用NA代替"NULL"更容易，并消除了正确匹配引号字符的顾虑。

library(RODBCext)
RPT_09_Hourly_Connected_v3<- function(Year, Month=NA, State = NA, Region=NA, City=NA, District=NA, Subdistrict=NA ,Address=NA){
  ch <- odbcDriverConnect([connection_string])

  sqlText <- paste("exec [dbo].[RPT_09_Hourly_Connected_v3]@Year=? ", 
                                                   ",@Month=? ",
                                                   ",@State=? ",
                                                   ",@Region=? ",
                                                   ",@City=? ",
                                                   ",@District=? ",
                                                   ",@Subdistrict=? ",
                                                   ",@Address=? ",
                                                   sep="")
  sqlExecute(channel = ch,
    query = sqlText,
    data = list(Year, Month, State, Region, City, District, Subdistrict, Address),
    fetch = TRUE,
    stringAsFactors = FALSE)
}

Answer 3

我过去在RJDBC中成功地使用过这个

d <- dbGetQuery(conn, paste0("exec my_STOREDPROC @Field1= '",Field1,"';"))

这可能只是一个语法问题。很难说出一个可重复的例子。注意到额外的一组引号，。