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, ..., rows_at_time = 1)

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

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

rows_at_time: The number of rows to fetch at a time, up to 1024.  Not
          all drivers work correctly with values > 1.  See Details.

   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: an initial guess at the number of rows, used if 'max = 0' and
          'believeNRows == FALSE' for the driver.

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

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

stringsAsFactors: should character columns not excluded by 'as.is' and
          not converted to anything else be converted to factors?

_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', and retrieves (some or all of) the
     results via 'sqlGetResults'.

     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 'sqlQuery' or 'odbcQuery' and used to retrieve
     waiting results into a data frame.  Its main use is with 'max' set
     to non-zero when 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.

     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 by the ODBC interface.  If the 'as.is' is true for a
     column, it is returned as character. Otherwise (where detected)
     'date', 'datetime' and 'timestamp' values are converted to
     '"Date"' and '"POSIXct"' values respectively.  (Some drivers seem
     to confuse times with dates, so times may get converted too.) 
     Other types are converted by R using 'type.convert'.  When
     character data are to be converted to numeric data, the setting of
     'options("dec")' to map the character used up the ODBC driver in
     setting decimal points-this is set to a locale-specific value when
     RODBC is initialized if it is not already set.

     Using 'buffsize' will yield a marginal increase in speed if set to
     no less than the maximum number of rows when 'believeNRows =
     FALSE'.  (If set too small it can result in unnecessarily high
     memory use as the buffers will need to be expanded.)

     Modern drivers should work (and work faster, especially if
     communicating with a remote machine) with 'rows_a_time = 1024'.
     However, some drivers may mis-fetch multiple rows, so set this to
     '1' if the results are incorrect.

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

     ## Not run: 
     channel <- odbcConnect("test")
     sqlSave(channel, USArrests, rownames = "State", verbose = TRUE)
     # options(dec=".") # optional, if DBMS is not locale-aware
     ## note case of State, Murder, rape are DBMS-dependent.
     sqlQuery(channel, paste("select State, Murder from USArrests",
                             "where Rape > 30 order by Murder"))
     close(channel)
     ## End(Not run)

