Learn R Programming
RODBC (version 0.9-1)

odbc: Low-level ODBC functions

Description

Rfunctions which talk directly to the ODBC interface.

Usage

odbcGetInfo(channel)
odbcTables(channel)

odbcColumns(channel, table)
odbcPrimaryKeys(channel, table)

odbcGetErrMsg(channel)
odbcClearError(channel)

odbcQuery(channel, query)
odbcFetchRow(channel)
odbcFetchRows(channel, max = 0, transposing = FALSE, buffsize = 1000, 
              nullstring = "NA") 
odbcColData(channel)
odbcNumRows(channel)
odbcNumFields(channel)
odbcNumCols(channel)

Arguments

channel
connection handle returned by odbcConnect()
query
any valid SQL statement
table
a database table name accessible from the connected dsn. This can be either a character string or an (unquoted) symbol.
max
limit on the number of rows to fetch, with 0 indicating no limit.
transposing
return rows and columns transposed, as character matrix.
buffsize
the number of rows to be transferred at a time.
nullstring
character string to be used when reading SQL_NULL_DATA items from the database.

Value

  • Most return -1 on failure, indicating that a message is waiting for odbcGetErrMsg. The exception is that an invalid channel returns -2. Examples are present in the sql.R code.

Details

odbcGetInfo reports the details provided by the ODBC driver and database about their makes and versions. odbcFetchRows returns a matrix of the pending rowset in $data limited to max rows if max is greater than 0. buffsize may be increased from the default of 1000 (rows*cols) for increased performance in a large dataset. This only has an effect with servers that do not return the number of rows affected by a query e.g. MSAccess, MSSqlServer. If transposing is TRUE the matrix will be transposed. This function is called by sqlGetResults, which then converts the matrix to a dataframe. This step incurs a significant performance penalty and working with matrices is much faster in large rowsets. odbcFetchRow is a deprecated function that returns a vector comprising the next row of the waiting rowset.

See Also

sqlQuery, odbcConnect