-
Notifications
You must be signed in to change notification settings - Fork 107
/
dbConnect-OdbcDriver-method.Rd
182 lines (148 loc) · 8.18 KB
/
dbConnect-OdbcDriver-method.Rd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
% Generated by roxygen2: do not edit by hand
% Please edit documentation in R/dbi-driver.R
\name{odbc}
\alias{odbc}
\alias{dbConnect,OdbcDriver-method}
\alias{dbConnect}
\title{Connect to a database via an ODBC driver}
\usage{
odbc()
\S4method{dbConnect}{OdbcDriver}(
drv,
dsn = NULL,
...,
timezone = "UTC",
timezone_out = "UTC",
encoding = "",
name_encoding = "",
bigint = c("integer64", "integer", "numeric", "character"),
timeout = 10,
driver = NULL,
server = NULL,
database = NULL,
uid = NULL,
pwd = NULL,
dbms.name = NULL,
attributes = NULL,
interruptible = getOption("odbc.interruptible", interactive()),
.connection_string = NULL
)
}
\arguments{
\item{drv}{An \code{OdbcDriver}, from \code{odbc()}.}
\item{dsn}{The data source name. For currently available options, see the
\code{name} column of \code{\link[=odbcListDataSources]{odbcListDataSources()}} output.}
\item{...}{Additional ODBC keywords. These will be joined with the other
arguments to form the final connection string.
Note that ODBC parameter names are case-insensitive so that (e.g.) \code{DRV}
and \code{drv} are equivalent. Since this is different to R and a possible
source of confusion, odbc will error if you supply multiple arguments that
have the same name when case is ignored.
Any values containing a leading or trailing space, a \code{=}, \verb{;}, \verb{\{},
or \verb{\}} are likely to require quoting. Use \code{\link[=quote_value]{quote_value()}} for a fairly
standard approach or see your driver documentation for specifics.}
\item{timezone}{The server time zone. Useful if the database has an internal
timezone that is \emph{not} 'UTC'. If the database is in your local timezone,
set this argument to \code{\link[=Sys.timezone]{Sys.timezone()}}. See \code{\link[=OlsonNames]{OlsonNames()}} for a complete
list of available time zones on your system.}
\item{timezone_out}{The time zone returned to R. If you want to display
datetime values in the local timezone, set to \code{\link[=Sys.timezone]{Sys.timezone()}}.}
\item{encoding}{The text encoding used on the Database. If the database is
not using UTF-8 you will need to set the encoding to get accurate
re-encoding. See \code{\link[=iconvlist]{iconvlist()}} for a complete list of available encodings
on your system. Note strings are always returned \code{UTF-8} encoded.}
\item{name_encoding}{The text encoding for column names used on the
Database. May be different than the \code{encoding} argument. Defaults to
empty string which is equivalent to returning the column names without
performing any conversion.}
\item{bigint}{The R type that \code{SQL_BIGINT} types should be mapped to.
Default is \link[bit64:bit64-package]{bit64::integer64}, which allows the full range of 64 bit
integers.}
\item{timeout}{Time in seconds to timeout the connection attempt. Setting a
timeout of \code{Inf} or \code{NA} indicates no timeout. Defaults to 10 seconds.}
\item{driver}{The ODBC driver name or a path to a driver. For currently
available options, see the \code{name} column of \code{\link[=odbcListDrivers]{odbcListDrivers()}} output.}
\item{server}{The server hostname. Some drivers use \code{Servername} as the name
for this argument. Not required when configured for the supplied \code{dsn}.}
\item{database}{The database on the server. Not required when configured for
the supplied \code{dsn}.}
\item{uid}{The user identifier. Some drivers use \code{username} as the name
for this argument. Not required when configured for the supplied \code{dsn}.}
\item{pwd}{The password. Some drivers use \code{password} as the name
for this argument. Not required when configured for the supplied \code{dsn}.}
\item{dbms.name}{The database management system name. This should normally
be queried automatically by the ODBC driver. This name is used as the class
name for the OdbcConnection object returned from \code{\link[=dbConnect]{dbConnect()}}. However, if
the driver does not return a valid value, it can be set manually with this
parameter.}
\item{attributes}{A list of connection attributes that are passed
prior to the connection being established. See \link{ConnectionAttributes}.}
\item{interruptible}{Logical. If \code{TRUE} calls to \code{SQLExecute} and
\code{SQLExecuteDirect} can be interrupted when the user sends SIGINT ( ctrl-c ).
Otherwise, they block. Defaults to \code{TRUE} in interactive sessions, and
\code{FALSE} otherwise. It can be set explicitly either by manipulating this
argument, or by setting the global option \code{odbc.interruptible}.}
\item{.connection_string}{A complete connection string, useful if you are
copy pasting it from another source. If this argument is used, any
additional arguments will be appended to this string.}
}
\description{
The \code{dbConnect()} method documented here is invoked when \code{\link[DBI:dbConnect]{DBI::dbConnect()}}
is called with the first argument \code{odbc()}. Connecting to a database via
an ODBC driver is likely the first step in analyzing data using the odbc
package; for an overview of package concepts, see the \emph{Overview} section
below.
}
\section{Connection strings}{
Internally, \code{dbConnect()} creates a connection string using the supplied
arguments. Connection string keywords are driver-dependent; the arguments
documented here are common, but some drivers may not accept them.
Alternatively to configuring DSNs and driver names with the driver manager,
you can pass a complete connection string directly as the
\code{.connection_string} argument.
\href{https://www.connectionstrings.com}{The Connection Strings Reference} is a
useful resource that has example connection strings for a large variety of
databases.
}
\section{Overview}{
The odbc package is one piece of the R interface to databases with support
for ODBC:
\figure{whole-game.png}{options: width=95\%}
The package supports any \strong{Database Management System (DBMS)} with ODBC
support. Support for a given DBMS is provided by an \strong{ODBC driver}, which
defines how to interact with that DBMS using the standardized syntax of ODBC
and SQL. Drivers can be downloaded from the DBMS vendor or, if you're a Posit
customer, using the \href{https://docs.posit.co/pro-drivers/}{professional drivers}.
To manage information about each driver and the data sources they provide
access to, our computers use a \strong{driver manager}. Windows is bundled with
a driver manager, while MacOS and Linux require installation of one; this
package supports the \href{https://www.unixodbc.org/}{unixODBC} driver manager.
In the \strong{R interface}, the \href{https://dbi.r-dbi.org/}{DBI package} provides a
front-end while odbc implements a back-end to communicate with the driver
manager. The odbc package is built on top of the
\href{https://nanodbc.github.io/nanodbc/}{nanodbc} C++ library.
Interfacing with DBMSs using R and odbc involves three high-level steps:
\enumerate{
\item \emph{Configure drivers and data sources}: the functions \code{\link[=odbcListDrivers]{odbcListDrivers()}},
\code{\link[=odbcListDataSources]{odbcListDataSources()}}, and \code{\link[=odbcListConfig]{odbcListConfig()}} help to interface with the
driver manager.
\item \emph{Connect to a database}: The \code{\link[=dbConnect]{dbConnect()}} function, called with the
first argument odbc(), connects to a database using the specified ODBC
driver to create a connection object.
\item \emph{Interface with connections}: The resulting connection object can be
passed to various functions to retrieve information on database
structure (\code{\link[=dbListTables]{dbListTables()}}), iteratively develop queries (\code{\link[=dbSendQuery]{dbSendQuery()}},
\code{\link[=dbColumnInfo]{dbColumnInfo()}}), and query data objects (\code{\link[=dbFetch]{dbFetch()}}).
}
}
\section{Learn more}{
To learn more about databases:
\itemize{
\item \href{https://solutions.posit.co/connections/db/}{"Best Practices in Working with Databases"}
documents how to use the odbc package with various popular databases.
\item \href{https://github.com/mkleehammer/pyodbc/wiki/Drivers-and-Driver-Managers}{The pyodbc "Drivers and Driver Managers" Wiki}
provides further context on drivers and driver managers.
\item \href{https://learn.microsoft.com/en-us/sql/odbc/reference}{Microsoft's "Introduction to ODBC"}
is a thorough resource on the ODBC interface.
}
}