1

I am trying to use a pool to connect to my database in R, but I get the error:

Schema must be specified when session schema is not set

How does one specify a schema ? It seems like I need to specify it inside the pool. If that's the case, what's the parameter name for a schema?

pool <- dbPool(
  drv = RJDBC::JDBC(
    "xxx",
    "dir_to_jar", "`"
  ),
  dbname = "db",
  schema = "schema" # this didn't work
  url = url,
  user = user,
  password = password,
  SSL = 'true'
)

pool %>% tbl("schema.table")
shsh
  • 684
  • 1
  • 7
  • 18
  • `dbplyr::in_schema`? – r2evans Dec 21 '21 at 19:17
  • I think this'll work if I know how to add a catalog name. Do you know how to add a catalog to ```in_schema```? – shsh Dec 21 '21 at 20:14
  • This may be related: https://stackoverflow.com/q/58289494/3358272. – r2evans Dec 21 '21 at 20:23
  • Unless you are able to do this with `DBI`/`RJDBC` (no pool), then I suggest this warrants the [tag:dbi] (and/or [tag:r-dbi]) tag. If this behavior persists with `DBI::dbConnect`, then I think it might be preferred to test with that vice confusing things with connection-pools. If you have the ability to do this with the `odbc` package (instead of `RJDBC`), then that tag is not needed ... but I'm guessing you're using `RJDBC` because of your specific DBMS. What is the DBMS? – r2evans Dec 21 '21 at 20:24
  • I am using Starburst. I'm not sure if this is a DBMS. – shsh Dec 21 '21 at 20:40

1 Answers1

2

I tried several other methods using DBI::dbConnect combined with Id and it worked:

pool <- DBI::dbConnect(
  drv = RJDBC::JDBC(
    "xxx",
    "dir_to_jar", "`"
  ),
  url = url,
  user = user,
  password = password,
  SSL = 'true'
)

# Didn't work
pool %>% tbl(dbplyr::in_schema("catalog.schema", "table"))

# Works!
s <- Id(catalog = "catalog", schema = "schema", table = "table")
df <- dbReadTable(pool, s)
shsh
  • 684
  • 1
  • 7
  • 18