sqlQuery                package:RODBC                R Documentation

_Q_u_e_r_y _a_n _O_D_B_C _D_a_t_a_b_a_s_e

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

     Submit an SQL query to an ODBC database, and retrieve the results.

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

     sqlQuery(channel, query, errors = TRUE, ...)
     sqlGetResults(channel, as.is = FALSE,  errors = FALSE,
                   max = 0, buffsize = 1000,
                   nullstring = NA, na.strings = "NA",
                   believeNRows = TRUE, dec = getOption("dec"))

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

 channel: connection handle as returned by `odbcConnect'.

   query: any valid SQL statement

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

     ...: additional arguments to be passed to `sqlGetResults'.

   as.is: which (if any) character columns should be converted, as in
          `read.table'?  See the details.

     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'
          character items from the database. 

na.strings: character string(s) to be mapped to `NA' when reading
          character data. 

believeNRows: logical.  Is the number of rows returned by the ODBC
          connection believable?  This might already be set to false
          when the channel was opened, and can that setting cannot be
          overriden.

     dec: The character for the decimal place to be assumed when
          converting character columns.

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

     `sqlQuery' is the workhorse function of RODBC.  It sends the SQL
     statement `query' to the server, using connection `channel'
     returned by `odbcConnect'.

     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 or
     additional arguments passed to `sqlQuery'.

     `sqlGetResults' is a mid-level function.  It should be called
     after a call to `odbcQuery' and used to retrieve waiting results
     into a data frame. 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.  It makes use of the setting of
     `options("dec")' to determine the character used in setting
     decimal points.  This is set to `"."' when RODBC is initialized if
     it is not already set.

     Where possible `sqlGetResults' transfers data directly: this
     happens for `double', `real', `integer' and `smallint' columns in
     the table.  All other SQL data types are converted to character
     strings (of length up to 256), and then converted by
     `type.convert' as controlled by the `as.is' argument.  Note that
     almost all callers set `as.is = TRUE'.

     Using `buffsize' will yield a marginal increase in speed if set to
     more than the maximum number of rows where `believeNRows = FALSE'.

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

     A data frame (possibly with 0 rows) on success. If `errors =
     TRUE', a character vector of error message(s), otherwise error
     code `-1' (general, call `odbcGetErrMsg' for details) or `-2' (no
     data, which may not be an error as some SQL commands do return no
     data).

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

     Michael Lapsley and Brian Ripley

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

     `odbcConnect', `sqlFetch', `sqlSave', `sqlTables', `odbcQuery'

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

     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
     ## note case of State, Murder, rape are DBMS-dependent.
     sqlQuery(channel, "select State, Murder from USArrests where rape > 30 order by Murder")
     odbcClose(channel)

