sqlQuery {RODBC}R Documentation

Query an ODBC Database

Description

Submit an SQL query to an ODBC database, and retrieve the results.

Usage

sqlQuery(channel, query, errors = TRUE, ...)

sqlGetResults(channel, as.is = FALSE,  errors = FALSE,
              max = 0, buffsize = 1000,
              nullstring = NA, na.strings = "NA",
              believeNRows = TRUE, dec = getOption("dec"))

Arguments

channel connection handle as returned by odbcConnect.
query any valid SQL statement
errors if TRUE halt and display error, else return -1
... additional arguments to be passed to sqlGetResults.
as.is which (if any) character columns should be converted, as in read.table? See the details.
max limit on the number of rows to fetch, with 0 indicating no limit.
buffsize the number of rows to be transferred at a time.
nullstring character string to be used when reading SQL_NULL_DATA character items from the database.
na.strings character string(s) to be mapped to NA when reading character data.
believeNRows logical. Is the number of rows returned by the ODBC connection believable? This might already be set to false when the channel was opened, and can that setting cannot be overriden.
dec The character for the decimal place to be assumed when converting character columns to numeric.

Details

sqlQuery is the workhorse function of RODBC. It sends the SQL statement query to the server, using connection channel returned by odbcConnect.

SQL beginners should note that the term 'Query' includes any valid SQL statement including table creation, alteration, updates etc as well as SELECTs. The sqlQuery command is a convenience wrapper that calls first odbcQuery and then sqlGetResults. If finer-grained control is needed, for example over the number of rows fetched, these functions should be called directly or additional arguments passed to sqlQuery.

sqlGetResults is a mid-level function. It should be called after a call to sqlQuery or odbcQuery and used to retrieve waiting results into a data frame. Its main use is with max set to non-zero when it will retrieve the result set in batches with repeated calls. This is useful for very large result sets which can be subjected to intermediate processing.

Where possible sqlGetResults transfers data directly: this happens for double, real, integer and smallint columns in the table. All other SQL data types are converted to character strings by the ODBC interface. If the as.is is true for a column, it is returned as character. Otherwise (where detected) date, datetime and timestamp values are converted to "Date" and "POSIXct" values respectively. (Some drivers seem to confuse times with dates, so times may get converted too.) Other types are converted by R using type.convert. When character data are to be converted to numeric data, the setting of options("dec") to map the character used up the ODBC driver in setting decimal points—this is set to a locale-specific value when RODBC is initialized if it is not already set.

Using buffsize will yield a marginal increase in speed if set to no less than the maximum number of rows where believeNRows = FALSE.

Value

A data frame (possibly with 0 rows) on success. If errors = TRUE, a character vector of error message(s), otherwise error code -1 (general, call odbcGetErrMsg for details) or -2 (no data, which may not be an error as some SQL commands do return no data).

Author(s)

Michael Lapsley and Brian Ripley

See Also

odbcConnect, sqlFetch, sqlSave, sqlTables, odbcQuery

Examples

## Not run: 
data(USArrests) # R < 2.0.0 only
channel <- odbcConnect("test")
sqlSave(channel, USArrests, rownames = "State", verbose = TRUE)
# options(dec=".") # optional, if DBMS is not locale-aware
## note case of State, Murder, rape are DBMS-dependent.
sqlQuery(channel, paste("select State, Murder from USArrests",
                        "where Rape > 30 order by Murder"))
close(channel)
## End(Not run)

[Package RODBC version 1.1-3 Index]