# DB Connection in RStudio

###### tags: `bmrn` `rstats`

### Version: 2022-06-16

This document will walk you through a database connection in RStudio Server (using the Citrix desktop).

## Navigate to RStudio Server

This example walks through setting up a database connection in [RStudio Server (DEV)](https://aceshiny-dev.bmrn.com/rstudio/s/57ea13c286bd33c286bd3/). If you don't have access to RStudio, you'll have to request a user license from your manager.

These instructions assume you've installed and are using `renv` for package management. If you haven't set up your project with `renv`, follow the instructions [here](https://bmrn-my.sharepoint.com/:b:/p/ma904058/EfeiEahlmaBDidb6EXdiM7YB_JLZn-EDFwlBZ3GM0gjrug?e=Bjf8ec).

## Install DBI/odbc packages

Below we install the packages recommended from [RStudio’s documentation](https://db.rstudio.com/getting-started/connect-to-database), `DBI` and `odbc`:

### DBI

Use the following code to install `DBI`

# install DBI
renv::install("DBI")
library(DBI)

### odbc

**KNOWN ISSSUE:** The `odbc` package has trouble installing in RStudio Server (see below)

> *We’re aware of this issue. Sadly, the newer version of the ODBC package utilize modern C++ features that are not available in the version of the GCC compiler provided by the OS and is not particularly easy to address.*
> *You could, however, try installing the pre-compiled binary package provided by RStudio. We have a colleague who is working on this right now and I can have him share his progress with you.*

You'll need to install `odbc` from a different repo, which you can do with the code below:

# repo for odbc package
odbc_repo <- paste0("https://aceshiny-dev.bmrn.com/", 
  "rspm/full-cran-as-of-2021-11-01/__linux__/centos7/latest")
# install odbc (from other repo)
options(
repos = odbc_repo
)
# install with renv
renv::install("odbc")
# load
library(odbc)

After these packages have been installed, use the following code to get a snapshot of your current packages and store them in the lockfile.

# update renv
renv::snapshot()

This will prompt you to confirm these packages should be updated in the lockfile (enter `y` and press return).

## odbc installed drivers

This will list the installed drivers (once `odbc` is installed and running properly)

# get list of drivers 
sort(unique(odbcListDrivers()[[1]]))
# "Athena"     "BigQuery"   "Cassandra"  "Hive"       "Impala"     
# "MongoDB"    "MySQL"      "Netezza"  "Oracle"     "PostgreSQL" "Redshift"
# "Salesforce" "SQLServer"  "Teradata

Now that we have the drivers installed, we can connect to the database.

## Connect to a database

The commands for connecting to a database are below.

# test DB connection 
dbconn <- dbConnect(drv = odbc::odbc(),
                Driver = "SQLServer",
                Server = "172.24.98.38",
                Database = "cdmrestricted" , 
                UID = "SRV_CDM_RESTRICTED",
                PWD = rstudioapi::askForPassword("Database password"),
                Port = 1433)

This will prompt RStudio to ask you for a passoword, **in which you'll enter your BioMarin credentials.**

After this, you'll see the following connection in the RStudio Server **Connections** pane:


This connection will be listed when you return to the project: