Connecting to a Database Using R

Table of Contents

• Setting Up the Container
• Connecting to the Database

Setting Up the Container

The steps below will go over how to set up a container in crunchr. Make sure you are in SAFE before proceeding.

Step One: Accessing Crunchr

On the SAFE desktop, click on the "PMAP Tools" folder, and then click on "PMAP crunchr Analytics"

Step Two: Creating a Container

On the crunchr homepage, click on "Compute," and then click "Create Container." You can name your container whatever you would like. the Domain does not need to change. For "Compute Image," select "PMAP RStudio" from the drop-down. then click "Create."

Connecting to the Database

Once you open your new container, you should see an Rstudio interface. The following steps will show you what code to run in Rstudio. You can copy and paste the code from here to your RStudio console. Just make sure to make any necessary changes (database name, your jhed, etc)

Setting up the Connection

The following code establishes a connection with the CAMP_PMCoe_Projection database, for demonstration purposes. Make sure to confirm the name of your database and update the field accordingly. Also note it may not run right here in this notebook, but it should run fine after copying and pasting it into the RStudio environment.

library(RODBC)

print(Sys.setenv(JHED = 'YourJHED')) #Put your JHED here
print(Sys.setenv(JHED_PWD = rstudioapi::askForPassword("")))


server="ESMPMDBPR4.WIN.AD.JHU.EDU"
db_name="CAMP_PMCoe_Projection" #Change this
pwd = Sys.getenv("JHED_PWD")
stopifnot(nzchar(pwd))
JHED = Sys.getenv("JHED")
stopifnot(nzchar(pwd))
 
connectionString <- paste0("Driver=freeTDS;TDS_Version=8.0;",'Server=',server,';Port=1433;Database=',
                           db_name,'; Uid=win\\',Sys.getenv("JHED"),
                           ';Pwd=',Sys.getenv("JHED_PWD"))
 
con <- odbcDriverConnect(connection=connectionString)

Error in library(RODBC): there is no package called 'RODBC'
Traceback:

1. library(RODBC)

Using SQL

You can view a list of available tables with

sqlTables(con)

You can put the contents of a table into a dataframe with

sqlFetch(con, 'TableName')

You can execute any SQL query and store the results in a dataframe with

sqlQuery(con, "Query")

See below for example code

#View the tables you can access
tabs <- sqlTables(con) #Prints out list of tables; use tables w/ dbo schema
View(tabs)

#Put the contents of a table into a dataframe
df <- sqlFetch(con, 'encounters')
View(df)

#Execute a query and put the results in a dataframe
df2 <- sqlQuery(con, "SELECT TOP 20 * FROM dbo.encounters")
View(df2)

close(con) #close the connection when done

Error in sqlTables(con): could not find function "sqlTables"
Traceback:

Additional Resources

RODBC Documentation