Dataset access via ODBC for R

Rstudio

1) The ODBC connection can be made selecting the 'connections' tab and clicking on “New Connection” 

2) Selecting “SeRP-PostgreSQL”:

3) This will bring up the connection configuration window:

4) Replace the Connection commands shown above with the below text:

library(DBI)

con <- dbConnect(odbc::odbc(), “SeRP-PostgreSQL”, uid=”<your_username>”, pwd=”<your_password>”)

Or to avoid hardcoding your password, replace the 

“pwd=<your_password>”

with 

“pwd=rstudioapi::askForPassword(“Password:”)”

Click on OK to connect and display up the table list:

Example commands to review tables:

library(odbc)
library(tidyverse)
library(dplyr)
library(dbplyr)

Load the above libraries to allow reviewing of the database.

odbcListObjects(con)


This command will list the catalog name. When using SeRP, the catalog name will always be 'UKSERPMonash' 
 
odbcListObjects(con, catalog="UKSERPMonash", schema="schema") 
 
This command will list the schema,  "schemaname" should match the name of your schema as listed on the 'connections' tab.  
 
odbcListColumns(con, catalog="UKSERPMonash", schema="tablename", table="tablename") 


This command will list the table variables, "tablename" should match the name of your table as listed on the 'connections' tab.


tbl(con, in_schema("schemaname", "tablename"))

This command will display the table "tablename" using schema "schemaname"

Finding the relevant Schema and Table names