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, DBMSencoding = "",
                       rows_at_time = 1000, bulk_add = NULL)

     odbcReConnect(channel, case, believeNRows)

_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 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 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. 

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

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

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

     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.

     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.

     If it is possible to set the DBMS 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'.

_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
     channel <- odbcConnect("pg", uid="ripley", pwd="secret", case="postgresql")

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

