Using R with RODBCext and RODBC to execute a SQL stored procedure

Question

I am using the RODBCext package and the RODBC package to execute a stored procedure in SQL server 2008 using R. If I use this code the stored procedure works.

query <- "EXEC [dbo].[usp_SchoolMerge] @Number = ?, 
                                       @Name = ?, 
                                       @Type = ?, 
                                       @Comments = ?, 
                                       @DualEnrollment =?, 
                                       @CEP = ?, 
                                       @DistrictGuidId = ?,
                                       @ImportName = ?,
                                       @ImportID = ?"

query <- gsub("[[:space:]]+", " ", query)

con2 <- odbcConnect("database", uid="userid", pwd="password")

for(i in 1:nrow(us11_12_00_school)) {

  sqlExecute(con2, query, us11_12_00_school[i,])

}

odbcClose(con2)

If I try to use the vectorized form explained here under 2.3.2 Using parameterized queries.

query <- "EXEC [dbo].[usp_SchoolMerge] @Number = ?, 
                                       @Name = ?, 
                                       @Type = ?, 
                                       @Comments = ?, 
                                       @DualEnrollment =?, 
                                       @CEP = ?, 
                                       @DistrictGuidId = ?,
                                       @ImportName = ?,
                                       @ImportID = ?"

query <- gsub("[[:space:]]+", " ", query)

con2 <- odbcConnect("database", uid="userid", pwd="password")

sqlExecute(con2, query, us11_12_00_school)

odbcClose(con2)

I get this error in R.

Error in sqlExecute(con2, query, us11_12_00_school) : 
  24000 0 [Microsoft][ODBC SQL Server Driver]Invalid cursor state
[RODBCext] Error: SQLExecute failed

If I use a data frame with only one row the vectorized code works. Anyone else had this problem? Any ideas?

Answer 1

Seems like you want to use only the first column of the us11_12_00_school - which you pass in the loop-version of the function with us11_12_00_school[i,]. In the vectorised version though, you pass in the whole dataframe!

I havent tested it out, but i guess passing the dataframe as us11_12_00_school[1,] in the vectorised version would give you the expected results?

Answer 2

I use the code below, and seems to work well. Hopefully it helps you too.

require(RODBC)
require(RODBCext)

ExecuteQuery <- function(query, arguments){

  dbhandle <- odbcDriverConnect('driver={SQL Server Native Client 11.0};server=SereverName;database=DbName;trusted_connection=yes')
  if(missing(arguments))
    result <- sqlExecute(dbhandle, query = query, fetch = TRUE)  
  else
    result <- sqlExecute(dbhandle, query =query, data=arguments, TRUE)  
  close(dbhandle)
  return(result)
}

CallProcWithSomeParams <- function(param1, param2){

  ExecuteQuery('exec dbo.procName ?, ?', data.frame(param1, param2))
}