R dynamic sql query using RODBC and export to .csv

Question

If I run the following code in R studio then it works but I have set sys.sleep. I have a large batch of queries to run and I don't know how long each will take. If I exclude the sys.sleep then the exports are blank as the export is run before the query is complete. Is there a way of getting R to wait until the query is complete?

#setup
  #install.packages("stringr", dependencies=TRUE)
  require(stringr)
  library(RODBC)    

#odbc connection
  db <- odbcDriverConnect("dsn=DW Master;uid=username;pwd=password;")   

#sql to be run
  qstr <- "select top 10 * from prod"

#variable
  weeknum<-c('201401','201402','201403')

for (i in weeknum ) 
  {
  data <- sqlQuery(db, qstr, believeNRows = FALSE)
  Sys.sleep(10)
  filename<-paste("data_", str_trim(i), ".csv")
  filename
  write.csv(data, file = filename)
  }

score 0 · Answer 1 · edited May 23 '17 at 12:16

From this SO post, try adding the rows_at_time argument:

data <- sqlQuery(db, qstr, believeNRows = FALSE, rows_at_time = 1)

Alternatively you can break up the two processes:

# QUERIES TO DATA FRAMES
weeknum<-c('201401','201402','201403')
for (i in weeknum ) {
  data <- sqlQuery(db, qstr, believeNRows = FALSE, rows_at_time = 1)
  assign(paste("data",i,sep=""),data)   
}

# DATA FRAMES TO CSV FILES
dfList <- c('data201401','data201402','data201403')
for (n in dfList) {
  df<-get(n)
  filename<-paste(n, ".csv", sep="")
  write.csv(df, file = filename)
}

R dynamic sql query using RODBC and export to .csv

1 Answers1