Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

dbReadTable with Id schema and table #120

Closed
sclewis23 opened this issue Jan 22, 2021 · 1 comment · Fixed by #146
Closed

dbReadTable with Id schema and table #120

sclewis23 opened this issue Jan 22, 2021 · 1 comment · Fixed by #146
Labels
feature a feature request or enhancement

Comments

@sclewis23
Copy link

I'm getting an error when I try to use the pool connection to use the DBI:Id() to select schema and table.
Error in (function (classes, fdef, mtable) : unable to find an inherited method for function 'dbReadTable' for signature '"Pool", "Id"'
This works with DBI, is this just not currently supported with Pool ?

`

library(DBI)
library(magrittr)
library(pool)

# Pool Connection
dbPoolConn <- pool::dbPool(
  odbc::odbc(), 
  Driver = secret::get_secret("db_Pool_CA", key = secret::local_key(), vault = file.path("/usrfiles/shared-projects/shannon/ProgramEnrollment/.vault"))$Driver,
  Server = secret::get_secret("db_Pool_CA", key = secret::local_key(), vault = file.path("/usrfiles/shared-projects/shannon/ProgramEnrollment/.vault"))$Server,
  Port = secret::get_secret("db_Pool_CA", key = secret::local_key(), vault = file.path("/usrfiles/shared-projects/shannon/ProgramEnrollment/.vault"))$Port,
  Database = secret::get_secret("db_Pool_CA", key = secret::local_key(), vault = file.path("/usrfiles/shared-projects/shannon/ProgramEnrollment/.vault"))$Database,
  UID = secret::get_secret("db_Pool_CA", key = secret::local_key(), vault = file.path("/usrfiles/shared-projects/shannon/ProgramEnrollment/.vault"))$UID,
  PWD = secret::get_secret("db_Pool_CA", key = secret::local_key(), vault = file.path("/usrfiles/shared-projects/shannon/ProgramEnrollment/.vault"))$PWD,
  bigint = "integer64"
)

# DBI connnection
dbDBIConn <- DBI::dbConnect(
  odbc::odbc(), 
  Driver = secret::get_secret("db_Pool_CA", key = secret::local_key(), vault = file.path("/usrfiles/shared-projects/shannon/ProgramEnrollment/.vault"))$Driver,
  Server = secret::get_secret("db_Pool_CA", key = secret::local_key(), vault = file.path("/usrfiles/shared-projects/shannon/ProgramEnrollment/.vault"))$Server,
  Port = secret::get_secret("db_Pool_CA", key = secret::local_key(), vault = file.path("/usrfiles/shared-projects/shannon/ProgramEnrollment/.vault"))$Port,
  Database = secret::get_secret("db_Pool_CA", key = secret::local_key(), vault = file.path("/usrfiles/shared-projects/shannon/ProgramEnrollment/.vault"))$Database,
  UID = secret::get_secret("db_Pool_CA", key = secret::local_key(), vault = file.path("/usrfiles/shared-projects/shannon/ProgramEnrollment/.vault"))$UID,
  PWD = secret::get_secret("db_Pool_CA", key = secret::local_key(), vault = file.path("/usrfiles/shared-projects/shannon/ProgramEnrollment/.vault"))$PWD,
  bigint = "integer64"
)


# schema
dbS <- "dbo"
# Country Month Data Table
dbCM_tb <- "CNTRY_MON_DATA_2"

# DBI Connection
DBI::dbReadTable(conn = dbDBIConn, name = DBI::Id(schema = dbS,  table = dbCM_tb)) %>%
  dplyr::slice(1)
#>   CNTRY_MON_KEY CNTRY_ID    STUDY_KEY YEAR_KEY MON_KEY SUBJ_ENROL_ACT
#> 1   -1874919421       36 TEST_STUDY_1     2020      01              3
#>   SUBJ_ENROL_PLAN SUBJ_BASELINE_ORG SUBJ_BASELINE_CUR SUBJ_SCREENED
#> 1               9                14                19             7
#>   SUBJ_SCREENED_FAIL SITES_ACTIVE_ACT SITES_ACTIVE_PLAN SITES_BASELINE_ORG
#> 1                  4                2                 1                  6
#>   SITES_BASELINE_CUR INC_IN_CHARTS          CREATED_DT    CREATED_BY
#> 1                 11          TRUE 2021-01-15 09:09:09 shannon.lewis
#>            UPDATED_DT     UPDATED_BY HASH_VALUE
#> 1 2021-01-19 07:31:34 michael.blanks blob[64 B]

# Pool Connection
DBI::dbReadTable(conn = dbPoolConn, name = DBI::Id(schema = dbS,  table = dbCM_tb)) %>%
  dplyr::slice(1)
#> Error in (function (classes, fdef, mtable) : unable to find an inherited method for function 'dbReadTable' for signature '"Pool", "Id"'

Created on 2021-01-22 by the reprex package (v0.3.0)

`
@hadley hadley added the feature a feature request or enhancement label Jan 5, 2023
@hadley
Copy link
Member

hadley commented Jan 5, 2023

Minimal reprex:

library(pool)

pool <- dbPool(RSQLite::SQLite())
DBI::dbWriteTable(pool, DBI::Id(table = "mtcars"), mtcars)
DBI::dbReadTable(pool, DBI::Id(table = "mtcars"))
#> Error in (function (classes, fdef, mtable) : unable to find an inherited method for function 'dbReadTable' for signature '"Pool", "Id"'

Created on 2023-01-05 with reprex v2.0.2

Just need to add the missing method (and check for other methods that might be absent).

hadley added a commit that referenced this issue Jan 6, 2023
@hadley
Only dispatch on first argument
f5fd688 
I think this is correct because Pool doesn't involve itself in any of the arugments; it just checks out the object then re-dispatches. This is potentially a breaking change because it could introduce new method dispatch ambiguities, but I don't think that's a concern here because Pool has neither superclasses or subclasses.

Fixes #120
@hadley hadley mentioned this issue Jan 6, 2023
Merged
hadley added a commit that referenced this issue Jan 28, 2023
@hadley
Overhaul DBI method forwarding (#146)
f8c85ec 
* All wrappers are generated with code, ensuring that each method has exactly the same syntax (see the snapshot test for what the generated methods look like). I've also rearranged the method registration to put all of the wrappers in one place and all of the customised methods in another. Along with a few other minor tweaks, this makes it possible to remove all the `@include` directives and hence the `Collate` field in the `DESCRIPTION`.

* Methods are now only ever registered for the first argument. I think this is correct because Pool doesn't involve itself in any of the arugments; it just checks out the object then re-dispatches. This is potentially a breaking change because it could introduce new method dispatch ambiguities, but I don't think that's a concern here because Pool has neither superclasses or subclasses. This fixes #120.

* Restore (and correct) test that ensures that pool provides methods for the correct DBI generics. That showed that we needed a `dbDisconnect` method, and suggested a number of methods that could be dropped since the generics have been deprecated.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
feature a feature request or enhancement
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Standardise DBI method forwarding rstudio/pool
2 participants
@hadley @sclewis23