RODBC functions and errors/warnings

问题:

A question about this R code:

library(RODBC)

ch <- tryCatch(odbcConnect("RTEST"),
  warning=function(w){print("FAIL! (warning)");return(NA)},
  error=function(e){print(paste("ERROR:",geterrmessage()));return(NA)})

df <- tryCatch(sqlQuery(ch,"SELECT Test from tblTest"),
  warning=function(w){print("FAIL! (warning)");return(NA)},
  error=function(e){print(paste("ERROR:",geterrmessage()));return(NA)})

odbcClose(ch)

Code works fine for errors (forced by omitting the required paramaters in the code) in both cases (warning- and error part are almost exactly the same): I get a NA value and an errormessage.

Also for an error with sqlQuery (give an invalid DSN): NA value and an errormessage.

But not for warnings with sqlQuery. No message output, but df contains the message (so no NA). Why?

回答1:

I checked code for sqlQuery and found this:

stat <- odbcQuery(channel, query, rows_at_time)
if (stat == -1L) {
    if (errors) 
        return(odbcGetErrMsg(channel))
    else return(invisible(stat))
}

error is parameter to sqlQuery, on default TRUE, so it gives you character vector without error or warning. If you change it to sqlQuery(ch,"SELECT Test from tblTest",FALSE) then df will contain -1 value. This is error code from C-level, but not error in R meaning so tryCatch could not handle it.

I suppose that you need to check if df==-1 after tryCatch.

回答2:

I ended up with this code. Now I can handle the specific Mysql error_code:

saveText <- function(query, channel, errors = TRUE) {
  stat <- odbcQuery(channel, query)
  if (stat == -1L) {
    if (errors)
      err <- odbcGetErrMsg(channel)
    else {
      err <- invisible(stat)
    }
    return(err)
  }
}