Writing a package function that uses a dbplyr call to tbl()

Question

I have a simple function that should fetch a table using a DBI::dbConnect() connection. I am having trouble with the call to tbl() that works fine in an interactive session.

My function:

a2_db_read <- function(con, tbl_name, schema = "dbo"){
  if(schema == "dbo"){
    dplyr::tbl(con, tbl_name)
  }
  else{
    dplyr::tbl(con, dbplyr::in_schema(schema, tbl_name))
  }
}

If I make the call dplyr::tbl() I get:

Error in UseMethod("tbl") : 
  no applicable method for 'tbl' applied to an object of class "Microsoft SQL Server"

If I make the call dbplyr::tbl() I get:

a2_db_read(a2_con_uat, "AVL Data")
Error: 'tbl' is not an exported object from 'namespace:dbplyr'

How can I get that call to succeed in a function? My package Imports is:

Imports: 
    DBI,
    dbplyr,
    dplyr,
    ggplot2,
    odbc

Answer 1

I got it working with dplyr::tbl(), the correct usage.

The problem was that my connection was stored as an object in a package when in fact the connection needs to be made every time I restart R.

On a fresh R environment, the stored stale connection caused the error:

Error in UseMethod("tbl") : no applicable method for 'tbl' applied to an object of class "Microsoft SQL Server"

When I regenerated the connection, it worked.