odbcConnect {RODBC}R Documentation

ODBC Open Connections

Description

Open connections to ODBC databases.

Usage

odbcConnect(dsn, uid = "", pwd = "", case = "nochange",
            believeNRows = TRUE)

odbcDriverConnect(connection = "", case = "nochange",
                  believeNRows = TRUE)

odbcReConnect(channel, case, believeNRows)

odbcConnectAccess(access.file, uid = "", pwd = "")
odbcConnectDbase(dbf.file)
odbcConnectExcel(xls.file)

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.
case Controls case changes for different RDBMS engines.
channel RODBC connection object returned by odbcConnect.
believeNRows logical. Is the number of rows returned by the ODBC connection believable? Not true for Oracle and Sybase, apparently.
access.file, dbf.file, xls.file file of an appropriate type.

Details

odbcConnect establishes a connection to the 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 comments 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 databases that translate table and column names the case must be set as appropriate. 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), "oracle" (upper) and "msaccess" (nochange)).

Function odbcReConnect re-connects to a database using the settings of an existing (and presumably now closed) channel object. Arguments case and believeNRows are taken from the object, but can be overridden by supplying those arguments.

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 that the drivers will also look relative to the user's home directory). Note: they will only work with English versions of the Microsoft drivers, which may or may not be installed in other locales. The file name can be omitted which will bring up a dialog box to search for a file.

Value

A non-negative integer which is used as handle if no error occurred, -1 otherwise. A successful return has class "RODBC", and attribute "connection.string" giving the full ODBC connection string.

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("DRIVER=Microsoft Excel Driver (*.xls);DBQ=C:\bdr\hills.xls")
channel3 <- odbcConnectExcel("hills.xls")

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

[Package RODBC version 1.1-7 Index]