odbcConnect | R Documentation |
Open connections to ODBC databases.
odbcConnect(dsn, uid = "", pwd = "", ...)
odbcDriverConnect(connection = "", case, believeNRows = TRUE,
colQuote, tabQuote = colQuote,
interpretDot = TRUE, DBMSencoding = "",
rows_at_time = 100, readOnlyOptimize = FALSE)
odbcReConnect(channel, ...)
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 |
case |
Controls case changes for different DBMS engines. See ‘Details’. |
channel |
RODBC connection object returned by |
believeNRows |
logical. Is the number of rows returned by the ODBC connection believable? Not true for some Oracle and Sybase drivers, apparently, nor for Actual Technologies' SQLite driver for Mac OS X. |
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 to be used at both ends, or a length–2 character vector 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 |
interpretDot |
logical. Should table names of the form
|
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 |
rows_at_time |
The default number of rows to fetch at a time,
between 1 and 1024. Not all drivers work correctly with values > 1: see
|
readOnlyOptimize |
logical: should the connection be optimized for read-only access? |
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 components if they are empty.
For DBMSs 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), 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. It is likely that "toupper"
is desirable
on IBM's DB2, but this is not enforced.
Note that readOnlyOptimize
may do nothing, and is not
guaranteed to enforce read-only access. With drivers that support it,
it is used to optimize locking strategies, transaction management and
so on. It does make access to Mimer read-only, and has no effect on
MySQL.
Function odbcReConnect
re-connects to a database using the
settings of an existing (and presumably now closed) channel object.
Arguments given in the original call can be overridden as needed.
Note that if a password is supplied (either as a pwd
argument
or as part of the DSN) it may be stored in the
connection.string
element of the return value, but the value is
(from RODBC 1.3-0) replaced by ******
. (This will break
odbcReConnect
.)
If it is possible to set the DBMS or ODBC driver 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 |
id |
a numeric ID for the channel. |
believeNRows |
the value of |
rows_at_time |
the value of |
Several errors which have been reported as bugs in RODBC 1.3-0
which were in fact ODBC driver errors that can be circumvented by
setting rows_at_time = 1
(and the warning under that argument has
always been there). The drivers involved have been third-party
Oracle drivers and old SQL Server drivers.
Michael Lapsley, Brian Ripley
odbcClose
, sqlQuery
, odbcGetInfo
## Not run:
# MySQL
channel <- odbcConnect("test", uid="ripley", pwd="secret")
# PostgreSQL: 'case' should be detected automatically
channel <- odbcConnect("pg", uid="ripley", pwd="secret", case="postgresql")
# re-connection
odbcCloseAll()
channel <- odbcReConnect(channel) # must re-assign as the data may change
## End(Not run)
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.