Skip to contents

Connection

SAIL-Connection.Rd

SAILDB ODBC DBI connection

Details

Available options:

  1. SAILDB.USE.SECRETS: Whether to use the saildb::Profile manager to store & load secrets, i.e. a password storage manager for each username used to authenticate database connections

  2. SAILDB.DROP.UDF: Specifies whether the saildb::Connection$drop() method should use the SAIL user-defined function instead of using an anonymous procedure

  3. SAILDB.CHUNK.SIZE: Describes the number of rows to be sent in each statement when saving a data.frame to the database; this can be reduced or increased depending on how wide your table structure is

  4. SAILDB.TIMEZONE: Specifies the timezone used for DATE, TIMESTAMP and DATE columns

  5. SAILDB.DB2.CODEPAGE: Set the desired DB2 code page for this system; code pages define the character sets used and the mapping between IBM <-> OS code sets. The default setting code page for SAIL's PR_SAIL is 1208, i.e. UTF-8 encoding. See IBM's code page documentation here for more details. PLEASE NOTE that this will update your local account's system environment variables permanently; setting this option to NA will use your system defined variable and will not update it.

  6. SAILDB.QUIET: Determines whether the saildb::Connection methods will send condition messages when operations are started / finished; can be used to measure performance and/or debug statement(s)

  7. SAILDB.NO.WARN: Determines whether warnings will be logged to the console

  8. SAILDB.THROW.ERRORS: Specifies whether the current thread should be halted when an error is encountered; you are expected to wrap your saildb::Connection calls with an error handler if you deactivate this option

Public fields

profile

(Profile|NA)
A private field referencing the R6 saildb::Profile class

using.profile

(logical)
A private field specifying whether this connection is using a profile

Active bindings

connected

(S4|NA)
A private field referencing the ODBC handle

connected

(character|NA)
A private field referencing the sanitised connection string used to connect to the database

profile

(Profile|NA)
A private field referencing the R6 saildb::Profile class

using.profile

(logical)
A private field specifying whether this connection is using a profile

connected

(logical)
A read-only field describing whether this connection is currently active and whether it is valid

connected

(character|NA)
A read-only field describing the connection string used to connect to the database

connected

(character|NA)
A read-only field referecing the username used to connect to the database

connected

(character|NA)
A read-only field describing the name of the connected database

Methods

Public methods

Method new()

Initialise a new connection

Usage

Connection$new(
  username = NA,
  password = NA,
  database = SAILDB.DEF$DATABASE,
  use.profile = getOption("SAILDB.USE.SECRETS", TRUE),
  codepage = getOption("SAILDB.DB2.CODEPAGE", SAILDB.DEF$CODEPAGE.VAL)
)

Arguments

username

(character)
An optional username; defaults to the local machine's username if use.profile is flagged, otherwise prompts the client to enter their username

password

(character)
An optional password

database

(character)
An optional database name; defaults to SAILDB.DEF$DATABASE constant

use.profile

(logical)
Specifies whether SAILDB should attempt to grab this database's password from the profile's keychain; defaults to option(SAILDB.USE.PROFILE=TRUE)

Note: you will be prompted to insert your username/password if we fail your user with any associated with a saildb::Profile keychain. Similarly, if the connection fails to authenticate you will be prompted to re-enter your username and password

codepage

(integer|NA)
An integer describing the desired database code page, see the saildb::Connection's details section for more information on the code pages available. Specifying NA as the code page will use the client's DB2CODEPAGE variable; defaults to option(SAILDB.DB2.CODEPAGE = SAILDB.DEF$CODEPAGE.VAL), i.e. 1208L.

Returns

A new SAILDB connection

Method is.temporary()

Determines whether the given table is temporary or not based on its reference

Usage

Connection$is.temporary(table.reference)

Arguments

table.reference

(character)
The table schema & name reference in the shape of [SCHEMA].[TABLE]

Returns

Either (a) a logical reflecting the table's expected temporary status; or (b) an NA value for an invalid table reference

Method exists()

Determines whether a table exists

Usage

Connection$exists(
  table.reference = NA,
  stop.on.error = getOption("SAILDB.THROW.ERRORS", TRUE),
  suppress.logs = getOption("SAILDB.QUIET", FALSE),
  suppress.warnings = getOption("SAILDB.NO.WARN", FALSE)
)

Arguments

table.reference

(character)
The table schema & name reference in the shape of [SCHEMA].[TABLE]

stop.on.error

(logical)
Whether to return a FALSE logical when an error is encountered instead of stopping the execution of the parent thread; defaults to option(SAILDB.THROW.ERRORS=TRUE)

suppress.logs

(logical)
Whether to suppress message logs; defaults to option(SAILDB.QUIET=FALSE)

suppress.warnings

(logical)
Whether to suppress warnings; defaults to option(SAILDB.NO.WARN=FALSE)

Returns

Either (a) a logical reflecting a table's existence; or (b) an NA value for an invalid operation

Method run()

Sends and executes an SQL statement; accepts and handles both SELECT queries, stored procedure execution, the creation of tables, and/or data manipulation queries like INSERT or UPDATE

Usage

Connection$run(
  stmt = NA,
  stop.on.error = getOption("SAILDB.THROW.ERRORS", TRUE),
  suppress.logs = getOption("SAILDB.QUIET", FALSE),
  suppress.warnings = getOption("SAILDB.NO.WARN", FALSE)
)

Arguments

stmt

(character)
The SQL statement string

stop.on.error

(logical)
Whether to return a FALSE logical when an error is encountered instead of stopping the execution of the parent thread; defaults to option(SAILDB.THROW.ERRORS=TRUE)

suppress.logs

(logical)
Whether to suppress message logs; defaults to option(SAILDB.QUIET=FALSE)

suppress.warnings

(logical)
Whether to suppress warnings; defaults to option(SAILDB.NO.WARN=FALSE)

Returns

Either:

  1. The resulting data.frame if executing a query e.g. a SELECT statement

  2. OR; a logical value reflecting the success if executing a statement e.g. an INSERT statement, or a function call etc;

Method query()

Submits and synchronously executes the specified SQL query

Usage

Connection$query(
  stmt = NA,
  stop.on.error = getOption("SAILDB.THROW.ERRORS", TRUE),
  suppress.logs = getOption("SAILDB.QUIET", FALSE),
  suppress.warnings = getOption("SAILDB.NO.WARN", FALSE)
)

Arguments

stmt

(character)
The SQL statement string

stop.on.error

(logical)
Whether to return a FALSE logical when an error is encountered instead of stopping the execution of the parent thread; defaults to option(SAILDB.THROW.ERRORS=TRUE)

suppress.logs

(logical)
Whether to suppress message logs; defaults to option(SAILDB.QUIET=FALSE)

suppress.warnings

(logical)
Whether to suppress warnings; defaults to option(SAILDB.NO.WARN=FALSE)

Returns

The resulting data.frame from a SELECT statement; can return a logical if stop.on.error behaviour is inactive

Method execute()

Submits and synchronously executes an SQL statement

Usage

Connection$execute(
  stmt = NA,
  stop.on.error = getOption("SAILDB.THROW.ERRORS", TRUE),
  suppress.logs = getOption("SAILDB.QUIET", FALSE),
  suppress.warnings = getOption("SAILDB.NO.WARN", FALSE)
)

Arguments

stmt

(character)
The SQL statement string

stop.on.error

(logical)
Whether to return a FALSE logical when an error is encountered instead of stopping the execution of the parent thread; defaults to option(SAILDB.THROW.ERRORS=TRUE)

suppress.logs

(logical)
Whether to suppress message logs; defaults to option(SAILDB.QUIET=FALSE)

suppress.warnings

(logical)
Whether to suppress warnings; defaults to option(SAILDB.NO.WARN=FALSE)

Returns

The number of rows affected; can return a logical if stop.on.error behaviour is inactive

Method transaction()

Run sequential, synchronous SALIR::Connection method calls as a transation; rollbacks can be performed by exiting the transaction using the SALIR::Connection$exit.transaction. Transactions are automatically committed at the end of the expression assuming no errors have occurred and/or no calls to SALIR::Connection$exit.transaction have taken place

Usage

Connection$transaction(
  expr = NA,
  stop.on.error = getOption("SAILDB.THROW.ERRORS", TRUE),
  suppress.logs = getOption("SAILDB.QUIET", FALSE),
  suppress.warnings = getOption("SAILDB.NO.WARN", FALSE)
)

Arguments

expr

(expression)
Some arbitrary transaction expression

stop.on.error

(logical)
Whether to return a FALSE logical when an error is encountered instead of stopping the execution of the parent thread; defaults to option(SAILDB.THROW.ERRORS=TRUE)

suppress.logs

(logical)
Whether to suppress message logs; defaults to option(SAILDB.QUIET=FALSE)

suppress.warnings

(logical)
Whether to suppress warnings; defaults to option(SAILDB.NO.WARN=FALSE)

Returns

A logical reflecting the success of the transaction

Method exit.transaction()

Used to exit a transaction and to perform a rollback from within a saildb::Connection$transaction expression

Usage

Connection$exit.transaction()

Method save()

Attempt to save a data.frame object to a table; either by creating a new table or appending it to an existing table; note that this operation is transactional, so the statements will be rolled back if it fails at any point

Usage

Connection$save(
  table.reference = NA,
  table.data = NA,
  can.append = FALSE,
  can.overwrite = FALSE,
  sanitise.columns = TRUE,
  logical.as.integer = TRUE,
  parse.datetimes = FALSE,
  reduce.db.logging = TRUE,
  chunk.size = getOption("SAILDB.CHUNK.SIZE", SAILDB.DEF$MIN.CHUNK.SIZE),
  stop.on.error = getOption("SAILDB.THROW.ERRORS", TRUE),
  suppress.logs = getOption("SAILDB.QUIET", FALSE),
  suppress.warnings = getOption("SAILDB.NO.WARN", FALSE)
)

Arguments

table.reference

(character)
The table schema & name reference in the shape of [SCHEMA].[TABLE]

table.data

(data.frame)
The data.frame you wish to save

can.append

(logical)
Whether to append to the table if it already exists; defaults to FALSE

can.overwrite

(logical)
Whether to truncate and overwrite the table if it already exists; defaults to FALSE

sanitise.columns

(logical)
Whether to strip all non-DB2 compliant characters from a column's name; defaults to TRUE

logical.as.integer

(logical)
Whether to insert logical types as a SMALLINT datatype; otherwise resolves to a VARCHAR type; defaults to TRUE

parse.datetimes

(logical)
Whether to attempt to parse character columns as one of [ DATE | TIME | TIMESTAMP ]; defaults to FALSE

reduce.db.logging

(logical)
Alters global tables to stop initial logging; defaults to TRUE

chunk.size

(integer)
Determines the size of insert statement chunks; defaults to option(SAILDB.CHUNK.SIZE=SAILDB.DEF$MIN.CHUNK.SIZE)

stop.on.error

(logical)
Whether to return a FALSE logical when an error is encountered instead of stopping the execution of the parent thread; defaults to option(SAILDB.THROW.ERRORS=TRUE)

suppress.logs

(logical)
Whether to suppress message logs; defaults to option(SAILDB.QUIET=FALSE)

suppress.warnings

(logical)
Whether to suppress warnings; defaults to option(SAILDB.NO.WARN=FALSE)

Returns

A logical describing the success of the action

Method create.from()

Wrapper method to create a new table from a SELECT statement; note that this operation is transactional, so the statements will be rolled back if it fails at any point

Usage

Connection$create.from(
  table.reference = NA,
  stmt = NA,
  can.append = FALSE,
  can.overwrite = FALSE,
  reduce.db.logging = TRUE,
  stop.on.error = getOption("SAILDB.THROW.ERRORS", TRUE),
  suppress.logs = getOption("SAILDB.QUIET", FALSE),
  suppress.warnings = getOption("SAILDB.NO.WARN", FALSE)
)

Arguments

table.reference

(character)
The table schema & name reference in the shape of [SCHEMA].[TABLE]

stmt

(character)
The select query used to build your cloned table

can.append

(logical)
Whether to append to the table if it already exists; defaults to FALSE

can.overwrite

(logical)
Whether to truncate and overwrite the table if it already exists; defaults to FALSE

reduce.db.logging

(logical)
Alters global tables to stop initial logging; defaults to TRUE

stop.on.error

(logical)
Whether to return a FALSE logical when an error is encountered instead of stopping the execution of the parent thread; defaults to option(SAILDB.THROW.ERRORS=TRUE)

suppress.logs

(logical)
Whether to suppress message logs; defaults to option(SAILDB.QUIET=FALSE)

suppress.warnings

(logical)
Whether to suppress warnings; defaults to option(SAILDB.NO.WARN=FALSE)

Returns

A logical describing the success of the action

Method map.from()

Wrapper method to create a new table from another table whilst mapping its columns to a different subset; note that this operation is transactional, so the statements will be rolled back if it fails at any point

Usage

Connection$map.from(
  input.reference = NA,
  output.reference = NA,
  output.map = NA,
  can.overwrite = FALSE,
  reduce.db.logging = TRUE,
  stop.on.error = getOption("SAILDB.THROW.ERRORS", TRUE),
  suppress.logs = getOption("SAILDB.QUIET", FALSE),
  suppress.warnings = getOption("SAILDB.NO.WARN", FALSE)
)

Arguments

input.reference

(character)
The input table to clone from in the shape of [SCHEMA].[TABLE]

output.reference

(character)
The output table to map into, in the shape of [SCHEMA].[TABLE]

output.map

(list)
A list of key-value pair characters describing the column name mapping and/or a subset of columns

can.overwrite

(logical)
Whether to truncate and overwrite the table if it already exists; defaults to FALSE

reduce.db.logging

(logical)
Alters global tables to stop initial logging; defaults to TRUE

stop.on.error

(logical)
Whether to return a FALSE logical when an error is encountered instead of stopping the execution of the parent thread; defaults to option(SAILDB.THROW.ERRORS=TRUE)

suppress.logs

(logical)
Whether to suppress message logs; defaults to option(SAILDB.QUIET=FALSE)

suppress.warnings

(logical)
Whether to suppress warnings; defaults to option(SAILDB.NO.WARN=FALSE)

Returns

A logical describing the success of the operation

Method grant()

Attempts to grant SELECT on the specified schema, or tables, to the specified user(s)

Usage

Connection$grant(
  schema = NA,
  tables = NA,
  users = NA,
  stop.on.error = getOption("SAILDB.THROW.ERRORS", TRUE),
  suppress.logs = getOption("SAILDB.QUIET", FALSE),
  suppress.warnings = getOption("SAILDB.NO.WARN", FALSE)
)

Arguments

schema

(character)
The schema name

tables

(character|list<character>)
An optional list/vector of table names to grant access to; in the shape of either (a) a scalar character, (b) a character vector, or (c) a list of characters

users

(character|list<character>)
A list of users to grant SELECT permission to

stop.on.error

(logical)
Whether to return a FALSE logical when an error is encountered instead of stopping the execution of the parent thread; defaults to option(SAILDB.THROW.ERRORS=TRUE)

suppress.logs

(logical)
Whether to suppress message logs; defaults to option(SAILDB.QUIET=FALSE)

suppress.warnings

(logical)
Whether to suppress warnings; defaults to option(SAILDB.NO.WARN=FALSE)

Returns

A logical that describes whether the action was successful

Method drop()

Attempts to drop a table

Usage

Connection$drop(
  table.reference = NA,
  ignore.extinct = TRUE,
  use.udf = getOption("SAILDB.DROP.UDF", TRUE),
  stop.on.error = getOption("SAILDB.THROW.ERRORS", TRUE),
  suppress.logs = getOption("SAILDB.QUIET", FALSE),
  suppress.warnings = getOption("SAILDB.NO.WARN", FALSE)
)

Arguments

table.reference

(character)
The table schema & name reference in the shape of [SCHEMA].[TABLE]

ignore.extinct

(logical)
Whether to ignore non-existent tables; defaults to TRUE

use.udf

(logical)
Whether to use the user-defined DROP_IF_EXISTS function found in the PR_SAIL database; defaults to option(SAILDB.DROP.BY.UDF=TRUE)

stop.on.error

(logical)
Whether to return a FALSE logical when an error is encountered instead of stopping the execution of the parent thread; defaults to option(SAILDB.THROW.ERRORS=TRUE)

suppress.logs

(logical)
Whether to suppress message logs; defaults to option(SAILDB.QUIET=FALSE)

suppress.warnings

(logical)
Whether to suppress warnings; defaults to option(SAILDB.NO.WARN=FALSE)

Returns

A logical that describes whether the action was successful

Method truncate()

Attempts to truncate a table

Usage

Connection$truncate(
  table.reference = NA,
  reuse.storage = FALSE,
  obey.del.triggers = FALSE,
  stop.on.error = getOption("SAILDB.THROW.ERRORS", TRUE),
  suppress.logs = getOption("SAILDB.QUIET", FALSE),
  suppress.warnings = getOption("SAILDB.NO.WARN", FALSE)
)

Arguments

table.reference

(character)
The table schema & name reference in the shape of [SCHEMA].[TABLE]

reuse.storage

(logical)
Whether to specify that all storage allocated to this table will continue to be allocated for the same table; defaults to FALSE

obey.del.triggers

(logical)
Whether to specify that we want to throw an error if delete triggers are defined for this table; defaults to FALSE

stop.on.error

(logical)
Whether to return a FALSE logical when an error is encountered instead of stopping the execution of the parent thread; defaults to option(SAILDB.THROW.ERRORS=TRUE)

suppress.logs

(logical)
Whether to suppress message logs; defaults to option(SAILDB.QUIET=FALSE)

suppress.warnings

(logical)
Whether to suppress warnings; defaults to option(SAILDB.NO.WARN=FALSE)

Returns

A logical that describes whether the action was successful

Method get.hnd()

Attempts to retrieve the stored ODBC handle

Usage

Connection$get.hnd()

Returns

Either (a) the stored DBIConnection handle of type S4; or (b) if not connected: NA

Method get.profile()

Attempts to retrieve the stored saildb::Profile

Usage

Connection$get.profile()

Returns

Either (a) the stored saildb::Profile of type R6 if initialised; or (b) NA

Method clone()

The objects of this class are cloneable with this method.

Usage

Connection$clone(deep = FALSE)

Arguments

deep

Whether to make a deep clone.