Support

Troubleshooting Connections

Follow

When I click New Connection, I don't see the kind of connection I want to make.

This is the most common problem users encounter. The New Connection dialog lists all the connection types it can find on the system. If you don't see the one you want listed, you may need to configure your system with the appropriate connection types. Here are the types of connections the dialog lists:

1) Connection types defined by installed R packages. 

Any R package can supply a connection. Currently, the sparklyr package and odbc package define connection types. Install one of these packages using e.g., install.packages("odbc")

2) ODBC drivers installed on your system.

If the odbc package 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.

3) ODBC DSNs installed on your system. 

If the odbc package is installed, any system DSN (Data Source Name) you have installed will be listed. 

4) 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.

When I try to connect, I always get a message saying I need to install a package.

The latest versions of the R packages that provide database connectivity are required.

If you are using a CRAN repository such as MRAN that does not include the latest package versions, you may not be able to install these from your default CRAN repository. You can either temporarily add our CRAN repository, or install the development version of the package from e.g., Github (see the package's help page for more information).

I made a connection in R code, but it's not 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're the package author, you can implement the RStudio Connections Contract to integrate with the Connections pane.

How do I enable tracing on my unixODBC driver connections?

Enabling tracing at the driver-manager level is a useful way to troubleshoot ODBC driver issues. This article explains how to enable tracing for each of the driver managers to help in ODBC troubleshooting tasks.

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.

How can I connect to an Oracle database?

In addition to installing a driver, Oracle also requires you to download and install the Oracle Instant Client on the system where you are running R. If you use Oracle in your enterprise, you may already have this software installed. If not, you will have to create an Oracle account and accept the licensing terms before downloading the Oracle Instant Client software.

[Linux] If you are working with RStudio Server, you should point your LD_LIBRARY_PATH at the installation path for the Oracle Instant Client. An easy way to do this is to add a line to rserver.conf like this:

rsession-ld-library-path=/usr/lib/oracle/12.1/client64/lib

Whenever you modify rserver.conf, make sure to restart RStudio Server.

[Linux] Where are my ODBC configuration files?

Configuration files are typically located under /etc/. You can find the location of odbcinst.ini and odbc.ini files by running the odbcinst -j command:

> odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

[Linux] How do I find dependencies for my ODBC drivers?

ODBC drivers on Linux are shared objects with filenames ending in *.so. You can print the shared object dependencies with the ldd command. This is useful in identifying missing dependencies.

> ldd sqlserver/bin/lib/libsqlserverodbc_sb64.so 
	linux-vdso.so.1 =>  (0x00007fff4ffe2000)
	libdl.so.2 => /lib64/libdl.so.2 (0x00007f96c8b4e000)
	libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f96c8932000)
	librt.so.1 => /lib64/librt.so.1 (0x00007f96c8729000)
	libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f96c8421000)
	libm.so.6 => /lib64/libm.so.6 (0x00007f96c811f000)
	libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f96c7f08000)
	libc.so.6 => /lib64/libc.so.6 (0x00007f96c7b47000)
	/lib64/ld-linux-x86-64.so.2 (0x00007f96cb7ba000)

[Linux] How can I test a database connection outside of R?

Sometimes it's easier to troubleshoot connections outside of R. You can verify that a connection works by installing isql, a command-line tool for interactive SQL access to databases. To use isql, you will have to create a data source name (DSN) first.  Usage of the tool is as follows:

isql DSN [USER [PASSWORD]] [options]

How do I use JDBC with RStudio?

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.

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 has a long history with R, and has recently been enhanced with help from the R Consortium. 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.

What is a DSN?

A data source name (DSN) is a configuration stored in odbc.ini that contains the information about a specific database that an ODBC driver needs in order to connect to it. 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.

Comments