R: JBDC result query using a loop

Question

I am trying to run several queries with one chunk of code to save time. Normally, I enter the variable and time, and return the results one at a time. However, there should be a way to use a loop that I iterate several variables through with one line, and collect the results in a data.frame with embedded code. However, I keept getting met with error "Error in dbSendQuery(con, sql) : Unable to retrieve JDBC result set..."

#enter sensor
sensor <- a
#enter start
t_start <- c("2022-07-29 04:54:00.0")
#enter end
t_end <-c("2022-08-01 23:59:59.0")
#open con
con <- openConn()

s <- tbl(con, in_schema("doc","sensors"))
consol_final <- s %>%
  filter(timeStamp >= t_start & t_end >= timeStamp,
          topic %in% sensor) %>%
  select("timeStamp", "ID", "running", "value") %>% 
  collect() %>%
  arrange(timeStamp)
closeConn

There must be a way to do this much faster than manually changing this using iteration. Here is my attempt that won't work no matter how much I tinker with it:

solution <- list()
sensor_list <- c(a,b,c,d,e,f) #note each of these is an object containing a topic URL
s <- tbl(con, in_schema("doc","sensors"))
con <- openConn()
for (i in 1:length(sensor_list)){
  consol_final <- s %>%
    filter(timeStamp >= t_start & t_end >= timeStamp,
          topic %in% sensor_list[i]) %>%
    select("timeStamp", "ID", "running", "value") %>%
    collect() %>%
    arrange(timeStamp)
  rbind(consol_final, solution)
}

I am very limited with my understanding of JBDC queries so it is difficult to wrap my head around the problem here. There also must be a solution to iterate through several time periods stored in a list as well. If possible, please advise on this as well. Thank you!!

R: JBDC result query using a loop

0 Answers