dplyr::tbl returning list instead of table

Question

I am trying to use dplyr in RStudio to manipulate tables in MS SQL Server database. I successfully connected to the database using DBI, ODBC.

Code:

library(DBI)
library(odbc)
library(dplyr)
library(dbplyr)
con <- dbConnect(odbc(), 
                 Driver = "SQL Server",
                 Server = "myserver",
                 database = "ABC",
                 UID = "sqladmin",
                 PWD = "pwd",
                 port = '14333')

data <- tbl(con, "abc")

abc is a table within database ABC. The connection is successful (I am able look at the tables and fields) but dplyr::tbl is returning a list of 2 instead of returning the table abc. So data is a list instead of table. Where am I going wrong in this code?

The schema is ABC --> dbo --> abc

Answer 1

The code works as expected. What you’re seeing is simply a limitation of the type display in the RStudio data inspector: the actual type returned by tbl is an object of S3 class tbl_SQLiteConnection but it is implemented as a nested list (similar to how data.frames are implemented as lists of columns).

You will be able to work with your data as expected. You can also invoke as_tibble(data) to get a proper tibble back … but you don’t need to do that to work with it!