odbcConnect              package:RODBC              R Documentation

_O_D_B_C _O_p_e_n _C_o_n_n_e_c_t_i_o_n_s

_D_e_s_c_r_i_p_t_i_o_n:

     Open connections to ODBC databases.

_U_s_a_g_e:

     odbcConnect(dsn, uid = "", pwd = "", ...)

     odbcDriverConnect(connection = "", case, believeNRows = TRUE,
                       colQuote, tabQuote = colQuote,
                       interpretDot = TRUE, DBMSencoding = "",
                       rows_at_time = 100, readOnlyOptimize = FALSE)

     odbcReConnect(channel, ...)

_A_r_g_u_m_e_n_t_s:

     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 some Oracle and Sybase
          drivers, apparently, nor for Actual Technologies' SQLite
          driver for Mac OS X.

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 to be used at
          both ends, or a length-2 character string giving the
          beginning and ending quotes. ANSI SQL uses double quotes, but
          the default mode for a MySQL server is to use backticks.

          The defaults are backtick ('`') if the DBMS is identified as
          '"MySQL"' by the driver, and double quote otherwise. 

interpretDot: logical.  Should table names of the form
          'qualifier.table' be interpreted  as table 'table' in schema
          'qualifier' (and for MySQL 'schema' means database)?

DBMSencoding: character string naming the encoding returned by the
          DBMS.  The default means the encoding of the locale R is
          running under.  Values other than the default require 'iconv'
          to  be available: see 'capabilities'.

rows_at_time: The default number of rows to fetch at a time, between 1
          and 1024.  Not all drivers work correctly with values > 1:
          see 'sqlQuery'.

readOnlyOptimize: logical: should the connection be optimized for
          read-only access?

_D_e_t_a_i_l_s:

     'odbcConnect' establishes a connection to the specified 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.

     For databases that translate table and column names 'case' must be
     set appropriately.  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), 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.  It is likely that
     '"toupper"' is desirable on IBM's DB2, but this is not enforced.

     Note that 'readOnlyOptimize' may do nothing, and is *not*
     guaranteed to enforce read-only access.  With drivers that support
     it, it is used to optimize locking strategies, transaction
     management and so on.  It does make access to Mimer read-only, and
     has no effect on MySQL.

     Function 'odbcReConnect' re-connects to a database using the
     settings of an existing (and presumably now closed) channel
     object. Arguments given in the original call can be overridden as
     needed.

     Note that if a password is supplied (either as a 'pwd' argument or
     as part of the DSN) it may be stored in the 'connection.string'
     element of the return value, but the value is (from 'RODBC' 1.3-0)
     replaced by '******'.  (This will break 'odbcReConnect'.)

     If it is possible to set the DBMS or ODBC driver to communicate in
     the character set of the R session then this should be done.  For
     example, MySQL can set the communication character set _via_ SQL,
     e.g. 'SET NAMES 'utf8''.

_V_a_l_u_e:

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

rows\_at\_time: the value of 'row_at_time'.

_A_u_t_h_o_r(_s):

     Michael Lapsley, Brian Ripley

_S_e_e _A_l_s_o:

     'odbcClose', 'sqlQuery', 'odbcGetInfo'

_E_x_a_m_p_l_e_s:

     ## Not run: 
     # MySQL
     channel <- odbcConnect("test", uid="ripley", pwd="secret")
     # PostgreSQL: 'case' should be detected automatically
     channel <- odbcConnect("pg", uid="ripley", pwd="secret", case="postgresql")

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

