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