3

Apologies if this has been answered elsewhere, but I could not find it. With the following code I am connecting to an MS SQL database using the RJDBC mechanism using pooling from the pool package:

library(RJDBC)
library(DBI)
library(pool)
library(dplyr)

drv <-
  JDBC(
    "com.microsoft.sqlserver.jdbc.SQLServerDriver",
    "C:/R/RJDBC/Microsoft JDBC Driver 6.0 for SQL Server/sqljdbc_6.0/enu/jre8/sqljdbc42.jar"
  )

pool_instance <- dbPool(
  drv = drv,
  dbname = "dbasename",
  url = "jdbc:sqlserver://sql01",
  user = "user",
  password = "password"
)

mydata <- dbGetQuery(pool_instance, "select * from my.Table")
src_pool(pool_instance) %>% tbl("my.Table") %>% head(5)

When I run this code, I make a successful connection to my SQL Server database and the dbGetQuery function call retrieves the data as expected.

However, when I call the src_pool function I get the following error message:

Error in UseMethod("tbl") : no applicable method for 'tbl' applied to an object of class "c('src_', 'src_sql', 'src')"

If I call the function src_pool(pool_instance) separately, without piping to the tbl function, the error message is similar:

Error in UseMethod("src_desc") : no applicable method for 'src_desc' applied to an object of class "c('src_', 'src_sql', 'src')"

I expected that either dplyr or pool would provide for these methods? Do I need to write code for these methods? What am I missing?

Note that I am a newby to SQL Server database connectivity.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Paul van Oppen
  • 1,443
  • 1
  • 9
  • 18
  • 1
    I just read an [R-bloggers](https://www.r-bloggers.com/accessing-mssql-server-with-r-rsqlserver-with-dplyr/) post that suggest that the schema table cannot have a dot (.). The real name of the table in the dbase schema contains a dot, just as my.Table does. – Paul van Oppen Mar 31 '17 at 03:03
  • Try something like this: `pool_instance %>% tbl(in_schema("my", "Table"))`. You might need to install and load the `tidyverse` and `dbplyr` packages as well. – Jake Fisher Sep 19 '17 at 13:57
  • Update from 2022, the issue is yet happening as commented by @Paul van Oppen, so when calling with pool and tbl, you have to use something liek this: `conn %>% tbl(sql("SELECT * FROM schema.table"))` – Corina Roca Dec 21 '22 at 14:48

0 Answers0