2

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?

waanders
  • 8,907
  • 22
  • 70
  • 102
  • Sorry, I posted the first part to quick. Edited now – waanders Aug 17 '10 at 10:49
  • I haven't access to database for few days, so I cannot answer to comments [in your previous question](http://stackoverflow.com/questions/3440373/functions-and-try-in-r). – Marek Aug 17 '10 at 11:23
  • No problem, you answered my question for 95%, the remaining part is covered in this question. Thanks a lot – waanders Aug 17 '10 at 12:16

2 Answers2

5

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.

Marek
  • 49,472
  • 15
  • 99
  • 121
  • 1
    Thanks. So if I add the `errors=FALSE` part and deletes the warning function part I can detect errors by checking for the NA value and warnings by checking for a -1 value. It works, but I'd rather seen one approach for handling errors and warning but I think it isn't possible – waanders Aug 17 '10 at 12:21
  • 1
    You could leave warning part in case you got actual "R-warning". `-1` value gives information that something went wrong on C-level part of odbc connection. – Marek Aug 17 '10 at 12:53
1

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)
  }
}
Ben Engbers
  • 433
  • 3
  • 12