odbcConnect {RODBC} | R Documentation |
Open connections to ODBC databases.
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)
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. |
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.
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.
Michael Lapsley, Brian Ripley
odbcClose
, sqlQuery
, odbcGetInfo
## 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)