ODBC connection is showing "(No Tables)" when I connect in R Studio.

RStudio IDE
,
1

Hello --

I'm having some trouble making an ODBC connection to my data sources within R Studio.

install.packages("odbc")
install.packages("DBI")
library(odbc)
library(DBI)
con <- dbConnect(odbc::odbc(), myDSN, uid = myUID, pwd = myPWD)

In the "Connections" tab, it shows that I'm connected to the DSN. When I drill into the connection it shows"(No Tables)". Similarly, when I try:

dbListTables(con)
character(0)

I tried the same code within R and could see all of my tables as I would expect to see them using dbListTables(con).

I tried other ways to connect to this data source and they are all working.

I'm running R version 4.1.2 and R Studio version 2021.09.2+382.

Thanks very much for taking the time to help me out.

2

I have never had any issues with odbc and RStudio
And I don't specifically include the 2 libraries odbc and DBI. I just include plyr and dplyr and tidyr
Is there a chance that there are other libraries loaded when you are running the Rstudio test?

I did a quick test and it looks like RStudio has already included library for dbconnect. which by the way I specify in my open: DBI::dbConnect

3

Thanks for the reply.

I've never had a problem with making this connection in RStudio before. This all worked well when I last ran the process last year.

When I start RStudio, the only libraries that are loaded are stats, graphics, grDevices, utils, datasets, methods, and base. Instead of loading the odbc and DBI libraries, I tried the DBI::dbConnect that you recommended. The connection works but it's not showing any tables. Similarly, I loaded the plyr, dplyr, and tidyr libraries and tried it all again and it's still not working.

I'll try re-installing RStudio to see if that gets me anywhere.

4

I've reinstalled RStudio and am still experiencing the same problem.

5

Hi, I don't believe that re-installing RStudio will help. The key is dbListTables(con), since it is returning nothing, it means that there is nothing being returned to R itself. The one thing I can think is the cause is a change in the underlying DSN connection. If I have to guess, it is either looking at the wrong, and empty, schema, or your access rights changed. From your same machine, I would try connecting to the database from another program, and see if you get the same results. Hope this helps!

6

I'm able to connect to the database using the same DSN everywhere I can think of except RStudio. It'll even work in R using the same commands that are failing in RStudio.

7

If you run DBI::dbListTables(con) in R in a terminal, does it return the list of tables and view?

8

That command works properly in R but not in RStudio.

9

Ok, can you share the output of sessionInfo() from both RStudio and R running in the terminal?

10

R:

R version 4.1.2 (2021-11-01)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19042)

Matrix products: default

locale:
[1] LC_COLLATE=English_United States.1252
[2] LC_CTYPE=English_United States.1252
[3] LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C
[5] LC_TIME=English_United States.1252

attached base packages:
[1] stats graphics grDevices utils datasets methods base

loaded via a namespace (and not attached):
[1] compiler_4.1.2

RStudio:
R version 4.1.2 (2021-11-01)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19042)

Matrix products: default

locale:
[1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United States.1252 LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C LC_TIME=English_United States.1252
system code page: 65001

attached base packages:
[1] stats graphics grDevices utils datasets methods base

loaded via a namespace (and not attached):
[1] compiler_4.1.2 tools_4.1.2

11

I think this has to do with enconding issue in RStudio. It was raised in connection with reading files, but I think it may also be messing with your DB connection. Can you try downgrading to something older than 2021.09.2? RStudio can't deal with file names with unicode characters - #25 by kevinushey cc: @kevinushey

12

Thanks very much. Uninstalling RStudio back to the prior version solved my problem. Is there anything I can do on my end to assist the developers with resolving this issue?

13

Hi, thank you for confirming. It looks like the latest version of RStudio may address the issue (RStudio & Posit Workbench Release Notes), would you mind upgrading to 2022.02.0+443? And test again? I think this entry in the notes addresses your issue.

  • RStudio now supports the experimental UTF-8 UCRT builds of R (#9824)

If that does work, then the advice to other developers would be to upgrade RStudio

14

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.