How do I catch error in tbl in dplyr while fetching data from the database?

Question

Sometimes I run into an issue where the database query generates error. One example is:

nanodbc/nanodbc.cpp:3069: 07009: [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index

I know why the error occurs but I can't seem to catch the error to try something else when something like this happens.

result <- tryCatch(
      data <- tbl(conn, query),
      
      error = function(e){
        
        print("Error encountered: ", e)
        print("Attempting to run by sorting the columns")
        
        
        new_query <- create_query_with_column_names(query)
        
        print("Attempting to fetch the data with the new query")
        data <- tbl(conn, new_query)
        
        end_time <- Sys.time()
        
        
        show_query_runtime(total_time=end_time-start_time, caller="fetch data without lazy loading.")
        
      }
    )

But instead, the code runs without error, but when I run the result, I get the error again.

> result

Error in result_fetch(res@ptr, n) : 
  nanodbc/nanodbc.cpp:3069: 07009: [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index 
Warning message:
In dbClearResult(res) : Result already cleared

The above code won't catch the error. Why? How can I fix this?

Answer 1

Take a look at this answer for detailed guidance on tryCatch in R.

The problem is most likely how you are returning values.

• If it executes correctly, the Try part returns the last statement in the section.
• If the Try does not execute correctly, then the error section will return the last statement in the section.

Right now, the last statement in your error section is show_query_runtime(...) but what it looks like you want is tbl(conn, new_query).

Try the following, note the use of return to specify the value that should be returned:

result <- tryCatch(
  # try section
  data <- tbl(conn, query),

  # error section
  error = function(e){
    print("Error encountered: ", e)
    print("Attempting to run by sorting the columns")
    new_query <- create_query_with_column_names(query)

    print("Attempting to fetch the data with the new query")
    data <- tbl(conn, new_query)
    
    end_time <- Sys.time()
    show_query_runtime(total_time=end_time-start_time, caller="fetch data without lazy loading.")

    return(data)
  }
)

In case it is part of the confusion, assigning data <- tbl(conn, new_query) within a function does not make the assignment in the calling environment. The variable data is not available once the tryCatch finishes. This is why we need to return the result out of the calling function.