RODBC error handling for sqlQuery

Question

I did not find any good error testing function for the results of an sqlQuery and that surprises me.

In the documentation (http://www.inside-r.org/packages/cran/rodbc/docs/sqlQuery), they state that its return values are:

"On success, a data frame (possibly with 0 rows) or character string. On error, if errors = TRUE a character vector of error message(s), otherwise an invisible integer error code -1 (general, call odbcGetErrMsg for details) or -2 (no data, which may not be an error as some SQL statements do return no data)."

I never ever could replicate a -1 or a -2 being returned (with errors = false, by default). How is it possible?

If write a SELECT request with an invalid table, I get a string with the error message from SQL Server, but no -1L nor -2L. When do they occur?

In the same spirit, testing for SQL errors or empty result sets includes a cascade of smaller tests to avoid errors when testing.

I finally used the following if..else..else code, but I wonder if it's possible to simplify it (and why I never see -1 nor -2). That leaves me a bad feeling of not covering all the cases...

sql.invalid.table.name <-
 "SELECT pfiID
  FROM test"

sql.0.rows <-
 "SELECT pfiID
  FROM dossier
  WHERE pfiID = 'fake-id'"

sql.10.rows <-
 "SELECT pfiID
  FROM dossier
  WHERE pfiID like '%/200201/0001'"

records <- sqlQuery(channel, sql.invalid.table.name)

if (is.null(nrow(records))) {
  stop(paste("", records, sep="\n"))
} else if (nrow(records) == 0) {
  cat("No data\n")
} else
  print(head(records))

records <- sqlQuery(channel, sql.0.rows)

if (is.null(nrow(records))) {
  stop(paste("", records, sep="\n"))
} else if (nrow(records) == 0) {
  cat("No data\n")
} else
  print(head(records))

records <- sqlQuery(channel, sql.10.rows)

if (is.null(nrow(records))) {
  stop(paste("", records, sep="\n"))
} else if (nrow(records) == 0) {
  cat("No data\n")
} else
  print(head(records))

Any ideas/comments?

Answer 1

Yes, the situation seems horrendous, but in practise it isn't as bad as it might be. The documentation is vague and convoluted because RODBC handles connections to any sort of database and different databases return different things in the event of problems.

I've done a fair amount of work using ROBDC to connect R to SQL Server, and in general SELECT queries were easy enough to deal with: they return a data frame is they work, and a character vector if not. So code like

records <- sqlQuery(channel, "SELECT blah FROM somewhere")
if(is.character(records))
{
  stop(paste(records, collapse = "\n"))
}

works well enough.

For absolute certainty, you'll need to be more thorough, but if you are connecting to only one type of database, it will hopefully be consistent about how it fails.