Why does dbListTables give a warning message when called via a function? (R DBI)

Question

I wrote a function using dbListTables from the DBI package, that throws a warning that I cannot understand. When I run the same code outside of a function, I don't get the warning message.

For info, the database used is Microsoft SQL Server.

Reproducible example

library(odbc)
library(DBI)

# dbListTables in a function: gives a warning message

dbListTablesTest <- function(dsn, userName, password){

  con <- dbConnect(
    odbc::odbc(),
    dsn      = dsn,
    UID      = userName,
    PWD      = password,
    Port     = 1433,
    encoding = "latin1"
  )

  availableTables <- dbListTables(con)
}

availableTables <- 
  dbListTablesTest(
    dsn = "myDsn"
    ,userName = myLogin
    ,password = myPassword
  )

# dbListTables not within a function works fine (no warnings)

con2 <- dbConnect(
  odbc::odbc(),
  dsn      = "myDsn",
  UID      = myLogin,
  PWD      = myPassword,
  Port     = 1433,
  encoding = "latin1"
)

availableTables <- dbListTables(con2)

(Incidentally, I realise I should use dbDisconnect to close a connection after working with it. But that seems to throw similar warnings. So for the sake of simplicity I've omitted dbDisconnect.)

The warning message

When executing the code above, I get the following warning message when using the first option (via a function), but I do not get it when using the second option (no function).

warning messages from top-level task callback '1'
Warning message:
Could not notify connection observer. trying to get slot "info" from an object of a basic class ("character") with no slots 

The warning is clearly caused by dbListTables, because it disappears when I omit that line from the above funtion.

My questions

• Why am I getting this warning message?
• More specifically why am I only getting it when calling dbListTables via a function?
• What am I doing wrong / should I do to avoid it?

My session info

R version 3.4.2 (2017-09-28)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

Matrix products: default

locale:
[1] LC_COLLATE=Dutch_Belgium.1252  LC_CTYPE=Dutch_Belgium.1252    LC_MONETARY=Dutch_Belgium.1252 LC_NUMERIC=C                   LC_TIME=Dutch_Belgium.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  tools     methods   base     

other attached packages:
[1] DBI_0.7    odbc_1.1.3

loaded via a namespace (and not attached):
[1] bit_1.1-12     compiler_3.4.2 hms_0.3        tibble_1.3.4   Rcpp_0.12.13   bit64_0.9-7    blob_1.1.0     rlang_0.1.2  

Thanks in advance for any help!

Hemicycle

I am getting the same warning using dbGetQuery, so this seems to be a wider issue. – Idolize

Answer 1

TL:DR calling odbc::dbConnect within another function causes this warning.

After a lot of digging in the odbc github, I have found the source of the warning. Calling dbConnect creates a db connection. Within this function is the following code:

# perform the connection notification at the top level, to ensure that it's had
# a chance to get its external pointer connected, and so we can capture the
# expression that created it
if (!is.null(getOption("connectionObserver"))) { # nocov start
  addTaskCallback(function(expr, ...) {
    tryCatch({
      if (is.call(expr) && identical(expr[[1]], as.symbol("<-"))) {
        # notify if this is an assignment we can replay
        on_connection_opened(eval(expr[[2]]), paste(
          c("library(odbc)", deparse(expr)), collapse = "\n"))
      }
    }, error = function(e) {
      warning("Could not notify connection observer. ", e$message, call. = FALSE)
    })

    # always return false so the task callback is run at most once
    FALSE
  })
} # nocov end

This warning call should look familiar. This is what generates the warning. So why does it do that?

The snippet above is trying to do some checking on the connection object, to see if everything went well.

How it does that, is by adding a function checking this to the 'TaskCallBack'. This is a list of functions that get executed after a top-level task is completed. I am not 100% sure on this, but from what I can tell, this means that these functions are executed after the highest function in the call stack finishes.

Normally, this would be a line in your script. So for example:

library(odbc)
con <- odbc::dbConnect(odbc::odbc(), ...)

After the assignment in the second line is finished, the following function is executed:

function(expr, ...) {
    tryCatch({
      if (is.call(expr) && identical(expr[[1]], as.symbol("<-"))) {
        # notify if this is an assignment we can replay
        on_connection_opened(eval(expr[[2]]), paste(
          c("library(odbc)", deparse(expr)), collapse = "\n"))
      }
    }, error = function(e) {
      warning("Could not notify connection observer. ", e$message, call. = FALSE)
    }
}

The top-level expression gets passed to the function and used to check if the connection works. Another odbc function called on_connection_opened then does some checks. If this throws an error anywhere, the warning is given, because of the tryCatch.

So why would the function on_connection_opened crash?

The function takes the following arguments:

on_connection_opened <- function(connection, code)

and one of the first things it does is:

display_name <- connection@info$dbname

Which seems to match the warning message:

trying to get slot "info" from an object of a basic class ("character") with no slots

From the name of the argument, it is clear that the function on_connection_opened expects a database connection object in its first argument. What does it get from its caller? eval(expr[[2]])

This is the lefthand side of the original call: con

In this case, this is a connection object and everything is nice.

Now we have enough information to answer your questions:

Why am I getting this warning message?

Your function creates a connection, which queues up the check connection function. If then checks for a list of tables and returns that. The check connection function then interprets the list of tables as if it is a connection, tries to check it, and fails miserably. This throws the warning.

More specifically why am I only getting it when calling dbListTables via a function?

dbListTables is not the culprit, dbConnect is. Because you are calling it from within a function, it doesn't get the connection object it is trying to check back and fails.

What am I doing wrong / should I do to avoid it?

A workaround would be to open a connection separately and pass that into your function. This way the connection gets opened in its own call, so the check works properly.

Alternatively, you can remove the TaskCallback again:

  before <- getTaskCallbackNames()
  con <- odbc::dbConnect(odbc::odbc(), ...)
  after <- getTaskCallbackNames()
  removeTaskCallback(which(!after %in% before))

Is the running is on_connection_opened essential? What does it do exactly?

As explained by the package's creator in this comment on Github, the function handles the displaying of the connection in the connections tab in RStudio. This is not that interesting to look at if you close the connection in the same function again. So this is not essential for your function.

Idolize

Thanks for figuring this out! If I understand correctly though, this isn't really an error on my part, is that correct? The addTaskCallBack function is somehow using the wrong expression for on_connection_opened? If this is indeed correct, do you have any idea if this will be fixed in the near future? – Hemicycle

@Hemicycle Correct. I don't know enough about the inner workings of the package to judge whether the call to that function is essential or not. I might file a bug report for it. But for the time being, opening the connection and passing it to your function should do everything correctly. – Idolize