| odbcConnect {RODBC} | R Documentation |
Open connections to ODBC databases.
odbcConnect(dsn, uid = "", pwd = "", ...)
odbcDriverConnect(connection = "", case, believeNRows = TRUE,
colQuote, tabQuote = colQuote)
odbcReConnect(channel, case, believeNRows)
odbcConnectAccess(access.file, uid = "", pwd = "", ...)
odbcConnectDbase(dbf.file, ...)
odbcConnectExcel(xls.file, readOnly = TRUE, ...)
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 Oracle and Sybase, apparently. |
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 for both ends, or a length-2
character string giving the beginning and ending quotes. ANSI SQL
uses doublequotes, 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 doublequote otherwise.
The Access, DBase and Excel wrappers set tabQuote = c("[", "]").
|
access.file, dbf.file, xls.file |
file of an appropriate type. |
readOnly |
logical: should the connection be read-only? |
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)). 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.
(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.)
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
also 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.
The Excel driver by default makes read-only connections, and has only limited abilities to create a new worksheet or to change a worksheet.
A ‘table’ in an Excel ‘database’ (spreadsheet) can be
either a ‘named range’
(http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q195951&)
or a worksheet: the latter have table name the name of the worksheet
with $ appended (and such names may contain spaces and other
characters not allowed in SQL table names). RODBC will generally
allow worksheets to be referred to with or without the trailing
$, but this does need to be taken into account in SQL queries
(where non-standard table names are escaped by enclosing them in
square brackets).
It assumes that the first row of the table in the worksheet contains
column headings: the driver parameter FirstRowHasNames=0 is
supposed to turn this off (giving column names F1 ...) but it
is broken in many versions of the drivers, including those current in
WIndows XP SP2.
A connection that allows modification can be created via
odbcConnectExcel(readOnly = FALSE) or directly (see the examples).
Howver, the Excel ODBC driver does not support deletion (including SQL
DROP, DELETE, UPDATE and ALTER
statements). In particular, sqlDrop will remove the data
in a worksheet but not the worksheet itself.
sqlSave can be used to create new worksheets (and it
also creates a marked range for the contents of the worksheet) but not
to overwrite an existing worksheet, but can be
sqlUpdate to update the contents of an existing worksheet.
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; ReadOnly=False")
channel3 <- odbcConnectExcel("hills.xls")
# re-connection
odbcCloseAll()
channel <- odbcReConnect(channel) # must re-assign as the data may well change
## End(Not run)