Helper functions do not recognize pool connections

[TobiasTDV]

I have noticed that the hepler functions in helpers.R cannot handle a pool connection. However, I need a pool connection for my Shiny app. In my case, it's about the isSQLServer helper function: isSQLServer <- function(conn) { inherits(conn, "Microsoft SQL Server") } With a pool connection, the command runs to FALSE, although the connection is actually a Microsoft SQL Server connection.

The problem is that the pool connection is stored in a different format and the inherits function cannot grab the database type. A possible solution on my part would be to test for a pool connection in advance using an if condition:

isSQLServer <- function(conn) {
  if(inherits(conn, "Pool")) {grepl("Microsoft SQL Server", conn$objClass)} else {inherits(conn, "Microsoft SQL Server")}
}

This is merely a suggestion. There will probably be other solutions to recognise the connection type.

I noticed the problem with the Upsert function. The Upsert function checks all connection types. Since all checks for the pool connection return FALSE, the Upsert syntax for PostgreSQL is selected at the end. This is where the syntax error came from in my case. The problem is therefore not with the Upsert function directly, but with the isSQLServer check.

For your information: This is how I created the pool connection:

library(pool)
conn <- dbPool(
    drv = odbc::odbc(),
    dbname = "db_name",
    dsn = "odbc_dsn",
    UID = "uid",
    PWD = "db_pw",
    minSize = 1,
    maxSize = 100,
    idleTimeout = 30000
  )

[ankane]

Hi @TobiasTDV, check out this section on pooling. I've considered allowing pools to be passed directly to dbx functions, but think it's typically better to checkout a connection once per request rather than with each database call.