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?

In your line for `",@City=N'", City,"'"`, Should that `N` be there? I think SQL is going to read that command as `@City=N'Detroit'`, for example. I'm surprised that your coded doesn't return an error. You have the same thing in the Address line. is this a variable type I'm not familiar with? — Benjamin, Jul 01 '16 at 14:27

score 1 · Answer 1 · answered Jul 01 '16 at 14:35

I find the RODBCext a lot easier to use since it uses parameter binding. It also makes it easier to use NA in place of "NULL" and eliminates the concern about matching up the quote characters correctly.

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

This seems like a very nice way, unfortunately `RODBC` doesnt work for me :( — M. Kooi, Jul 01 '16 at 14:50

score 1 · Accepted Answer · answered Feb 17 '17 at 10:34

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.

Serban Tanasa · Answer 3 · 2016-07-01T14:16:00.337

0

I've used this in the past successfully with RJDBC:

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

It might be simply a matter of syntax. Hard to tell w/o a reproducible example. Note the extra set of quotes.

edited Jul 01 '16 at 14:16

answered Jul 01 '16 at 14:09

Serban Tanasa

3,592
2
23
45

R, Call a SQL Server stored procedure with RJDBC

3 Answers3