This material does not describe a single function, but (rather) how to use SQRL interfaces, once created. These functions do not have their own help files, since their names are not pre-determined.
# Open a connection to the data source. thoth()# Alternative method (explicit form). thoth("open")
# Doing this is fine (the channel survives). rm(list = ls(all.names = TRUE))
# Check if the connection is open. thoth("isopen")
# Open a connection and confirm status. thoth()$isopen
# Close the connection. thoth("close")
# Close the connection when not in use. thoth(autoclose = TRUE)
Opening connections in the above way isn't usually necessary, since this occurs automatically as required.
The isopen command “pings” the data source, to reliably
establish whether or not the connection really is open (including after a
network outage or remote closure).
With autoclose = TRUE, isopen will normally return
FALSE, since the connection is closed after every data source command
sequence (including the open command).
# Submit a query.
thoth("select 1")# Submit another query.
thoth("select ", sample(6, 1), " from dual")
# Submit a query from file.
thoth("my/", "file.sql")
# Submit a parameterised query from file.
thoth("emissions.sqrl", make = "VEB", model = "Trabant 601")
# Submit a multi-statement query.
thoth(query = "use necronomicon; select top
<R> N </R> shoggoths from pit", N = 5)
# Ensure input is treated only as a file name.
thoth(file = "create table")
If necessary, a connection channel will be opened beforehand.
The connection will remain open afterwards, unless autoclose is
TRUE.
To be clear, the phrase ‘parameterised query’ is not meant in the sense of prepared or parameterised statements (as per package RODBCext). Here, parameter substitution occurs inside R, with the resulting string being passed to the ODBC driver as an ordinary query.
If a query should fail because of an unexpectedly lost connection, an attempt will be made to re-connect and re-submit. Unless credentials are required for authentication, this should go unnoticed by the user.
When a query returns no data (as would ‘use database’), the interface function returns invisibly.
If a file called (say) use database should exist, then
thoth("use database") submits the content of that file (rather than the
apparent command).
This renders use of the file argument mostly unnecessary.
Use of either the query or file argument forces interpretation of
the corresponding value as a query or file path (from which to read a query),
respectively.
The query argument uses the verbose option and allows multiple
statements and embedded R (as with sqrlScript files), whereas
unnamed queries do not (instead, they allow and concatenate multiple strings).
# Get information on source data types.
thoth("typeinfo")# List all tables.
thoth("tables")
# List all tables within a database (schema).
thoth("tables", "mydatabase")
# Get information on the columns of a particular table.
thoth("columns", "my.table")
The typeinfo, tables, and columns commands are
simple (reduced functionality) wrappers about
RODBC:sqlTypeInfo,
RODBC:sqlTables, and
RODBC:sqlColumns (respectively).
# Get the associated source definition.
thoth("source")# Get the value of one named parameter.
thoth("uid")
# Alternative method (pings the source).
thoth()$uid
# List the values of all parameters.
thoth("config")
Passwords are returned obliterated.
# Enable visible indication of open connections.
thoth("visible", TRUE)# Define the ping statement for the data source.
thoth("ping", "use database")
# Do not convert strings to factors.
thoth("stringsAsFactors FALSE")
# Set (opening and closing) table-name quotes.
thoth(tabQuote = c("`", "'"))
# Setting multiple parameters at once.
thoth(as.is = TRUE, na.strings = c("NA", "-", ""))
# Set one (named) parameter from a file.
thoth("ping" = "path/to/file")
# Import an entire configuration file.
thoth("config", "path/", "to/", "file")
# Reset parameters to their default values.
thoth("reset", c("as.is", "na.strings"))
Calls of the form thoth("name value"),
thoth("name", "value"), thoth("name", value),
thoth(name = value), thoth("name" = value),
thoth(name = "value") and thoth("name" = "value")
are largely interchangeable.
The driver and dsn parameters accept file paths as their values. For all other parameters, values are extracted from within any specified files.
Assigning visible TRUE authorises modification of the global
prompt option.
When running R.exe, Rterm.exe or Rgui.exe on a
“Windows” operating system, this also authorises modification of the
R window title.
# Change the interface.
thoth("interface", "H")# Change it back.
H(interface = "thoth")
If the proposed new interface name already belongs to some other object within the R search path, then the change request will be denied (unless that name is “remove”, in which case the current interface function will be deleted).
A successful change deletes the previous interface.
# See the data sources and their interfaces.
thoth("sources")
This is equivalent to calling sqrlSources().
# Get help on 'thoth'.
thoth("help")# Alternative form.
thoth("?")
The above calls will attempt to provide help tailored for the specific
interface, and will fall back to these notes (help(sqrlUsage) or
?sqrlUsage) should that fail.
Either of the commands text or html may be appended to
help if a specific output format is required.
# Deregister the associated source.
thoth("remove")
This closes any open connection to the data source, deletes the
(thoth) interface function, and deregisters the source from SQRL.
Once you have a named interface, created either automatically (on loading of the
SQRL namespace) or manually (via
sqrlSource()), it can be used to communicate with
the associated data source.
Connection handles and communication parameters are managed under the hood.
Subsequent sections provide usage examples for an interface called thoth.
The names of your own interface functions can be discovered by calling
sqrlSources().