odbcConnect {RODBC} | R Documentation |
Open connections to ODBC databases.
odbcConnect(dsn, uid = "", pwd = "", ...) odbcDriverConnect(connection = "", case, believeNRows = TRUE, colQuote, tabQuote = colQuote, DBMSencoding = "", rows_at_time = 1000, bulk_add = NULL) odbcReConnect(channel, case, believeNRows) odbcConnectAccess(access.file, uid = "", pwd = "", ...) odbcConnectAccess2007(access.file, uid = "", pwd = "", ...) odbcConnectDbase(dbf.file, ...) odbcConnectExcel(xls.file, readOnly = TRUE, ...) odbcConnectExcel2007(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, nor for MySQL Connector/ODBC 5.00.11. |
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 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("[", "]") .
|
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: see capabilities . |
rows_at_time |
The number of rows to fetch at a time, up to 1024. Not
all drivers work correctly with values > 1: see sqlQuery . |
bulk_add |
if "yes" , SQLBulkOperations will be used
in future (not currently). Set to "no" to suppress this. |
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 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 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 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), "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-language 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. 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 ‘README’.
If it is possible to set the DBMS 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'.
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 . |
The Excel drivers by default makes read-only connections, and have 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 is assumed 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.
There are at least two known problems with reading columns that do not
have a format set before data entry, and so start with format
‘General’. First, the driver uses the first few rows to
determined the column type, and is over-fond of declaring
‘Numeric’ even when there are non-numeric entries. The default
number of rows consulted is 8, but attempt to change this in the DSN
setup are ignored. Second, if a column is declared as ‘Text’,
numeric entries will be read as SQL nulls and hence R NA
s.
Unfortunately, in neither case does reformatting the column help.
A connection that allows modification can be created via
odbcConnectExcel(readOnly = FALSE)
or directly (see the examples).
However, 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. One can use
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)