DBI::dbExistsTable

[wibeasley]

Issue Description and Expected Result

The dbExistsTable() isn't working correctly for me when the table's schema isn't the default.

The dbExistsTable() functions finds the table when I don't specify a schema, but not when I do. In the long term-I'm afraid the wrong table (a 2nd table in a different schema) would be manipulated.

Database

SQL Server 2016 & 2008

Reproducible Example

library(magrittr)
table_id <- DBI::Id(
  schema  = "schema_1",
  name    = "car"
)

ds <- mtcars %>% 
  tibble::rownames_to_column("car")

# Create the Table
channel <- DBI::dbConnect(
  drv   = odbc::odbc(),
  dsn   = "cdw_cache"
)
result <- DBI::dbWriteTable(
  conn        = channel,
  name        = table_id,
  value       = ds,
  overwrite   = T,
  append      = F
)

DBI::dbGetQuery(channel, "SELECT COUNT(*) FROM schema_1.car")
# Produces `1 32`

# Everything is expected until this line.

DBI::dbExistsTable(channel, table_id)
#Produces: Error in name@name[["table"]] : subscript out of bounds

DBI::dbExistsTable(channel, "schema_1.car")
# Produces: [1] FALSE

DBI::dbExistsTable(channel, "car")
# Produces: [1] TRUE

DBI::dbDisconnect(channel)

Session Info

Session info ----------------------------------------------------------------------------
 setting  value                                      
 version  R version 3.5.0 Patched (2018-05-03 r74699)
 system   x86_64, mingw32                            
 ui       RStudio (1.1.446)                          
 language (EN)                                       
 collate  English_United States.1252                 
 tz       America/Chicago                            
 date     2018-06-11                                 

Packages --------------------------------------------------------------------------------
 package    * version    date       source                          
 assertthat   0.2.0      2017-04-11 CRAN (R 3.5.0)                  
 backports    1.1.2      2017-12-13 CRAN (R 3.5.0)                  
 base       * 3.5.0      2018-05-07 local                           
 bindr        0.1.1      2018-03-13 CRAN (R 3.5.0)                  
 bindrcpp   * 0.2.2      2018-03-29 CRAN (R 3.5.0)                  
 bit          1.1-14     2018-05-29 CRAN (R 3.5.0)                  
 bit64        0.9-7      2017-05-08 CRAN (R 3.5.0)                  
 blob         1.1.1      2018-03-25 CRAN (R 3.5.0)                  
 checkmate    1.8.6      2018-05-12 Github (mllg/checkmate@f161dc3) 
 compiler     3.5.0      2018-05-07 local                           
 config       0.3        2018-03-27 CRAN (R 3.5.0)                  
 datasets   * 3.5.0      2018-05-07 local                           
 DBI          1.0.0      2018-05-02 CRAN (R 3.5.0)                  
 devtools     1.13.5     2018-02-18 CRAN (R 3.5.0)                  
 digest       0.6.15     2018-01-28 CRAN (R 3.5.0)                  
 dplyr        0.7.5      2018-05-19 CRAN (R 3.5.0)                  
 glue         1.2.0      2017-10-29 CRAN (R 3.5.0)                  
 graphics   * 3.5.0      2018-05-07 local                           
 grDevices  * 3.5.0      2018-05-07 local                           
 hms          0.4.2.9000 2018-06-11 Github (tidyverse/hms@14e74ab)  
 magrittr   * 1.5        2014-11-22 CRAN (R 3.5.0)                  
 memoise      1.1.0      2017-04-21 CRAN (R 3.5.0)                  
 methods    * 3.5.0      2018-05-07 local                           
 odbc         1.1.6      2018-06-09 CRAN (R 3.5.0)                  
 OuhscMunge   0.1.9.9007 2018-06-11 local                           
 pillar       1.2.3      2018-05-25 CRAN (R 3.5.0)                  
 pkgconfig    2.0.1      2017-03-21 CRAN (R 3.5.0)                  
 purrr        0.2.5      2018-05-29 CRAN (R 3.5.0)                  
 R6           2.2.2      2017-06-17 CRAN (R 3.5.0)                  
 Rcpp         0.12.17    2018-05-18 CRAN (R 3.5.0)                  
 readr        1.2.0      2018-06-11 Github (tidyverse/readr@c662432)
 rlang        0.2.1      2018-05-30 CRAN (R 3.5.0)                  
 RODBC        1.3-15     2017-04-13 CRAN (R 3.5.0)                  
 stats      * 3.5.0      2018-05-07 local                           
 tibble       1.4.2      2018-01-22 CRAN (R 3.5.0)                  
 tidyr        0.8.1      2018-05-18 CRAN (R 3.5.0)                  
 tidyselect   0.2.4      2018-02-26 CRAN (R 3.5.0)                  
 tools        3.5.0      2018-05-07 local                           
 utils      * 3.5.0      2018-05-07 local                           
 withr        2.1.2      2018-03-15 CRAN (R 3.5.0)                  
 yaml         2.1.19     2018-05-01 CRAN (R 3.5.0)    

edit: removed some intermediate debugging code.

[danielwo]

I had similar issues. This particular error is because table_id needs to be
table_id <- DBI::Id( schema = "schema_1", table = "car" )
That is name needs to be replaced with table, because dbExistsTable specifically calls
name@name[["table"]].

[wibeasley]

1. thanks @danielwo, that fixes my main concern, which was the error thrown by DBI::dbExistsTable(channel, table_id). I didn't realize there is some wiggle room in the table parameter name of DBI:Id(). I was following examples like dbWriteTable failing to write with schema on SQL Server DBI#229, which uses 'name' instead of 'table..

2. The unqualified call (i.e., DBI::dbExistsTable(channel, "car")) still returns TRUE, even though there's no table with that name in the default schema (i.e., 'dbo').

Is that the desired behavior of DBI::dbExistsTable? If so, I'm happy to add warnings in the documentation. I'm still worried that calling code could manipulate/drop/recreate the wrong table due to schema confusion.

[andrew57jm]

I get an error for all cases except the unqualified call. i.e. dbExistsTable(con, "cars") returns TRUE even though there is no table "cars" in the default schema.

dbExistsTable(con,"vehicles.cars") returns FALSE even though cars is present in schema vehicles.

Finally, dbExistsTable(con, DBI::Id(schema="vehicles", table="cars")) , which should work per the above comments returns:
Error in connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in% : nanodbc/nanodbc.cpp:4266: 24000: [Microsoft][ODBC Driver 17 for

Should the call using DBI::Id work?

[danielwo]

Sorry, I haven't been a very good github neighbor. I had ran across this thread while banging my head against the wall at work trying to get things to work and gave my solution. I'm not actually associated in anyway with the R package, so I haven't really kept up or responded.
This issue with the catalog is actually actually another problem I encountered. I hesitate to give my solution, since I essentially created my own frankenstein package on top of odbc to allow for various things I wanted to do in Microsoft SQL Server (allowing table truncation, various types of appending and work specific table/server connection wrappers).

I can't really test it right now, but you could try putting additional fields into your DBI::Id(catalog = "my_database", ...) for the database you are using (I gather from
stackoverflow that the database and catalog are often synonymous and the dbExistsTable function uses catalog.

If that doesn't work, my solution was to actually to create my own method for my own specific purposes. For you, if you don't want to specify the catalog/database you could try the following (i haven't tested):
`setMethod(
"dbExistsTable", c("OdbcConnection", "Id"),
function(conn, name, ...) {
name@name[["table"]] %in% connection_sql_tables(conn@ptr,
schema_name = if ("schema" %in% names(name@name)) name@name[["schema"]] else "%",
table_name = if ("table" %in% names(name@name)) name@name[["table"]] else "%"
)
})``

or alternatively try

`setMethod(
"dbExistsTable", c("OdbcConnection", "Id"),
function(conn, name, ...) {
name@name[["table"]] %in% odbc::dbListTables(conn, schema_name=name@name[["schema"]], table_name=name@name[["table"]])
})``

dbListTables calls the underlying function connection_sql_tables which I don't really know the implementation of. However the documentation for dbListTables says that if you don't put any value in, it defaults to return ALL schemas/tables/catalogs etc, which is why you get the behavior returning TRUE.

dbExistsTable(con,"vehicles.cars") returns FALSE because the dbExistsTable method for character calls name %in% dbListTables(conn, ...) where dbListTables returns the table name so "vehicles.cars" won't be a table name returned by dbListTables as it includes the schema.

Best of luck!

(Sorry for some edits, I cleaned up some things and changed a couple of things).

Another edit: Apparently at issue 197 you can set catalog=NULL.

[andrew57jm]

by setting tbl_id <- DBI::Id(catalog = database_name, schema = schema_name, table = table_name) dbExistsTable(con, tbl_id) now works properly. For sql server I guess there are no explicit catalogs, so the database name suffices.
using this form for tbl_id I now get a different error for dbWriteTable(con, tbl_id, df, append = T)
Error: Can't unquote "database_name"."schema_name"."table_name" but at least this is progress. Thanks!

[jimhester]

As mentioned by above posters the arguments for Id() when used by odbc must be named catalog, schema, table.

The behavior of dbExistsTable() when you don't specify a schema is driver dependant, there is no real way for the odbc package to know what the default schema for a given driver is. So I don't think there is much we can do about that issue.

I verified that fixing the argument name fixes the original issue, so I will close this.