This material does not describe a function, but (rather) the various parameters governing ODBC communications and package behaviour. The majority of these are passed through to RODBC.
SQRL adopts a set-and-forget approach, wherein changes to the values of these parameters are persistent, and all subsequent communications make use of those values. Each registered (SQRL) data source has its own independent set of values.
A character string (typically a single character). When an atomic object (typically a vector of character or integer type) is pasted into SQL, that object is first collapsed to a single string, with aCollapse separating each sequential pair of the object's elements. The default value is comma (“,”).
A logical vector, or a numeric vector (of column indices), or a character
vector (of column names).
Argument to RODBC::sqlQuery (see also
utils::read.table).
Tells RODBC which character columns of a table, as returned by a query
to the ODBC connection, not to convert to some other data type
(i.e., which character columns to leave as is).
Due to SQRL's set-and-forget approach to parameters, it is inconvenient to
change as.is on a query-by-query basis.
That being the case, it is often best defined as a logical singleton (either
TRUE or FALSE).
Alternatively, temporary query-specific values can be set within SQRL
scripts (see sqrlSource).
The default value is FALSE (convert all character columns).
A logical singleton.
Tells SQRL whether or not to automatically close the data source
connection after each query (in general, a sequence of multiple statements).
The default value is FALSE, which leaves the connection open.
When set to TRUE, connections will open only for the duration of each
query.
When user input is required for authentication each time a new connection is
opened, the default setting will be more convenient.
A logical singleton.
Argument to RODBC::odbcDriverConnect.
Tells RODBC whether or not to trust the nominal number of rows returned by
the ODBC connection.
Locked while the connection is open.
The default value is TRUE, except for SQLite (FALSE).
A positive integer.
Argument to RODBC::sqlQuery.
Specifies the number of rows (of a query result) to fetch at a time.
The default value is 1000.
A character string, specifically one of “nochange”, “toupper”,
“tolower”, “mysql”, “postgresql”, or “msaccess”.
Argument to RODBC::odbcDriverConnect.
Specifies case-changing behaviour for table and column names.
Locked while the connection is open.
The default value is “nochange”.
An RODBC connection handle.
Returned by RODBC::odbcDriverConnect.
Argument to RODBC::sqlQuery.
This parameter is read-only.
A character vector of length 0, 1, or 2, or NULL.
Argument to RODBC::odbcDriverConnect.
Specifies the quote character(s) for column names.
A vector of length zero means no quotes, of length one means apply the specified
quote character at both ends of a name, and of length two means apply the first
character to the start of the name and the second character to the end of the
name.
Locked while the connection is open.
The default value is a backtick for MySQL, and a double-quote for everything
else.
A character string.
Argument to RODBC::odbcDriverConnect.
Specifies an ODBC connection string.
The content of this string will be database-management system (DBMS)
dependent.
Overrides dsn, should both be defined.
Locked while the connection is open.
Defaults to the empty string (connect via DSN instead).
Will accept NULL as an alias for the empty string.
Can be specified as a character vector of named (and/or unnamed) components,
from which a single string will be constructed (see the examples in
sqrlSource).
Setting connection resets dsn, unless connection contains the
“<dsn>” placeholder (see sqrlSource).
A character string.
Argument to RODBC::odbcDriverConnect.
Names the encoding returned by the DBMS.
Locked while the connection is open.
Defaults to the empty string (use encoding of the R locale).
Will accept NULL as an alias for the empty string.
A character string (typically a single character).
Argument to RODBC::sqlQuery.
Defines the decimal-place marker to be used when converting data from text to
numeric format.
The default value is options("dec"), as set by RODBC.
A character string.
The name or file path of the ODBC driver for the source (either
currently in use, or to be used when a channel is opened).
This determines the requisite dialect of SQL.
Locked while the connection channel is open.
Defaults to the empty string.
Will accept NULL as an alias for the empty string.
A character string.
Argument to RODBC::odbcConnect.
Specifies the data source name (DSN) to connect to.
Can be a file path.
Overridden by connection, when that parameter is defined.
Setting dsn resets connection, unless connection contains the
“<dsn>” placeholder (see sqrlSource).
Setting dsn also sets driver, if the DSN exists and the
associated driver can be identified.
Locked while the connection is open.
Defaults to the empty string.
Will accept NULL as an alias for the empty string.
A logical singleton.
Argument to RODBC::sqlQuery.
Controls whether or not to throw R errors in response to
DBMS/ODBC exceptions.
The default value is TRUE (this differs from the RODBC default).
A character string, or NULL.
The name of the SQRL interface function for this data source (see
sqrlInterface).
Setting NULL or “remove” removes the interface.
The default value is NULL (undefined).
A logical singleton.
Argument to RODBC::odbcDriverConnect.
Locked while the connection is open.
Controls whether or not to interpret table names of the form “aaa.bbb” as
table “bbb” in schema/database “aaa”.
The default value is TRUE.
A character string (typically a single character). When a list-like object (typically an actual list) is pasted into SQL, that object is first collapsed to a single string, with lCollapse separating each sequential pair of the object's elements (to each of which, aCollapse will have first been applied). The default value is the empty string (“”).
A character vector.
Empty by default.
Holds named procedures, as defined by the user (see sqrlScript and
sqrlUsage).
Setting to NULL empties the library.
An integer.
Argument to RODBC::sqlQuery.
Caps the number of rows fetched back to R.
The default value is 0 (meaning unlimited; retrieve all rows).
A character vector.
Argument to RODBC::sqlQuery.
Specifies strings to be mapped to NA within character data.
The default value is “NA”.
A character string. The name of this SQRL data source. While often identical to the names of both the underlying ODBC data source and the SQRL interface function, it need match neither in general. Multiple SQRL sources may interface with the same ODBC source. This parameter is write once, and cannot be changed after creation of the SQRL source. There is no default value.
A character string.
Argument to RODBC::sqlQuery.
The string with which to replace SQL_NULL_DATA items within character
columns.
The default value is NA_character_.
A character string.
Defines a reliable, trusted, SQL statement, used by SQRL to
verify source connections.
The initial value is NULL, which causes a simple,
DBMS-dependent, statement to be determined the first time a connection
is opened.
Manual definition may be necessary in the event that SQRL fails to
identify an appropriate statement for the particular DBMS of the
source.
An invalid ping statement may lead to incorrect assessments of whether or not
the connection is open.
Manually setting NULL causes the statement to be redetermined the next
time a connection is opened.
Pings are submitted verbatim, without passing through SQRL's parser.
A character string (typically a single character).
Defines an indicator to be applied to the R command prompt when the connection
is open and visible is TRUE.
Defaults to the first character of name.
Single-letter indicators are recommended since, if two sources are open and one
indicator is a substring of the other, then SQRL may fail to correctly
update the prompt when one source is closed.
Can be set to an empty string, in which case nothing is applied to the prompt.
Will accept NULL as an alias for the empty string.
A character string.
Argument to RODBC::odbcConnect.
Specifies a password to use at the next authentication request.
This need not match the password that was used to open the current channel.
Defaults to the empty string (interpreted as do not supply a password to the
ODBC driver).
Will accept NULL as an alias for the empty string.
Write-only.
A logical singleton.
Argument to RODBC::odbcDriverConnect.
Specifies whether or not to optimise the ODBC connection for read-only
access.
Locked while the connection is open.
The default value is FALSE.
An arbitrary object, being the final outcome of the last successful query or
procedure.
Read-mostly.
Can be set to NULL (its default value), to free memory.
A logical singleton, TRUE by default.
Should a query fail due to an apparent network outage or other unexpected loss
of connection, a ping (above) is made to verify that occurrence before
reconnecting and resubmitting the failed query.
This process is automatic and silent, unless manual input is required for
authentication.
However, as any temporary tables will not have survived the initial connection
loss, a non-existence error may eventually occur.
Failure of the second attempt is always fatal (no third attempt will be made).
Changing retry to FALSE disables this action.
A positive integer, between 1 and 1024.
Argument to RODBC::odbcDriverConnect.
Specifies the number of rows to fetch at a time when retrieving query results.
Locked while the connection is open.
The default value is 100.
Manually setting 1 may be necessary with some ODBC drivers.
A logical singleton.
Controls SQRL parser behaviour.
When TRUE (the default), the parser splits multi-statement scripts on
what it considers to be statement-terminating semicolons; submitting the
individual statements as each such semicolon is encountered.
This may fail in the presence of DBMS-specific procedural-language
syntax.
When FALSE (the fallback mode), sequential statements are delimited only
by SQRL tags, especially the <do> tag (see
sqrlScript).
A logical singleton.
Argument to RODBC::sqlQuery.
Controls the conversion of character columns to factor columns within query
results, excluding those columns covered by as.is.
The default value is FALSE.
A character vector of length 0, 1, or 2, or NULL.
Argument to RODBC::odbcDriverConnect.
Specifies the quote character(s) for table names.
A vector of length zero means no quotes, of length one means apply the specified
quote character at both ends of a name, and of length two means apply the first
character to the start of the name and the second character to the end of the
name.
Locked while the connection is open.
Defaults to the value of colQuote.
A character string.
Argument to RODBC::odbcConnect.
Specifies the user identity (UID, user name) to use on the data
source.
Locked while the connection is open.
Defaults to the local name of the R user (Sys.info()["user"]).
Will accept NULL as an alias for the empty string (which is interpreted
as do not pass a UID to the ODBC driver).
May be inaccurate when the UID is specified within a DSN.
A logical singleton.
Controls whether or not to display verbose output during query submission.
Intended mainly for debugging.
The default value is FALSE (verbose output disabled).
Verbose output is always disabled within non-interactive sessions.
A logical singleton.
Toggles display of the wintitle and prompt indicators (while an
open connection channel exists to the source).
The default value is FALSE (do not show indicators).
Changing this to TRUE authorises modification of the “prompt”
global option (see base::options).
A character string, possibly empty.
Will accept NULL as an alias for the empty string.
Defines an indicator that, unless empty, is displayed on the R window title bar
while a connection channel is open to the source, and provided visible is
TRUE.
An asterisk (*) is appended to the indicator while a query is running on the
source, and a plus-sign (+) is appended while results are being retrieved from
it.
A question mark (?) is appended during connection-testing pings, but these are
usually fleeting.
If two sources are open and one indicator is a substring of the other, then
SQRL may fail to correctly update the title when one source is closed.
Only works with R.exe, Rterm.exe and Rgui.exe, and then
only while running on a “Windows” operating system.
Works with both MDI and SDI modes, but does not work with
“RStudio”.
sqrlUsage,
RODBC
# Define a new source.
sqrlSource("thoth", dbcname = "Karnak",
driver = "Teradata Database ODBC Driver 16.10")
# Retrieve all parameter values.
thoth("config")
# Retrieve a (fixed) subset of parameter values.
thoth("settings")
# Retrieve a single (named) parameter value.
thoth("as.is")
# Various means of setting a value.
thoth(as.is = TRUE)
thoth("as.is" = TRUE)
thoth("as.is", TRUE)
thoth("as.is", "TRUE")
thoth("as.is TRUE")
# If you wanted the string 'TRUE'.
thoth(as.is = "TRUE")
thoth("as.is 'TRUE'")
# Various means of setting multiple values.
thoth(as.is = TRUE, stringsAsFactors = FALSE)
thoth(list(as.is = TRUE, stringsAsFactors = FALSE))
thoth(config = list(as.is = TRUE, stringsAsFactors = FALSE))
Run the code above in your browser using DataLab