Open connections to ODBC databases.
odbcConnect(dsn, uid = "", pwd = "", ...)odbcDriverConnect(connection = "", case, believeNRows = TRUE,
colQuote, tabQuote = colQuote,
interpretDot = TRUE, DBMSencoding = "",
rows_at_time = 100, readOnlyOptimize = FALSE)
odbcReConnect(channel, ...)
odbcConnectAccess(access.file, uid = "", pwd = "", ...)
odbcConnectAccess2007(access.file, uid = "", pwd = "", ...)
odbcConnectDbase(dbf.file, ...)
odbcConnectExcel(xls.file, readOnly = TRUE, ...)
odbcConnectExcel2007(xls.file, readOnly = TRUE, ...)
A non-negative integer which is used as handle if no error occurred,
-1 otherwise. A successful return has class "RODBC", and
attributes including
the full ODBC connection string.
the value of case.
a numeric ID for the channel.
the value of believeNRows.
the value of rows_at_time.
character string. A registered data source name.
UID and password for authentication (if required).
character string. See your ODBC documentation for the format.
further arguments to be passed to odbcDriverConnect.
Controls case changes for different DBMS engines. See ‘Details’.
RODBC connection object returned by odbcConnect.
logical. Is the number of rows returned by the ODBC connection believable? Not true for some Oracle and Sybase drivers, apparently, nor for Actual Technologies' SQLite driver for Mac OS X.
how to quote column (table) names in SQL statements. Can be of length 0 (no quoting), a length--1 character vector giving the quote character to be used at both ends, or a length--2 character vector giving the beginning and ending quotes. ANSI SQL uses double quotes, but the default mode for a MySQL server is to use backticks.
The defaults are backtick (`) if the DBMS is identified
as "MySQL" by the driver, and double quote otherwise.
The Access, DBase and Excel wrappers set tabQuote = c("[", "]").
A user reported that the SAS ODBC driver required colQuote = NULL.
logical. Should table names of the form
qualifier.table be interpreted as table
table in schema qualifier (and for MySQL
‘schema’ means database)?
character string naming the encoding returned by
the DBMS. The default means the encoding of the locale R is running
under. Values other than the default require iconv to
be available: it always is from R 2.10.0, otherwise see
capabilities.
The default number of rows to fetch at a time,
between 1 and 1024. Not all drivers work correctly with values > 1: see
sqlQuery.
logical: should the connection be optimized for read-only access?
file of an appropriate type.
logical: should the connection be read-only?
Michael Lapsley, Brian Ripley
odbcConnect establishes a connection to the specified DSN, and
odbcDriverConnect allows a more flexible specification via a
connection string. odbcConnect uses the connection string
"DSN=dsn;UID=uid;PWD=pwd",
omitting the last two components if they are empty.
See the examples for other uses of connection strings.
Under the Windows GUI, specifying an incomplete connection, for
example the default "", will bring up a dialog box to complete the
information required. (This does not work from Rterm.exe
unless a driver is specified, a Windows restriction.)
For DBMSs that translate table and column names case must
be set appropriately. Allowable values are "nochange",
"toupper" and "tolower" as well as the names of
databases where the behaviour is known to us (currently
"mysql", which maps to lower case on Windows but not on Linux,
"postgresql" (lower), and "msaccess" (nochange)). If
case is not specified, the default is "nochange" unless
the appropriate value can be figured out from the DBMS name reported
by the ODBC driver. It is likely that "toupper" is desirable
on IBM's DB2, but this is not enforced.
(The DBase driver is unusual: it preserves names on reading, but
converts both table and column names to upper case on writing, and
truncates table names to 8 characters. RODBC does not attempt to do
any mapping for that driver.)
Note that readOnlyOptimize may do nothing, and is not
guaranteed to enforce read-only access. With drivers that support it,
it is used to optimize locking strategies, transaction management and
so on. It does make access to Mimer read-only, and has no effect on
MySQL.
Function odbcReConnect re-connects to a database using the
settings of an existing (and presumably now closed) channel object.
Arguments given in the original call can be overridden as needed.
Note that if a password is supplied (either as a pwd argument
or as part of the DSN) it may be stored in the
connection.string element of the return value, but the value is
(from RODBC 1.3-0) replaced by ******. (This will break
odbcReConnect.)
odbcConnectAccess, odbcConnectDbase and
odbcConnectExcel are convenience wrappers to generate
connection strings for those file types. The files given can be
relative to the R working directory or absolute paths (and it seems
also relative to the user's home directory). The file name can be
omitted, which will on Rgui bring up a dialog box to search for
a file.
Note: they will only work with English-language 32-bit versions
of the Microsoft drivers, which may or may not be installed in other
locales, and are not usable from 64-bit R. The 2007 versions
work with the drivers which are installed with Office 2007/2010 and
give access to formats such as *.xlsx and *.accdb.
These drivers are also available separately and there is a 64-bit
version: see the package manual. (You must have the 32-bit drivers
when using 32-bit R and the 64-bit drivers when using 64-bit R:
otherwise there will be a cryptic message about a driver not being
found. And the 64-bit drivers cannot be installed alongside 32-bit
Microsoft Office, and vice versa.
See the package manual for some of the peculiarities of the Excel
drivers. readOnly = TRUE may allow very limited changes (to
insert and update rows).
If it is possible to set the DBMS or ODBC driver to communicate in the character set of the R session then this should be done. For example, MySQL can set the communication character set via SQL, e.g. SET NAMES 'utf8'.
odbcClose, sqlQuery, odbcGetInfo