sqlQuery {RODBC} | R Documentation |
Submit an SQL query to an ODBC database, and retrieve the results.
sqlQuery(channel, query, errors = TRUE, ..., rows_at_time = 1) sqlGetResults(channel, as.is = FALSE, errors = FALSE, max = 0, buffsize = 1000, nullstring = NA_character_, na.strings = "NA", believeNRows = TRUE, dec = getOption("dec"), stringsAsFactors = default.stringsAsFactors())
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 . |
rows_at_time |
The number of rows to fetch at a time, up to 1024. Not all drivers work correctly with values > 1. See ‘Details’. |
as.is |
which (if any) columns returned as character should be
converted to another type? Allowed values are as for
read.table . See ‘details’. |
max |
limit on the number of rows to fetch, with 0
indicating no limit. |
buffsize |
an initial guess at the number of rows, used if
max = 0 and believeNRows == FALSE . |
nullstring |
character string to be used when reading
SQL_NULL_DATA character items from the database. |
na.strings |
character vector of strings 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 overridden. |
dec |
The character for the decimal place to be assumed when converting character columns to numeric. |
stringsAsFactors |
logical: should columns returned as character and not
excluded by as.is and not converted to anything else be
converted to factors? |
sqlQuery
is the workhorse function of RODBC. It sends the SQL
statement query
to the server, using connection channel
returned by odbcConnect
, and retrieves (some or all of)
the results via sqlGetResults
.
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.
This paragraph applies only to SQL data types which are returned by
ODBC as character vectors. If odbcConnect
set
DBMSencoding
to a non-empty value, the character strings are
re-encoded. Then 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 the
"Date"
or "POSIXct"
class. (Some drivers
seem to confuse times with dates, so times may get converted
too. Also, some DBMSs (e.g. Oracle's) idea of date
is a
date-time.) Remaining cases 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 when believeNRows =
FALSE
. (If set too small it can result in unnecessarily high
memory use as the buffers will need to be expanded.)
Modern drivers should work (and work faster, especially if
communicating with a remote machine) with rows_a_time = 1024
.
However, some drivers may mis-fetch multiple rows, so the default is
1
.
A data frame (possibly with 0 rows) on success. On error,
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 statements do return no data).
Michael Lapsley and Brian Ripley
odbcConnect
, sqlFetch
,
sqlSave
, sqlTables
, odbcQuery
## Not run: 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, ## and at least one Oracle driver needs column and table names double-quoted. sqlQuery(channel, paste("select State, Murder from USArrests", "where Rape > 30 order by Murder")) close(channel) ## End(Not run)