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.
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 usually defined as a logical singleton (either
TRUE or FALSE).
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 normally only open for the duration of
each query, but may sometimes remain open if a mid-query exception is thrown.
Setting TRUE has no immediate effect upon a connection that is already
open.
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.
Default value is TRUE.
A positive integer.
Argument to RODBC:sqlQuery.
Specifies the number of rows (of a query result) to fetch at a time.
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.
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
specified character to the start of the name and the second specified 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.
Setting connection resets dsn, unless connection contains the
“<dsn>” placeholder.
A character string.
Argument to RODBC:odbcDriverConnect.
Names the encoding returned by the DBMS.
Locked while the connection is open.
Default value is 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.
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.
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.
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.
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 simple SQL statement used by SQRL to verify source connections. This enables SQRL to make one automatic reconnection attempt after a network outage or other unexpected channel closure (this process is silent unless user-input is required for authentication). The default value is DBMS-dependent. Manual definition may be necessary in the event that SQRL fails to identify an appropriate statement for the particular DBMS of the source.
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 the password for authentication.
Locked while the connection is open.
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.
Default value is FALSE.
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.
Argument to RODBC:sqlQuery.
Controls the conversion of character to factor columns within query results,
excluding those columns covered by as.is.
Defaults to default.stringsAsFactors().
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
specified character to the start of the name and the second specified 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 while processing SQL
or SQRL files.
The default is FALSE (verbose output disabled).
When TRUE, usually only the head of each
intermediate object is displayed.
Sufficiently exotic objects may fail to print.
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 (including connection-testing pings), and a plus-sign (+) is appended
while results are being retrieved from it.
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”.