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.

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

Changes in dbQuoteString cause incompatibility with filter() in dplyr #218

Closed
ckarras opened this issue Jan 17, 2018 · 1 comment
Closed

Comments

@ckarras
Copy link

ckarras commented Jan 17, 2018

I'm trying to build a dynamic SQL query using dplyr (latest dev version installed with devtools::install_github) and filter on a DBIConnection.

I get the following error:

    Error in getMethod("dbQuoteString", c("DBIConnection", "character"), asNamespace("DBI")) : 
    no method found for function 'dbQuoteString' and signature DBIConnection, character

After investigation, I found that the dbQuoteString methods have recently been modified in DBI, and
setMethod("dbQuoteString", c("DBIConnection", "character") has been removed:
6c111b7#diff-6d14eaf8cb28fc7edffaeb9d1337c374

I can workaround the issue by downgrading DBI to v0.7-12 (instead of 0.7-13):
devtools::install_github("rstats-db/DBI@v0.7-12")

library(DBI)
library(plyr)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:plyr':
#> 
#>     arrange, count, desc, failwith, id, mutate, rename, summarise,
#>     summarize
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(dbplyr)
#> 
#> Attaching package: 'dbplyr'
#> The following objects are masked from 'package:dplyr':
#> 
#>     ident, sql

sqlconn <- DBI::dbConnect(odbc::odbc(), Driver = "{SQL Server Native Client 11.0}", Server = "...", Database = "...", UID = "...", PWD = "...", Port = 1433)

sqlQuery <- "SELECT * FROM sys.tables"
tblQuery <- tbl(sqlconn, sql(sqlQuery))

queryResults <- tblQuery %>% 
  filter(name %in% c("Job","Schedule")) %>% 
  collect()

With version v0.7-13 of DBI, the following methods are available:

showMethods("dbQuoteString")
#> Function: dbQuoteString (package DBI)
#> conn="DBIConnection"

By downgrading to version v0.7-12, the following methods are available (which solves the error):

showMethods("dbQuoteString")
#> Function: dbQuoteString (package DBI)
#> conn="DBIConnection", x="character"
#> conn="DBIConnection", x="SQL"

Note: I originally reported this issue in dplyr (tidyverse/dplyr#3303), but after discussion it seems it's an unintended breaking change in DBI. .

@krlmlr
Copy link
Member

krlmlr commented Jan 25, 2018

Thanks for raising this. Now all three signatures are available. I hope this resolves the original problem, I haven't checked.

krlmlr added a commit that referenced this issue Jan 27, 2018
- The `SQL()` function gains a `names` argument which can be used to assign names to SQL strings.
- `dbListResults()` is deprecated by documentation (#58).
- `dbGetException()` is soft-deprecated by documentation (#51).
- Help pages for generics now contain a dynamic list of methods implemented by DBI backends (#162).
- `sqlInterpolate()` now supports both named and positional variables (#216, @hannesmuehleisen).
- Specialized methods for `dbQuoteString()` and `dbQuoteIdentifier()` are available again, for compatibility with clients that use `getMethod()` to access them (#218).
krlmlr added a commit that referenced this issue Mar 9, 2018
…ps the names from the output if the `names` argument is unset. - The `dbReadTable()`, `dbWriteTable()`, `dbExistsTable()`, `dbRemoveTable()`, and `dbListFields()` generics now specialize over the first two arguments to support implementations with the `Id` S4 class as type for the second argument. Some packages may need to update their documentation to satisfy R CMD check again. New generics ------------ - Schema support: Export `Id()`, new generics `dbListObjects()` and `dbUnquoteIdentifier()`, methods for `Id` that call `dbQuoteIdentifier()` and then forward (#220). - New `dbQuoteLiteral()` generic. The default implementation uses switchpatch to avoid dispatch ambiguities, and forwards to `dbQuoteString()` for character vectors. Backends may override methods that also dispatch on the second argument, but in this case also an override for the `"SQL"` class is necessary (#172). Default implementations ----------------------- - Default implementations of `dbQuoteIdentifier()` and `dbQuoteLiteral()` preserve names, default implementation of `dbQuoteString()` strips names (#173). - Specialized methods for `dbQuoteString()` and `dbQuoteIdentifier()` are available again, for compatibility with clients that use `getMethod()` to access them (#218). - Add default implementation of `dbListFields()`. - The default implementation of `dbReadTable()` now has `row.names = FALSE` as default and also supports `row.names = NULL` (#186). API changes ----------- - The `SQL()` function gains an optional `names` argument which can be used to assign names to SQL strings. Deprecated generics ------------------- - `dbListConnections()` is soft-deprecated by documentation. - `dbListResults()` is deprecated by documentation (#58). - `dbGetException()` is soft-deprecated by documentation (#51). - The deprecated `print.list.pairs()` has been removed. Bug fixes --------- - Fix `dbDataType()` for `AsIs` object (#198, @yutannihilation). - Fix `dbQuoteString()` and `dbQuoteIdentifier()` to ignore invalid UTF-8 strings (r-dbi/DBItest#156). Documentation ------------- - Help pages for generics now contain a dynamic list of methods implemented by DBI backends (#162). - `sqlInterpolate()` now supports both named and positional variables (#216, @hannesmuehleisen). - Point to db.rstudio.com (@wibeasley, #209). - Reflect new 'r-dbi' organization in `DESCRIPTION` (@wibeasley, #207). Internal -------- - Using switchpatch on the second argument for default implementations of `dbQuoteString()` and `dbQuoteIdentifier()`.
@github-actions github-actions bot locked and limited conversation to collaborators Oct 9, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants