sqlFetch {RODBC} | R Documentation |
Read a table of an ODBC database into a data frame.
sqlFetch(channel, sqtable, ..., colnames = FALSE, rownames = TRUE) sqlFetchMore(channel, ..., colnames = FALSE, rownames = TRUE)
channel |
connection handle returned by odbcConnect . |
sqtable |
a database table name accessible from the connected DSN. This should be either a literal character string or a character vector of length 1. |
... |
additional arguments to be passed to
sqlQuery or sqlGetResults . See
‘Details’. |
colnames |
logical: retrieve column names from first row of table?
(For use when sqlSave(colnames = TRUE) was used.) |
rownames |
either logical or character.
If logical, retrieve row names from the first column
(rownames ) in the table? If character, the column name to
retrieve them from. |
sqlFetch
by default retrieves the the entire contents of the table
sqtable
. Rownames and column names are restored as indicated
(assuming that they have been placed in the table by the corresponding
arguments to sqlSave
).
Alternatively, sqlFetch
can fetch the first max
rows, in
which case sqlFetchMore
will retrieve further result rows,
provided there has been no other ODBC query on that channel in the
meantime.
These functions try to cope with the peculiar way the Excel ODBC
driver handles table names, and to quote Access table names which
contain spaces. Dotted table names, e.g. myschema.mytable
, are
allowed on systems that support them, but the existence of the table
will not be checked.
Useful additional parameters to pass to sqlQuery
or
sqlGetResults
include
max
:0
(the default) indicating no limit.nullstring
:SQL_NULL_DATA
character items from the database:
default NA
.na.strings
:NA
when reading character data: default "NA"
.as.is
:sqlGetResults
.dec
:rows_at_time
:1
.
A data frame on success, or a character or numeric error code (see
sqlQuery
).
If the table name desired is not a valid SQL name (alphanumeric plus
_
), you can use sqlQuery
with whatever quoting
mechanism your DBMS vendor provides (e.g. [ ]
on Microsoft
products and backticks on recent versions of MySQL).
Michael Lapsley and Brian Ripley
sqlSave
, sqlQuery
,
odbcConnect
, odbcGetInfo
## Not run: channel <- odbcConnect("test") sqlSave(channel, USArrests) sqlFetch(channel, "USArrests") # get the lot sqlFetch(channel, "USArrests", max=20) sqlFetchMore(channel, max=20) sqlFetchMore(channel) # get the rest sqlDrop(channel, "USArrests") close(channel) ## End(Not run)