RODBC_1.3-19.zip

ODBC Connectivity

by Brian Ripley

Department of Statistics, University of Oxford

ripley@stats.ox.ac.uk

September 16, 2021

Package RODBC implements ODBC database connectivity. It was originally written by

Michael Lapsley (St George’s Medical School, University of London) in the early days of R

(1999), but after he disappeared in 2002, it was rescued and since much extended by Brian

Ripley. Version 1.0-1 was released in January 2003, and RODBC is nowadays a mature

systems (DBMSs) such as MySQL

2

, PostgreSQL, Microsoft Access and SQL Server, DB2,

Oracle and SQLite. It originated on Windows in the early 1990s, but ODBC driver man-

agers unixODBC and iODBC are nowadays available on a wide range of platforms (and iODBC

is used by macOS. The connection to the particular DBMS needs an ODBC driver: these

may come with the DBMS or the ODBC driver manager or be provided separately by

the DBMS developers, and there are third-party

Easysoft and OpenLink. (This means that for some DBMSs there are several different

ODBC drivers available, and they can behave differently.)

platform client-server design, so it is common to run R on a personal computer and access

data on a remote server whose OS may not even be known to the end user. This does

This number describes both the API (most drivers nowadays work with API 3.51 or 3.52)

and capabilities. The latter allow ODBC drivers to implement newer features partially or

not at all, so some drivers are much more capable than others: in the main RODBC works

with basic features. ODBC is a superset of the ISO/IEC 9075-3:1995 SQL/CLI standard.

A somewhat biased overview of ODBC on Unix-alikes can be found at https://www.

easysoft.com/developer/interfaces/odbc/linux.html.

2 Basic Usage

Two groups of functions are provided in RODBC. The mainly internal odbc* commands

implement low-level access to C-level ODBC functions with similar

names. The sql*

but should be closed explicitly at the end of the session—however RODBC will clear up

after you if you forget (with a warning that might not be seen in a GUI environment).

There can be many simultaneous connections.

The simplest way to make a connection is

and when you are done with it,

5

More flexibility is available via function odbcDriverConnect, which works with a connec-

but it can be constructed without a DSN by specifying a driver directly via DRIVER=,

and more (in some cases many more) driver-specific parameters can be given. See the

documentation for the driver (and Appendix A) for more details.

where ‘database’ can be interpreted very widely, including for example Excel spreadsheets

Some drivers will return all visible table-like objects, not just those owned by you. In that

case you may want to restrict the scope by e.g.

The details are driver-specific but in most cases some pattern can use wildcards

6

with

underscore matching a single character and percent matching zero or more characters.

Since underscore is a valid character in a table name it can be handled literally by preceding

it by a backslash—but it is rarely necessary to do so.

It is often necessary to reduce the data to be transferred: we have seen how to subset rows,

but it can be more effective to restrict the columns or to return only rows meeting some

conditions. To find out what columns are available, use sqlColumns, for example

or even in upper case. Describing how to extract data from databases is the forte of the

SQL language, and doing so efficiently is the aim of many of the DBMSs, so this is a very

powerful tool. To learn SQL it is best to find a tutorial specific to the dialect you will

use; for example Chapter 3 of the MySQL manual is a tutorial. A basic tutorial which

covers some common dialects

7

can be found at http://www.1keydata.com/sql/sql.html:

tutorials on how to perform common tasks in several commonly used DBMSs are available

at http://sqlzoo.net/.

SQL-92 expects both table and column names to be alphanumeric plus underscore, and

schema (see below): this can be suppressed by opening the connection with argument