RODBC (version 1.2-1)

odbcConnect: ODBC Open Connections

Description

Open connections to ODBC databases.

Usage

odbcConnect(dsn, uid = "", pwd = "", ...)
odbcDriverConnect(connection = "", case, believeNRows = TRUE,
                  colQuote, tabQuote = colQuote, DBMSencoding = "",
                  rows_at_time = 1000, bulk_add = NULL)
odbcReConnect(channel, case, believeNRows)
#ifdef windows
odbcConnectAccess(access.file, uid = "", pwd = "", ...)
odbcConnectDbase(dbf.file, ...)
odbcConnectExcel(xls.file, readOnly = TRUE, ...)
#endif

Arguments

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 u

DBMSencoding

character string naming the encoding returned by the DBMS. The default means the encoding of the locale Ris running under. Values other than the default require iconv to be available: see <

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.

access.file, dbf.file, xls.file

file of an appropriate type.

readOnly

logical: should the connection be read-only?

Value

• 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.

Excel Connections

The Excel driver by default makes read-only connections, and has only limited abilities to create a new worksheet or to change a worksheet.

A table in an Excel database (spreadsheet) can be either a named range (http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q195951&) or a worksheet: the latter have table name the name of the worksheet with $ appended (and such names may contain spaces and other characters not allowed in SQL table names). RODBC will generally allow worksheets to be referred to with or without the trailing $, but this does need to be taken into account in SQL queries (where non-standard table names are escaped by enclosing them in square brackets).

It assumes that the first row of the table in the worksheet contains column headings: the driver parameter FirstRowHasNames=0 is supposed to turn this off (giving column names F1 ...) but it is broken in many versions of the drivers, including those current in WIndows XP SP2. A connection that allows modification can be created via odbcConnectExcel(readOnly = FALSE) or directly (see the examples). Howver, the Excel ODBC driver does not support deletion (including SQL DROP, DELETE, UPDATE and ALTER statements). In particular, sqlDrop will remove the data in a worksheet but not the worksheet itself. sqlSave can be used to create new worksheets (and it also creates a marked range for the contents of the worksheet) but not to overwrite an existing worksheet, but can be sqlUpdate to update the contents of an existing worksheet.

Details

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.

#ifdef windows

Under the Windows GUI, specifying an incomplete connection, for example the default "", will bring up a dialog box to complete the information required. (This does not work from Rterm.exe unless a driver is specified, a Windows restriction.) #endif

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. #ifdef windows (The DBase driver is unusual: it preserves names on reading, but converts both table and column names to upper case on writing, and truncates table names to 8 characters. RODBC does not attempt to do any mapping for that driver.) #endif

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. #ifdef windows

odbcConnectAccess, odbcConnectDbase and odbcConnectExcel are convenience wrappers to generate connection strings for those file types. The files given can be relative to the Rworking directory or absolute paths (and it seems also relative to the user's home directory). Note: they will only work with English versions of the Microsoft drivers, which may or may not be installed in other locales. The file name can be omitted which will bring up a dialog box to search for a file. #endif

If it is possible to set the DBMS to communicate in the character set of the Rsession then this should be done. For example, MySQL can set the communication character set via SQL, e.g. SET NAMES 'utf8'.

See Also

odbcClose, sqlQuery, odbcGetInfo

Examples

#ifdef unix
# MySQL
channel <- odbcConnect("test", uid="ripley", pwd="secret")
# PostgreSQL
channel <- odbcConnect("pg", uid="ripley", pwd="secret", case="postgresql")
#endif
#ifdef windows
# interactive specification under RGui
channel <- odbcDriverConnect("")

# MySQL on Windows -- MySQL maps to lower case on Windows only
channel <- odbcConnect("testdb", uid="ripley", case="tolower")

# Access
channel <- odbcConnect("testacc") # if this was set up as a DSN
channel2 <- odbcConnectAccess("test.mdb", uid="ripley")

# Excel
channel <- odbcConnect("bdr.xls") # if this was set up as a DSN
channel2 <-
 odbcDriverConnect("DRIVER=Microsoft Excel Driver (*.xls);DBQ=C:\bdr\hills.xls; ReadOnly=False")
channel3 <- odbcConnectExcel("hills.xls")
#endif

# re-connection
odbcCloseAll()
channel <- odbcReConnect(channel) # must re-assign as the data may well change