RODBC: ODBC Database Connectivity

Description

RODBC implements odbc database connectivity with compliant databases where drivers exist on the host system.

Usage

sqlQuery(channel, query, errors = TRUE, ...)
sqlSave(channel, dat, tablename = NULL, append = FALSE, rownames = FALSE, 
        colnames = FALSE, verbose = FALSE, oldstyle = FALSE, ...) 
sqlFetch(channel, sqtable, ..., colnames = FALSE, rownames = FALSE) 
sqlTables(channel, errors = FALSE, as.is = TRUE))
sqlPrimaryKeys(channel, sqtable, as.is = TRUE, errors = FALSE)
sqlColumns(channel, sqtable, errors = FALSE, as.is = TRUE, special = FALSE)
sqlDrop(channel, sqtable, errors = TRUE)
sqlClear(channel, sqtable, errors = TRUE)
sqlCopy(channel, query, destination, destchannel = -1, verbose = TRUE,
        errors = TRUE)
sqlCopyTable(channel, srctable, desttable, destchannel = -1, verbose = TRUE, 
             errors = TRUE) 
sqlGetResults(channel, as.is = FALSE,  as = "data frame", errors = FALSE,
              max = 0, buffsize = 1000, nullstring = "NA", na.strings = "NA")
sqlUpdate(channel, dat, verbose = FALSE, test = FALSE, nastring = NULL)
sqlTypeInfo(channel, type = "all", errors = FALSE, as.is = TRUE)

Arguments

channel, destchannel

connection handle returned by odbcConnect.

query

any valid SQL statement

table, tablename, destination, srctable, desttable, sqtable

a database table name accessible from the connected dsn. This can be either a character string or an (unquoted) symbol.

errors

if TRUE halt and display error, else return -1

verbose

Display statements as they are sent to the server

append

logical. Should data be appended to an existing table?

dat

a data frame

rownames

save row labels as the first column in the table

colnames

save column names as first row of table

as.is

as in read.table

type of return value. Valid values are "data frame", "matrix" and "transposed matrix", the latter two being character matrices as returned by the DBMS.

transposing

return rows and columns transposed, as character matrix.

special

return columns needed to specify a row uniquely.

test

show what would be done, only.

nastring

character string to be used for writing NAs to the database.

max

limit on the number of rows to fetch, with 0 indicating no limit.

buffsize

the number of rows to be transferred at a time.

nullstring

character string to be used when reading SQL_NULL_DATA items from the database.

na.strings

character string(s) to be mapped NA when reading data.

...

additional arguments to be passed to sqlGetResults.

type

The types of columns about which information is requested. Possible values are

"all", "char", "varchar", "real",
      "double", "integer", "smallint", "timestamp"

oldstyle

logical. If false, attempt to use sqlTypeInfo to choose the types of columns when a table has to be created.

Value

In general sql* commands return a data frame on success, or -1/verbose on error depending on the errors parameter.

Details

Two groups of commands are provided. odbc* commands implement relatively low level access to the odbc functions of similar name. sql* commands are higher level constructs to read, save, copy and manipulate data between data frames and sql tables. Up to 16 connections can be open at once to any combination of dsn/hosts. Columns are limited to 255 chars of non-binary data. sqlQuery is the workhorse function. It sends the SQL statement query to the server, using connection channel returned by odbcConnect. Returns a data frame of results, transformed according to as.is. If errors=FALSE returns -1 on error, otherwise halts with a message from the server. transposing reverses columns and rows if TRUE. buffsize will yield a marginal increase in speed if increased for some database engines e.g. MSaccess. SQL beginners should note that the term `Query' includes any valid SQL statement including table creation, alteration, updates etc as well as SELECTs. The sqlQuery command is a convenience wrapper that calls first odbcQuery and then sqlGetResults. If finer-grained control is needed, for example over the number of rows fetched, these functions should be called directly.

sqlGetResults is a mid-level function. It should be called after a call to odbcQuery and used to retrieve waiting results into a dataframe. Its main use is with max set to non zero it will retrieve the result set in batches with repeated calls. This is useful for very large result sets which can be subjected to intermediate processing.

sqlSave saves the data frame dat in the table dat. The table name is taken from tablename if given or the name of the dataframe. If the table exists and has the appropriate structure it is used, or else it is created anew with type varchar(255). If rownames=TRUE the first column of the table will be the row labels with colname rowname. rownames can also be a string giving the desired name (see example). colnames copied the column names into row 1. This is intended for cases where case conversion alters the original column names and it is desired that they are retained. Note that there are drawbacks to this approach: it presupposes that the rows will be returned in correct order; not always valid. It will also cause numeric rows to be returned as factors. WARNING: sqlSave uses the 'great white shark' method of testing tables (bite it and see). The logic will unceremoniously DROP the table and create it anew with VARCHAR column types in its attempt to find a writeable solution. test=TRUE will not necessarily predict this behaviour. Attempting to write indexed columns or writing to pseudo- columns are less obvious causes of failed writes followed by a DROP. If your table structure is precious to you back it up.

sqlFetch loads the the entire contents of the table dat. (The reverse of sqlSave.) Rownames and column names are restored as indicated. (More accurately the first row and column returned is transferred to the row/col names).

sqlCopy as above, but saves the output of query in table destination on dsn destchannel. [Not yet implemented.] sqlCopyTable copies the structure of srctable to desttable on dsn destchannel. This is within the limitations of the ODBC lowest common denominator. More precise control is possible via sqlQuery. sqlClear deletes the content of the table sqtable. No confirmation is requested.

sqlDrop removes the table sqtable. No confirmation is requested.

sqlUpdate updates the table where the rows already exist. The dataframe must contain a column named after the row that the database regards as the optimal for defining a row uniquely. (This is returned by sqlColumns(..., special=TRUE)). sqlColumns, sqlTables, and sqlPrimaryKeys return information as data frames. Note that the column names contain underscores and are invalid in Runless quoted. The column names are not constant across ODBC versions so the data should be accessed by column number. The argument special to sqlColumns returns the columns needed to specify a row uniquely. This is intended to form the basis of a WHERE clause for updates.

sqlGetResults returns the last result set created by an odbc* call as a data frame.

sqlTypeInfo attempts to find the types of columns the database supports. Not all ODBC drivers support this. Where it is supported, it is used to decide what columns types to create when creating a new table in the database. If the information is unavailable, "varchar" columns are used.

A few functions, notably sqlFetch, try to cope with the peculiar way the Excel ODBC driver handles names.

Examples

Run this code

library(RODBC)
data(USArrests)
channel <- odbcConnect("test", "", "") # userId and password
sqlSave(channel, USArrests, rownames = "State", verbose = TRUE)
options(dec=".") # this is the default decimal point
sqlQuery(channel, "select State, Murder from USArrests where rape > 30 order by Murder")
sqlFetch(channel, "USArrests", rownames = TRUE) # get the lot
sqlDrop(channel, "USArrests")  # quotes are optional
odbcClose(channel)
rm(USArrests)