I'm running into a problem when mapping to a list of SQL table pointers using purrr
and dbplyr
. Essentially what I want to do is use a glue
character vector with all the table names I need, and then manipulate all the tables using purrr::map()
.
However there is a problem. I have no issue using dbplyr on this database with a single table, as shown in the example below. But for multiple tables I get the following error:
#> [[1]]
#> Error: <SQL> 'SELECT TOP 10 *
#> FROM "flights_1"'
#> nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid object name 'flights_1'.
So here is an example of what I am doing on Microsoft SQL Server 12.0.5203.0.
library(tidyverse)
library(glue)
library(odbc)
# Connection and data -----------------------------------------------------
# Establish connection
con <- dbConnect(odbc::odbc(), "MS_SQL_Server_12", Database = "my_Database",
UID = "my_Username", PWD = rstudioapi::askForPassword("Your password?"))
# Copy example data to connection
copy_to(con, nycflights13::flights, "flights_1",
temporary = FALSE,
indexes = list(
c("year", "month", "day"),
"carrier",
"tailnum",
"dest"
)
)
copy_to(con, nycflights13::flights, "flights_2",
temporary = FALSE,
indexes = list(
c("year", "month", "day"),
"carrier",
"tailnum",
"dest"
)
)
# The actual stuff --------------------------------------------------------
# Single table works dandy
single_table_pointer <- tbl(con, "flights_1")
# But all of the sudden multiple tables doesn't work
table_names <- glue("flights_{1:2}")
table_list <- table_names %>%
map(., .f = ~tbl(con, .x))
table_list
#> [[1]]
#> Error: <SQL> 'SELECT TOP 10 *
#> FROM "flights_1"'
#> nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid object name 'flights_1'.
This is something that has worked in the past for me (thus I'm wondering if it has something to do with packages issues arising from updating to 3.5), and works using toy examples where the connection "con" is for instance.
con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
I know this is a bit of a stretch, but if anyone here has experienced something similiar I'd love to hear from you.