pool_nhsbsa: Pool to NHSBSA DB

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

Pool to NHSBSA DB

Description

Pool to NHSBSA DB from the Bastion Host. This is particularly useful in shiny apps but can be used as a general alternative to con_nhsbsa. Hopefully you will already have the ODBC driver installed, but if not you may have to raise a service request with IT.

Usage

pool_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

Pool to DB. Note: When finished close your connection using pool::poolClose()

Examples

#Initialise pool assuming you have a `.Renviron` file with DB_DALP_USERNAME
#and DB_DALP_PASSWORD set (recommended)

## Not run: 
  pool <- nhsbsaR::pool_nhsbsa(database = "DALP")

## End(Not run)


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

## End(Not run)


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

## End(Not run)

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

## End(Not run)


# Remember to disconnect your session
## Not run: 
  pool::poolClose(pool)

## End(Not run)

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