odbc
odbc copied to clipboard
r-dbi
odbcListObjects functions improperly with MSSQL connections
Issue Description and Expected Result
odbcListObjects() doesn't return objects in the specified DB. Instead, it returns a list of all DBs in the connection, with the type of catalog:
library(DBI)
library(odbc)
simba_msql1 <- dbConnect(odbc(),
"SQLServer1",
uid = "rstudioadmin",
pwd = "ABCd4321",
database = "airontime")
simba_msql2 <- dbConnect(odbc(),
"SQLServer2",
uid = "rstudioadmin",
pwd = "ABCd4321",
database = "finance")
freetds_msql <- dbConnect(odbc(),
"SQLServer3",
uid = "rstudioadmin",
pwd = "ABCd4321",
database = "airontime")
dbGetInfo(simba_msql1)$driver.version
#> [1] "1.4.13.1013"
dbGetInfo(freetds_msql)$driver.version
#> [1] "01.00.0082"
odbcListObjects(simba_msql1)
#> name type
#> 1 airontime catalog
#> 2 finance catalog
#> 3 immunogenicity catalog
#> 4 master catalog
#> 5 msdb catalog
#> 6 rdsadmin catalog
#> 7 tempdb catalog
odbcListObjects(simba_msql2)
#> name type
#> 1 airontime catalog
#> 2 finance catalog
#> 3 immunogenicity catalog
#> 4 master catalog
#> 5 msdb catalog
#> 6 rdsadmin catalog
#> 7 tempdb catalog
odbcListObjects(freetds_msql)
#> name type
#> 1 airontime catalog
#> 2 finance catalog
#> 3 immunogenicity catalog
#> 4 master catalog
#> 5 msdb catalog
#> 6 rdsadmin catalog
#> 7 tempdb catalog
Created on 2020-02-26 by the reprex package (v0.3.0)
Instead, I would expect this to honor the selected database and return details about the schemas associated with that database. This behavior impacts the RStudio connections pane, which shows all DBs in the connection instead of the schemas in the selected DB:
Database
Microsoft SQL Server 12.00.5571
Session Info
> devtools::session_info()
─ Session info ─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
setting value
version R version 3.6.2 (2019-12-12)
os Ubuntu 18.04.4 LTS
system x86_64, linux-gnu
ui RStudio
language (EN)
collate C.UTF-8
ctype C.UTF-8
tz Etc/UTC
date 2020-02-26
─ Packages ─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
package * version date lib source
assertthat 0.2.1 2019-03-21 [1] RSPM (R 3.6.1)
backports 1.1.5 2019-10-02 [1] RSPM (R 3.6.1)
bit 1.1-15.2 2020-02-10 [1] RSPM (R 3.6.2)
bit64 0.9-7 2017-05-08 [1] RSPM (R 3.6.1)
blob 1.2.1 2020-01-20 [1] RSPM (R 3.6.2)
callr 3.4.2 2020-02-12 [1] RSPM (R 3.6.2)
cli 2.0.1 2020-01-08 [1] RSPM (R 3.6.2)
clipr 0.7.0 2019-07-23 [1] RSPM (R 3.6.1)
crayon 1.3.4 2017-09-16 [1] RSPM (R 3.6.2)
DBI * 1.1.0 2019-12-15 [1] RSPM (R 3.6.1)
desc 1.2.0 2018-05-01 [1] RSPM (R 3.6.1)
devtools 2.2.2 2020-02-17 [1] RSPM (R 3.6.2)
digest 0.6.25 2020-02-23 [1] RSPM (R 3.6.2)
ellipsis 0.3.0 2019-09-20 [1] RSPM (R 3.6.1)
evaluate 0.14 2019-05-28 [1] RSPM (R 3.6.1)
fansi 0.4.1 2020-01-08 [1] RSPM (R 3.6.2)
fs 1.3.1 2019-05-06 [1] RSPM (R 3.6.1)
glue 1.3.1 2019-03-12 [1] RSPM (R 3.6.1)
hms 0.5.3 2020-01-08 [1] RSPM (R 3.6.2)
htmltools 0.4.0 2019-10-04 [1] RSPM (R 3.6.1)
knitr 1.28 2020-02-06 [1] RSPM (R 3.6.2)
magrittr 1.5 2014-11-22 [1] RSPM (R 3.6.2)
memoise 1.1.0 2017-04-21 [1] RSPM (R 3.6.1)
odbc * 1.2.2 2020-01-10 [1] RSPM (R 3.6.2)
pkgbuild 1.0.6 2019-10-09 [1] RSPM (R 3.6.1)
pkgconfig 2.0.3 2019-09-22 [1] RSPM (R 3.6.1)
pkgload 1.0.2 2018-10-29 [1] RSPM (R 3.6.1)
prettyunits 1.1.1 2020-01-24 [1] RSPM (R 3.6.2)
processx 3.4.2 2020-02-09 [1] RSPM (R 3.6.2)
ps 1.3.2 2020-02-13 [1] RSPM (R 3.6.2)
R6 2.4.1 2019-11-12 [1] RSPM (R 3.6.1)
Rcpp 1.0.3 2019-11-08 [1] RSPM (R 3.6.1)
remotes 2.1.1 2020-02-15 [1] RSPM (R 3.6.2)
reprex 0.3.0 2019-05-16 [1] RSPM (R 3.6.1)
rlang 0.4.4 2020-01-28 [1] RSPM (R 3.6.2)
rmarkdown 2.1 2020-01-20 [1] RSPM (R 3.6.2)
rprojroot 1.3-2 2018-01-03 [1] RSPM (R 3.6.1)
rstudioapi 0.11 2020-02-07 [1] RSPM (R 3.6.2)
sessioninfo 1.1.1 2018-11-05 [1] RSPM (R 3.6.1)
testthat 2.3.1 2019-12-01 [1] RSPM (R 3.6.1)
usethis 1.5.1 2019-07-04 [1] RSPM (R 3.6.1)
vctrs 0.2.3 2020-02-20 [1] RSPM (R 3.6.2)
whisker 0.4 2019-08-28 [1] RSPM (R 3.6.1)
withr 2.1.2 2018-03-15 [1] RSPM (R 3.6.1)
xfun 0.12 2020-01-13 [1] RSPM (R 3.6.2)
[1] /home/james/R/x86_64-pc-linux-gnu-library/3.6
[2] /opt/R/3.6.2/lib/R/library
We are basically just returning with the driver and driver manager give us. I am not sure why they work like this, but here we are.
A further note on this:
When connected to a default database (soleng in this case), I can see schemas for that database:
However, those same schemas appear for other databases as well, even though they are not valid schemas in those databases. In this case, soleng1 has different schemas, but it shows with identical schemas as soleng when soleng is set to the default database:
This is due to the behavior of odbcListObjects()
Furthermore, when I don't specify a default database, I cannot see individual schemas in the database:
I thought this might be due to the fact that catalog is not passed to connection_sql_tables here since it's just an empty string. However, when catalog is passed, then the resulting data.frame is empty and as a result, all tables are returned, not just schemas. I've played around with different values here to see if I can create the expected behavior, but so far I haven't had any luck.
