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)
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.
|
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 sqlSave . Set to "no" to suppress this. |
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.
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.
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.
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 . |
Michael Lapsley, Brian Ripley
odbcClose
, sqlQuery
, odbcGetInfo
## Not run: # MySQL channel <- odbcConnect("test", uid="ripley", pwd="secret") # PostgreSQL channel <- odbcConnect("pg", uid="ripley", pwd="secret", case="postgresql") # re-connection odbcCloseAll() channel <- odbcReConnect(channel) # must re-assign as the data may well change ## End(Not run)