odbcConnect {RODBC}R Documentation

ODBC Open Connections

Description

Open connections to ODBC databases.

Usage

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, ...)

Arguments

dsn character string. A registered data source name.
uid, pwd UID and password for authentication (if required).
connection character string. See your ODBC documentation for the format.
... further arguments to be passed to odbcDriverConnect.
case Controls case changes for different DBMS engines. See ‘Details’.
channel RODBC connection object returned by odbcConnect.
believeNRows 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.
colQuote, tabQuote 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("[", "]").
interpretDot logical. Should table names of the form qualifier.table be interpreted as table table in schema qualifier (and for MySQL ‘schema’ means database)?
DBMSencoding 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.
rows_at_time The default number of rows to fetch at a time, between 1 and 1024. Not all drivers work correctly with values > 1: see sqlQuery.
readOnlyOptimize logical: should the connection be optimized for read-only access?
access.file, dbf.file, xls.file file of an appropriate type.
readOnly logical: should the connection be read-only?

Details

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). Note: they will only work with English-language versions of the Microsoft drivers, which may or may not be installed in other locales. The file name can be omitted, which will on Rgui bring up a dialog box to search for a file. The 2007 versions work with the drivers which are installed with Office 2007 and give access to formats such as ‘*.xlsx’ and ‘*.accdb’. These drivers are also available separately: see the package manual.

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'.

Value

A non-negative integer which is used as handle if no error occurred, -1 otherwise. A successful return has class "RODBC", and attributes including

connection.string the full ODBC connection string.
case the value of case.
id a numeric ID for the channel.
believeNRows the value of believeNRows.
rows_at_time the value of rows_at_time.

Note

Several errors which have been reported as bugs in RODBC 1.3-0 which were in fact ODBC driver errors that can be circumvented by setting rows_at_time = 1 (and the warning under that argument has always been there). The drivers involved have been third-party Oracle drivers and old SQL Server drivers.

Author(s)

Michael Lapsley, Brian Ripley

See Also

odbcClose, sqlQuery, odbcGetInfo

Examples

## Not run: 
# interactive specification under RGui
channel <- odbcDriverConnect("")

# MySQL on Windows -- MySQL maps to lower case on Windows only
channel <- odbcConnect("testdb", uid="ripley", case="tolower")

# Access
channel <- odbcConnect("testacc") # if this was set up as a DSN
channel2 <- odbcConnectAccess("test.mdb", uid="ripley")

# Excel
channel <- odbcConnect("bdr.xls") # if this was set up as a DSN
channel2 <-
 odbcDriverConnect(paste("DRIVER=Microsoft Excel Driver (*.xls)",
                         "DBQ=D:\bdr\hills.xls",
                         "ReadOnly=False", sep = ";"))
## or "DRIVER=Microsoft Excel Driver (*.xls *.xlsx, *.xlsm, *.xlsb)"
channel3 <- odbcConnectExcel("hills.xls")

# re-connection
odbcCloseAll()
channel <- odbcReConnect(channel) # must re-assign as the data may change
## End(Not run)

[Package RODBC version 1.3-1 Index]