con_nhsbsa: Connect to NHSBSA DB

In nhsbsa-data-analytics/nhsbsaR: NHSBSA R package

Connect to NHSBSA DB

Description

Connect to NHSBSA DB from the Bastion Host. Hopefully you will already have the ODBC driver installed, but if not you may have to raise a service request with IT.

Usage

con_nhsbsa(
  dsn = "FBS_8192k",
  driver = "Oracle in OraClient19Home1",
  database,
  username = toupper(Sys.getenv(paste0("DB_", database, "_USERNAME"))),
  password = Sys.getenv(paste0("DB_", database, "_PASSWORD"))
)

Arguments

dsn	Default is "FBS_8192k" with Fetch Buffer Size of 8192k created for us by Platform Services on Azure VDs. If, for some reason, this DSN is unavailable on your VD, set this to NULL in order to use the default "Oracle in OraClient19Home1" driver with default settings.
driver	String, ODBC driver to connect to the DB. Default is correct but an argument is provided in case this ever changes.
database	String, name of the DB - one of c("DALP", "DWCP")).
username	String, username for DB. Default value is Sys.getenv(paste0("DB_", database, "_USERNAME")) and we recommend setting this in an .Renviron file.
password	String, username for DB. Default value is Sys.getenv(paste0("DB_", database, "_PASSWORD")) and we recommend setting this in an .Renviron file.

Value

Connection to DB. Note: When finished close your connection using DBI::dbDisconnect()

Examples

# Initialise connection assuming you have a `.Renviron` file with
# DB_DALP_USERNAME and DB_DALP_PASSWORD set (recommended)
## Not run: 
  con <- nhsbsaR::con_nhsbsa(database = "DALP")

## End(Not run)

# Initialise connection without `.Renviron` file (Note: Never store passwords
# in your code)
## Not run: 
  con <- nhsbsaR::con_nhsbsa(
    database = "DWCP",
    username = rstudioapi::showPrompt(),
    password = rstudioapi::askForPassword()
  )

## End(Not run)


# Boring DBI methods...
## Not run: 
  DBI::dbGetQuery(conn = con, statement = "SELECT * FROM dual")
  DBI::dbReadTable(conn = con, name = "DUAL") # Case sensitive in ODBC

## End(Not run)


# Cool dbplyr methods... (see https://dbplyr.tidyverse.org)
## Not run: 
  dplyr::tbl(src = con, from = "DUAL") # Case sensitive in ODBC
  dplyr::tbl(src = con, from = dbplyr::in_schema(schema = "SYS", table = "DUAL") # Case sensitive in ODBC
  dplyr::tbl(src = con, from = dbplyr::sql("SELECT * FROM dual"))

## End(Not run)


# Remember to disconnect your session
## Not run: 
  DBI::dbDisconnect(con)

## End(Not run)

nhsbsa-data-analytics/nhsbsaR documentation built on Jan. 25, 2025, 8:54 a.m.