Access to Database Management Systems (DBMS) in R

ODBC Interface

The RODBC package provides access to databases (including Microsoft Access and Microsoft SQL Server) through an ODBC interface.

The primary functions are given below.


Function	Description
*odbcConnect(dsn, uid="", pwd="")*	Open a connection to an ODBC database
*sqlFetch(channel, sqtable)*	Read a table from an ODBC database into a data frame
*sqlQuery(channel, query)*	Submit a query to an ODBC database and return the results
*sqlSave(channel, mydf, tablename = sqtable, append = FALSE)*	Write or update (append=True) a data frame to a table in the ODBC database
*sqlDrop(channel, sqtable)*	Remove a table from the ODBC database
*close(channel)*	Close the connection

# RODBC Example
# import 2 tables (Crime and Punishment) from a DBMS
# into R
data frames (and call them crimedat and pundat)

library(RODBC)
myconn <-odbcConnect("mydsn", uid="Rob", pwd="aardvark")
crimedat <- sqlFetch(myconn, "Crime")
pundat <- sqlQuery(myconn, "select * from Punishment")
close(myconn)

Other Interfaces

The RMySQLpackage provides an interface to MySQL.

The ROracle package provides an interface for Oracle.

The RJDBC package provides access to databases through a JDBC interface.

Going Further

This tutorial at DataCamp has another example with the RODBC package.