This article answers frequently asked questions from RStudio Connections. For additional help, see Troubleshooting RStudio Professional Drivers and db.rstudio.com.
When I click New Connection, why don't I see the kind of connection I want to make?
The RStudio IDE will automatically detect specific database connections that are set up on your system. There are five ways to set up connections that will automatically populate in the new connection dialog. For more information, see Using RStudio Connections. The five methods are:
- odbcinst.ini (/etc/odbcinst.ini): ODBC drivers installed on your system. If the
odbcpackage is installed, any ODBC driver you have installed will be listed. The method for installing ODBC drivers varies by system. See our page on Setting up ODBC Drivers. - odbc.ini (/etc/odbc.ini): ODBC data source names (DSN's) on your system. If the
odbcpackage is installed, any DSN on your system will be listed. - .odbc.ini (~/odbc.ini): ODBC data source names (DSN's) in your home directory. If the
odbcpackage is installed, any DSN on your home directory will be listed. - R snippets (/etc/rstudio/connections/<snippet>.R): Connections supplied by your administrator. An administrator may put connection snippets into a folder to make them available to end users. If you're an administrator, see our page on RStudio Connection Snippet Files for more information.
- R packages (/inst/rstudio/connections/<snippet>.R): Connection types defined by installed R packages. Any R package can supply a connection. Currently, sparklyr, odbc, and pins implement connection contracts. Install one of these packages using e.g.,
install.packages("odbc").
I made a connection in R code, but why isn't it showing up in the Connections pane?
The package which makes the connection needs to tell RStudio that the connection has been created. Not all R packages that connect to data do this. If you are the package author, you can implement the RStudio Connections Contract to integrate with the Connections pane.
How do I set up an ODBC connection?
If you want to connect R to a database, you will need to install and configure an ODBC driver on the system where you are running R. You can get ODBC drivers from third parties, the database vendor themselves, or from RStudio if you have purchased our professional products. If you work on a platform other than Windows, you may need to install the unixODBC driver manager if it does not already exist (contact your system administrator for assistance). You may also need to configure your driver location in the odbcinst.ini file and data source names (DSN) in odbc.ini. See Setting Up ODBC Drivers for more information. Also refer to your database vendor documentation for specific database material.
What is a DSN?
A data source name (DSN) is a configuration stored in odbc.ini that contains information about a database connection. The DSN has a name like "SQL Server Marketing DB", and points to a single data source and a specific driver listed in the odbcinst.ini file. A DSN is one of the four methods RStudio uses to discover existing connections. For more information, please read Setting Up ODBC Drivers.
What are the odbc.ini and odbcinst.ini files?
The system file odbcinst.ini contains information about ODBC drivers available to all users, and the odbc.ini file contains information about DSN's available to all users. You can locate these files with odbcinst -j.
These files can exist at the system (i.e. server) level or at the user level. The system files are odbcinst.ini and odbc.ini (note no leading dot), and the user file is ~/.odbc.ini in each user's home directory (note leading dot). For more details, please see everything you wanted to know about odbcinst but were afraid to ask.
Should I use unixODBC or iodbc?
RStudio recommends using unixODBC because odbc R package depends on it. For more information, see the unixODBC User Manual.
Should I use odbc or RODBC?
The RODBC package is not compatible with DBI; therefore, we recommend switching to the newer odbc package. The DBI package is a common interface between R and database management systems. It is important that modern database connectors for R be DBI-compliant. Compared to the existing RODBC package, odbc is also faster (~3x for reading, ~2x for writing), translates date/time data types, and is under active development.
Can I use JDBC drivers?
JDBC drivers are incompatible with the odbc R package maintained by RStudio. If you want to use JDBC drivers along with Java, you will have to install these open-source R packages: rJava, RJDBC, and DBI. The R console will recognize JDBC connections, but they will not be displayed in the the Connections pane unless you or a third party has created a connections contract. RStudio does not provide professional support for JDBC connections.
How do I connect to Oracle?
Connecting to Oracle databases with RStudio Professional Drivers
Why does my catalog only show the first letter of each table?
Where can I find instructions for MacOS aarch64/arm64/M1/M2/Apple Silicon?
Look for the MacOS aarch64 instructions under Setting up database connections.
Comments