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
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!
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:
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.
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.
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))
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With