RODBCtables              package:RODBC              R Documentation

_O_p_e_r_a_t_i_o_n_s _o_n _T_a_b_l_e_s _i_n _O_D_B_C _d_a_t_a_b_a_s_e_s

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

     Operations on tables in ODBC databases.

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

     sqlColumns(channel, sqtable, errors = FALSE, as.is = TRUE, special = FALSE)
     sqlPrimaryKeys(channel, sqtable, errors = FALSE, as.is = TRUE)
     sqlDrop(channel, sqtable, errors = TRUE)
     sqlClear(channel, sqtable, errors = TRUE)

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

 channel: connection handle as returned by `odbcConnect'.

 sqtable: a database table name accessible from the connected dsn. This
          should be either a character string or a character vector of
          length 1.

  errors: if TRUE halt and display error, else return -1

   as.is: as in `sqlGetResults'.

 special: return only the column(s) needed to specify a row uniquely. 
          Depending on the database, there might be none.

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

     `sqlClear' deletes the content of the table `sqtable'.  No
     confirmation is requested.

     `sqlDrop' removes the table `sqtable'.  No confirmation is
     requested.

     `sqlColumns' and `sqlPrimaryKeys' return information as data
     frames.  Note that the column names often contain underscores and
     are invalid in R unless 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 (see `sqlUpdate'.

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

     A data frame on success, or character/numeric on error depending
     on the `errors' parameter.  If no data is returned, either a
     zero-row data frame or an error. (For example, if there are no
     primary keys or special column(s) in this table an empty data
     frame is returned, but if primary keys are not supported by the
     DBMS, an error code results.)

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

     Michael Lapsley and Brian Ripley

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

     `odbcConnect', `sqlQuery', `sqlFetch', `sqlSave', `sqlTables',
     `odbcGetInfo'

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

     ## example results from MySQL
     data(USArrests)
     channel <- odbcConnect("test", "", "") # userId and password
     sqlDrop(channel, "USArrests", errors = FALSE) # precautionary
     sqlSave(channel, USArrests)
     sqlColumns(channel, "USArrests")
     sqlColumns(channel, "USArrests", special = TRUE)
     sqlPrimaryKeys(channel, "USArrests")
     sqlColumns(channel, "USArrests")
     ##   Table_cat Table_schema Table_name Column_name Data_type Type_name
     ## 1                         USArrests    rownames        12   varchar
     ## 2                         USArrests      murder         8    double
     ## 3                         USArrests     assault         4   integer
     ## 4                         USArrests    urbanpop         4   integer
     ## 5                         USArrests        rape         8    double
     ##   Column_size Buffer_length Decimal_digits Num_prec_radix Nullable Remarks
     ## 1         255           255           <NA>           <NA>        0
     ## 2          22             8             31             10        1
     ## 3          11             4              0             10        1
     ## 4          11             4              0             10        1
     ## 5          22             8             31             10        1
     sqlColumns(channel, "USArrests", special = TRUE)
     ##   Scope Column_name Data_type Type_name Precision Length Scale
     ## 1     2    rownames        12   varchar         0      0     0
     ##   Pseudo_column
     ## 1             1
     sqlPrimaryKeys(channel, "USArrests")
     ##   Table_qualifer Table_owner Table_name Column_name Key_seq Pk_name
     ## 1           <NA>        <NA>  USArrests    rownames       1 PRIMARY
     sqlClear(channel, "USArrests")
     sqlDrop(channel, "USArrests")
     odbcClose(channel)

