can't copy_to schema other than Dflt.

[halpo]

I am using dbplyr and odbc to connect to a "Microsoft SQL Server" database running Microsoft SQL Server Enterprise version 12.0.5556.0. When I run

# db previously defined as an odbc/Microsoft SQL Server connection
service.tbl <- c('A' = 'Army', 'N' = 'Navy', 'M' = 'Marine Corps', 'F' = 'Air Force') %>%
               data_frame(ABBR = names(.), SERVICE = .)
service <- copy_to(db, service.tbl, in_schema('MAP', 'SERVICE'), temporary=FALSE)

I get the error:

Error: <SQL> 'SELECT *
FROM MAP.SERVICE as 'zzz1'
WHERE (0 = 1)'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name 'SERVICE'.

However the table was created just in the wrong place.

DBI::dbListTables(DB) %>% grep('SERVICE', ., value=TRUE)

produces

[1] "MAP.SERVICE"

Also

tbl(db, in_schema('MAP', 'SERVICE'))

fails. Whereas,

tbl(db, in_schema('Dflt', '[MAP.SERVICE]'))

does not and gives the correct contents of the table.

I traced this down to the DBI::dbCreateTable,OdbcConnection,character,data.frame-method which in turn calls the dbQuoteIdentifier method. at this point the in_schema('MAP', 'SERVICE') is evaluated as the character string MAP.SERVICE with the classes c('ident_q', 'ident', 'character')and the ident classes are disregarded and the table name becomes '"MAP.SERVICE"' with the double quotes which is the wrong thing to do. This could be remedied by defining a method fordbQuoteIdentifier`.

setMethod(dbQuoteIdentifier, list(conn = 'Microsoft SQL Server', x= 'ident'),
function(conn, x, ...){
    return(DBI::SQL(x))
}

This fixes the problem for me. There might be more issues related to it but there does need to be some more consideration for database schemas.

NOTE: I apologize for not having a reproducible example that can be run directly, but the database I work with is behind several layers of firewall.

[jimhester]

I think this is a bug in dplyr / dbplyr rather than odbc (see tidyverse/dplyr#3013 for example). Currently the only way to handle schemas in odbc is to quote them yourself with DBI::SQL("foo.bar"), or you can install the SQLTable branch and use dbId(), see #91 (comment) for example usage.

This really is mostly a DBI issue, as there are currently no functions in DBI to handle quoting of schemas properly (r-dbi/DBI#24).

[jimhester]

Should be fixed by 536ee60