November 29, 2023
This document details how to create a database connection to different relational data sources from R, in the Windows Enclave Virtual Desktop using ODBC (i.e. the Complete PDSR Curated Data Set, COVID-19 Mart, etc.)
The Microsoft ODBC Drivers for SQL Server are stand-alone ODBC drivers which provide an application programming interface (API) implementing the standard ODBC interfaces to Microsoft SQL Server.
- Access to Enclave Platform should already be granted and your workspace provisioned. If you do not have the approval to access the Enclave Platform, complete and submit the Access Request Form.
- You should be logged on to your Enclave Windows Virtual Desktop. You cannot access the Enclave Platform resources from outside the platform.
Step 1: Create a DSN
The simplest way to make a connection from R is using a Data Source Name (DSN). You will need to create a DSN for the data source you are trying to connect to (i.e. Complete PDSR Curated Data Set, COVID19 Mart, etc.)
Such connections, are created using the user's MGB credentials, and are normally used throughout an active session, as shown in the example below. Please follow the step-by-step instructions in the document, How to Create a Connection to a Data Source in Windows Enclave VDI, to create a DSN for your application.
Step 2: Creating a Database Connection from R Programs
Please Note: The example will not run if you don't install odbc packages. Here is the installation of dependent packages.
Now, paste and run the R code below.
Make sure you replace <DSN_Name> with the actual name of the Data Source Name (DSN) from Step 1 (i.e. "PDSR_Monthly")
library(odbc) con <- dbConnect(odbc::odbc(), DSN="DSN_Name") dbGetQuery(con, "select getdate()")
Note: The above code was tested to run successfully under R 4.0.4
Setting up R to connect to SQL Server | RStudio Support