Changes in dbQuoteString cause incompatibility with filter() in dplyr #218
Description
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. .