odbc: Low-level ODBC functions

In RODBC: ODBC Database Access

Low-level ODBC functions

Description

R functions which talk directly to the ODBC interface.

Usage

odbcTables(channel, catalog = NULL, schema = NULL,
           tableName = NULL, tableType = NULL, literal = FALSE)

odbcQuery(channel, query, rows_at_time = attr(channel, "rows_at_time"))
odbcFetchRows(channel, max = 0, buffsize = 1000,
              nullstring = NA_character_, believeNRows = TRUE)

odbcGetErrMsg(channel)
odbcClearError(channel)

Arguments

channel	connection handle as returned by odbcConnect, of class "RODBC".
catalog, schema, tableName, tableType	NULL or character: whether these do anything depends on the ODBC driver. The first three can be length-one character vectors, and tableType can specify zero or more types.
literal	logical: should arguments be interpreted literally or including wildcards?
query	any valid SQL statement.
rows_at_time	The number of rows to fetch at a time, between 1 and 1024. Not all drivers work correctly with values > 1: see sqlQuery.
max	limit on the number of rows to fetch, with 0 indicating no limit.
buffsize	the number of records to be transferred at a time.
nullstring	character string to be used when reading SQL_NULL_DATA items in a column transferred as character.
believeNRows	logical. Is the number of rows returned by the ODBC connection believable?

Details

These are low-level functions called by sqlTables, sqlQuery, sqlGetResults and similar high-level functions. They are likely to be confind to the RODBC namespace in the near future.

odbcTables enquires about the tables on the connected database. Whether arguments after the first do anything and what they do depends on the ODBC driver: see the help on sqlTables for some driver-specific details.

odbcFetchRows returns a data frame of the pending rowset, limited to max rows if max is greater than 0.

buffsize may be increased from the default of 1000 rows for increased performance on a large dataset. This only has an effect when max = 0 and believeNRows = FALSE (either for the ODBC connection or for this function call), in which case buffsize is used as the initial allocation length of the R vectors to hold the results. (Values of less than 100 are increased to 100.) If the initial size is too small the vector length is doubled, repeatedly if necessary.

Value

odbcGetErrMsg returns a (possibly zero-length) character vector of pending messages.

odbcClearError returns nothing, invisibly.

The otheres return 1 on success and -1 on failure, indicating that a message is waiting to be retrieved odbcGetErrMsg. odbcFetchRows may return -2 indicating "No Data", the message that would be returned by odbcGetErrMsg.

Author(s)

Michael Lapsley and Brian Ripley

See Also

sqlQuery, odbcConnect, odbcGetErrMsg.

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