| sqlQuery {RODBC} | R Documentation |
Submit an SQL query to an ODBC database, and retrieve the results.
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"))
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. |
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 odbcQuery and used to retrieve waiting
results into a data frame. Its main use is with max set to non
zero 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. It makes use of the setting of
options("dec") to determine the character used in setting
decimal points. This is set to "." when RODBC is initialized
if it is not already set.
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 (of length up to 256), and then converted
by type.convert as controlled by the as.is
argument. Note that almost all callers set as.is = TRUE.
Using buffsize will yield a
marginal increase in speed if set to more than the maximum number of
rows where believeNRows = FALSE.
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).
Michael Lapsley and Brian Ripley
odbcConnect, sqlFetch,
sqlSave, sqlTables, odbcQuery
library(RODBC)
data(USArrests)
channel <- odbcConnect("test", "", "") # userId and password
sqlSave(channel, USArrests, rownames = "State", verbose = TRUE)
options(dec=".") # this is the default decimal point
## note case of State, Murder, rape are DBMS-dependent.
sqlQuery(channel, "select State, Murder from USArrests where rape > 30 order by Murder")
odbcClose(channel)