R, Call a SQL Server stored procedure with RJDBC

Question

I would like to call a stored procedure from a function in R. See my code below. unfortunately this code only generates a dataframe without values in it. I would like to fix this with RJDBC&DBI, since there seems to be a problem with 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

This worked for me before RODBC crashed. Is there any difference between RODBC and 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
}

If I execute the stored procedure in SQL Server by hand it will look like this:

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

Can you explain what's wrong and how to fix it?

Answer 1

I found a very easy solution, and I wish I knew this before! Maybe I can help someone else with my answer.

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

output of sqlText:

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

Instead of using the SP execute window you just execute now the SP with the paremters in a new query window.