sqrlUsage: How to Use the Interface Functions

Description

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.

Arguments

Opening and Closing

# Open a connection to the data source.
chaos()
# Alternative method (explicit form).
chaos("open")
# Doing this is fine (the channel survives).
rm(list = ls(all.names = TRUE))
# Check if the connection is open.
chaos("isopen")
# Close the connection.
chaos("close")

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).

Submitting Queries

# Submit a query.
chaos("select 1")
# Submit another query.
chaos("select ", sample(6, 1), " from dual")
# Submit a query from file.
chaos("my/", "file.sql")
# Submit a parameterised query from file.
chaos("emissions.sqrl", make = "VEB", model = "Trabant 601")

If necessary, a connection channel will be opened beforehand. The connection will remain open afterwards.

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 chaos("use database") submits the content of that file (rather than the apparent command).

Querying Metadata

# Get information on source data types.
chaos("typeinfo")
# Get a list of source tables.
chaos("tables")
# Get information on the columns of a particular table.
chaos("columns", "my.table")

The typeinfo, tables, and columns commands are simple wrappers to RODBC:sqlTypeInfo, RODBC:sqlTables, and RODBC:sqlColumns (respectively).

Reviewing Settings

# Get the associated source definition.
chaos("source")
# Get the value of one named parameter.
chaos("uid")
# Alternative method (pings the source).
chaos()$uid
# List the values of all parameters.
chaos("config")

Passwords are returned obliterated.

Setting Parameters

# Enable visible indication of open connections.
chaos("visible", TRUE)
# Define the ping statement for the data source.
chaos("ping", "use database")
# Do not convert strings to factors.
chaos("stringsAsFactors FALSE")
# Set (opening and closing) table-name quotes.
chaos(tabQuote = c("`", "'"))
# Setting multiple parameters at once.
chaos(as.is = TRUE, na.strings = c("NA", "-", ""))
# Set one (named) parameter from a file.
chaos("ping" = "path/to/file")
# Import an entire configuration file.
chaos("config", "path/", "to/", "file")
# Reset parameters to their default values.
chaos("reset", c("as.is", "na.strings"))

Calls of the form chaos("name value"), chaos("name", "value"), chaos("name", value), chaos(name = value), chaos("name" = value), chaos(name = "value") and chaos("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.

Changing the Interface

# Change the interface.
chaos("interface", "K")
# Change it back.
K(interface = "chaos")

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.

Listing Data Sources

# See the data sources and their interfaces.
chaos("sources")

This is equivalent to calling sqrlSources().

Getting Help

# Get help on 'chaos'.
chaos("help")
# Alternative form.
chaos("?")

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.

Removing the Source

# Deregister the associated source.
chaos("remove")

This closes any open connection to the data source, deletes the (chaos) interface function, and deregisters the source from SQRL.

Details

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 chaos. The names of your own interface functions can be discovered by calling sqrlSources().