Connection
SAIL-Connection.Rd
SAILDB ODBC DBI connection
Details
Available options:
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 connectionsSAILDB.DROP.UDF
: Specifies whether thesaildb::Connection$drop()
method should use the SAIL user-defined function instead of using an anonymous procedureSAILDB.CHUNK.SIZE
: Describes the number of rows to be sent in each statement when saving adata.frame
to the database; this can be reduced or increased depending on how wide your table structure isSAILDB.TIMEZONE
: Specifies the timezone used forDATE
,TIMESTAMP
andDATE
columnsSAILDB.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'sPR_SAIL
is1208
, 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 toNA
will use your system defined variable and will not update it.SAILDB.QUIET
: Determines whether thesaildb::Connection
methods will send condition messages when operations are started / finished; can be used to measure performance and/or debug statement(s)SAILDB.NO.WARN
: Determines whether warnings will be logged to the consoleSAILDB.THROW.ERRORS
: Specifies whether the current thread should be halted when an error is encountered; you are expected to wrap yoursaildb::Connection
calls with an error handler if you deactivate this option
Public fields
profile
(
Profile|NA
)
A private field referencing the R6 saildb::Profile classusing.profile
(
logical
)
A private field specifying whether this connection is using a profile
Active bindings
connected
(
S4|NA
)
A private field referencing the ODBC handleconnected
(
character|NA
)
A private field referencing the sanitised connection string used to connect to the databaseprofile
(
Profile|NA
)
A private field referencing the R6 saildb::Profile classusing.profile
(
logical
)
A private field specifying whether this connection is using a profileconnected
(
logical
)
A read-only field describing whether this connection is currently active and whether it is validconnected
(
character|NA
)
A read-only field describing the connection string used to connect to the databaseconnected
(
character|NA
)
A read-only field referecing the username used to connect to the databaseconnected
(
character|NA
)
A read-only field describing the name of the connected database
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 ifuse.profile
is flagged, otherwise prompts the client to enter their usernamepassword
(
character
)
An optional passworddatabase
(
character
)
An optional database name; defaults toSAILDB.DEF$DATABASE
constantuse.profile
(
logical
)
Specifies whether SAILDB should attempt to grab this database's password from the profile's keychain; defaults tooption(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 thesaildb::Connection
's details section for more information on the code pages available. SpecifyingNA
as the code page will use the client'sDB2CODEPAGE
variable; defaults tooption(SAILDB.DB2.CODEPAGE = SAILDB.DEF$CODEPAGE.VAL)
, i.e.1208L
.
Method is.temporary()
Determines whether the given table is temporary or not based on its reference
Method exists()
Determines whether a table exists
Arguments
table.reference
(
character
)
The table schema & name reference in the shape of[SCHEMA].[TABLE]
stop.on.error
(
logical
)
Whether to return aFALSE
logical when an error is encountered instead of stopping the execution of the parent thread; defaults tooption(SAILDB.THROW.ERRORS=TRUE)
suppress.logs
(
logical
)
Whether to suppress message logs; defaults tooption(SAILDB.QUIET=FALSE)
suppress.warnings
(
logical
)
Whether to suppress warnings; defaults tooption(SAILDB.NO.WARN=FALSE)
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
Arguments
stmt
(
character
)
The SQL statement stringstop.on.error
(
logical
)
Whether to return aFALSE
logical when an error is encountered instead of stopping the execution of the parent thread; defaults tooption(SAILDB.THROW.ERRORS=TRUE)
suppress.logs
(
logical
)
Whether to suppress message logs; defaults tooption(SAILDB.QUIET=FALSE)
suppress.warnings
(
logical
)
Whether to suppress warnings; defaults tooption(SAILDB.NO.WARN=FALSE)
Method query()
Submits and synchronously executes the specified SQL query
Arguments
stmt
(
character
)
The SQL statement stringstop.on.error
(
logical
)
Whether to return aFALSE
logical when an error is encountered instead of stopping the execution of the parent thread; defaults tooption(SAILDB.THROW.ERRORS=TRUE)
suppress.logs
(
logical
)
Whether to suppress message logs; defaults tooption(SAILDB.QUIET=FALSE)
suppress.warnings
(
logical
)
Whether to suppress warnings; defaults tooption(SAILDB.NO.WARN=FALSE)
Method execute()
Submits and synchronously executes an SQL statement
Arguments
stmt
(
character
)
The SQL statement stringstop.on.error
(
logical
)
Whether to return aFALSE
logical when an error is encountered instead of stopping the execution of the parent thread; defaults tooption(SAILDB.THROW.ERRORS=TRUE)
suppress.logs
(
logical
)
Whether to suppress message logs; defaults tooption(SAILDB.QUIET=FALSE)
suppress.warnings
(
logical
)
Whether to suppress warnings; defaults tooption(SAILDB.NO.WARN=FALSE)
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
Arguments
expr
(
expression
)
Some arbitrary transaction expressionstop.on.error
(
logical
)
Whether to return aFALSE
logical when an error is encountered instead of stopping the execution of the parent thread; defaults tooption(SAILDB.THROW.ERRORS=TRUE)
suppress.logs
(
logical
)
Whether to suppress message logs; defaults tooption(SAILDB.QUIET=FALSE)
suppress.warnings
(
logical
)
Whether to suppress warnings; defaults tooption(SAILDB.NO.WARN=FALSE)
Method exit.transaction()
Used to exit a transaction and to perform a rollback from within a saildb::Connection$transaction
expression
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 savecan.append
(
logical
)
Whether to append to the table if it already exists; defaults toFALSE
can.overwrite
(
logical
)
Whether to truncate and overwrite the table if it already exists; defaults toFALSE
sanitise.columns
(
logical
)
Whether to strip all non-DB2 compliant characters from a column's name; defaults toTRUE
logical.as.integer
(
logical
)
Whether to insert logical types as aSMALLINT
datatype; otherwise resolves to aVARCHAR
type; defaults toTRUE
parse.datetimes
(
logical
)
Whether to attempt to parse character columns as one of[ DATE | TIME | TIMESTAMP ]
; defaults toFALSE
reduce.db.logging
(
logical
)
Alters global tables to stop initial logging; defaults toTRUE
chunk.size
(
integer
)
Determines the size of insert statement chunks; defaults tooption(SAILDB.CHUNK.SIZE=SAILDB.DEF$MIN.CHUNK.SIZE)
stop.on.error
(
logical
)
Whether to return aFALSE
logical when an error is encountered instead of stopping the execution of the parent thread; defaults tooption(SAILDB.THROW.ERRORS=TRUE)
suppress.logs
(
logical
)
Whether to suppress message logs; defaults tooption(SAILDB.QUIET=FALSE)
suppress.warnings
(
logical
)
Whether to suppress warnings; defaults tooption(SAILDB.NO.WARN=FALSE)
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
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 tablecan.append
(
logical
)
Whether to append to the table if it already exists; defaults toFALSE
can.overwrite
(
logical
)
Whether to truncate and overwrite the table if it already exists; defaults toFALSE
reduce.db.logging
(
logical
)
Alters global tables to stop initial logging; defaults toTRUE
stop.on.error
(
logical
)
Whether to return aFALSE
logical when an error is encountered instead of stopping the execution of the parent thread; defaults tooption(SAILDB.THROW.ERRORS=TRUE)
suppress.logs
(
logical
)
Whether to suppress message logs; defaults tooption(SAILDB.QUIET=FALSE)
suppress.warnings
(
logical
)
Whether to suppress warnings; defaults tooption(SAILDB.NO.WARN=FALSE)
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
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 columnscan.overwrite
(
logical
)
Whether to truncate and overwrite the table if it already exists; defaults toFALSE
reduce.db.logging
(
logical
)
Alters global tables to stop initial logging; defaults toTRUE
stop.on.error
(
logical
)
Whether to return aFALSE
logical when an error is encountered instead of stopping the execution of the parent thread; defaults tooption(SAILDB.THROW.ERRORS=TRUE)
suppress.logs
(
logical
)
Whether to suppress message logs; defaults tooption(SAILDB.QUIET=FALSE)
suppress.warnings
(
logical
)
Whether to suppress warnings; defaults tooption(SAILDB.NO.WARN=FALSE)
Method grant()
Attempts to grant SELECT
on the specified schema, or tables, to the specified user(s)
Arguments
schema
(
character
)
The schema nametables
(
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 charactersusers
(
character|list<character>
)
A list of users to grantSELECT
permission tostop.on.error
(
logical
)
Whether to return aFALSE
logical when an error is encountered instead of stopping the execution of the parent thread; defaults tooption(SAILDB.THROW.ERRORS=TRUE)
suppress.logs
(
logical
)
Whether to suppress message logs; defaults tooption(SAILDB.QUIET=FALSE)
suppress.warnings
(
logical
)
Whether to suppress warnings; defaults tooption(SAILDB.NO.WARN=FALSE)
Method drop()
Attempts to drop a table
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 toTRUE
use.udf
(
logical
)
Whether to use the user-definedDROP_IF_EXISTS
function found in thePR_SAIL
database; defaults tooption(SAILDB.DROP.BY.UDF=TRUE)
stop.on.error
(
logical
)
Whether to return aFALSE
logical when an error is encountered instead of stopping the execution of the parent thread; defaults tooption(SAILDB.THROW.ERRORS=TRUE)
suppress.logs
(
logical
)
Whether to suppress message logs; defaults tooption(SAILDB.QUIET=FALSE)
suppress.warnings
(
logical
)
Whether to suppress warnings; defaults tooption(SAILDB.NO.WARN=FALSE)
Method truncate()
Attempts to truncate a table
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 toFALSE
obey.del.triggers
(
logical
)
Whether to specify that we want to throw an error if delete triggers are defined for this table; defaults toFALSE
stop.on.error
(
logical
)
Whether to return aFALSE
logical when an error is encountered instead of stopping the execution of the parent thread; defaults tooption(SAILDB.THROW.ERRORS=TRUE)
suppress.logs
(
logical
)
Whether to suppress message logs; defaults tooption(SAILDB.QUIET=FALSE)
suppress.warnings
(
logical
)
Whether to suppress warnings; defaults tooption(SAILDB.NO.WARN=FALSE)
Method get.profile()
Attempts to retrieve the stored saildb::Profile
Returns
Either (a) the stored saildb::Profile
of type R6
if initialised; or (b) NA