RODBC (version 1.0-2)

odbcConnect: 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)
#ifdef windows
odbcConnectAccess(access.file, uid = "", pwd = "")
odbcConnectDbase(dbf.file)
odbcConnectExcel(xls.file)
#endif

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, con

RODBC connection object returned by odbcConnect.

believeNRows

logical. Is the number of rows returned by the ODBC connection believable? Not true for Oracle, apparently.

access.file, dbf.file, xls.file

file of an appropriate type.

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.

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". See the examples for other uses of connection strings.

#ifdef windows Under the Windows GUI, specifying an incomplete connection, for example "", will bring up a dialog box to complete the information required.

#endif 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. #ifdef windows

odbcConnectAccess, odbcConnectDbase and odbcConnectExcel are convenience wrappers to generate connection strings for those file types. The files given can be relative to the Rworking 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. #endif

See Also

odbcClose, sqlQuery, odbcGetInfo

Examples

#ifdef unix
# MySQL
channel <- odbcConnect("test", uid="ripley", pwd="secret")
# PostgreSQL
channel <- odbcConnect("pg", uid="ripley", pwd="secret", case="postgresql")
#endif
#ifdef windows
# interactive specification
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("bdr.xls")
#endif

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