sqlFetch: Reading Tables from ODBC Databases

In RODBC: ODBC Database Access

Reading Tables from ODBC Databases

Description

Read some or all of a table from an ODBC database into a data frame.

Usage

sqlFetch(channel, sqtable, ..., colnames = FALSE, rownames = TRUE)

sqlFetchMore(channel, ..., colnames = FALSE, rownames = TRUE)

Arguments

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.

Details

Note the ‘table’ includes whatever table-like objects are provided by the DBMS, in particular views and system tables.

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, unless the connection was opened with interpretDot = FALSE.

Useful additional parameters to pass to sqlQuery or sqlGetResults include

max:

limit on the number of rows to fetch, with 0 (the default) indicating no limit.

nullstring:

character string to be used when reading SQL_NULL_DATA character items from the database: default NA_character_.

na.strings:

character string(s) to be mapped to NA when reading character data: default "NA".

as.is:

as in sqlGetResults.

dec:

The character for the decimal place to be assumed when converting character columns to numeric.

rows_at_time:

Allow for multiple rows to be retrieved at once. See sqlQuery.

Value

A data frame on success, or a character or numeric error code (see sqlQuery).

Note

If the table name desired is not a valid SQL name (alphanumeric plus _) and these functions are not able to interpret the name, you can use sqlQuery with whatever quoting mechanism your DBMS vendor provides (e.g. [ ] on some Microsoft products and backticks on MySQL).

Author(s)

Michael Lapsley and Brian Ripley

See Also

sqlSave, sqlQuery, odbcConnect, odbcGetInfo

Examples

## Not run: 
channel <- odbcConnect("test")
sqlSave(channel, USArrests)
sqlFetch(channel, "USArrests") # get the lot
sqlFetch(channel, "USArrests", max = 20, rows_at_time = 10)
sqlFetchMore(channel, max = 20)
sqlFetchMore(channel) # get the rest
sqlDrop(channel, "USArrests") 
close(channel)

## End(Not run)

RODBC documentation built on Nov. 25, 2023, 5:07 p.m.