Send SQL Query to Database

Source: R/hsSqlQuery.R

Get data from database requested via an SQL query. This function performs opening of the connection, data retieval via SQL and closing of the connection. If an error occurs the program stops and an error description is shown.

hsSqlQuery(
  mdb,
  sql,
  use2007Driver = NULL,
  dbg = TRUE,
  stopOnError = TRUE,
  DBMSencoding = "",
  ...
)

Arguments

mdb

full path to MS Access database file (extension “.mdb” or “.accdb”) or MS Excel file (extension “.xls” or “.xlsx”).
sql

SQL query
use2007Driver

if TRUE the functions odbcConnectAccess2007 and odbcConnectExcel2007 are used instead of odbcConnectAccess and odbcConnectExcel, respectively
dbg

if TRUE (default), debug messages are shown.
stopOnError

if TRUE (default), the program stops in case of an error, otherwise a warning is shown and NULL is returned.
DBMSencoding

finally passed to odbcDriverConnect. Default: "", You may want to use: "UTF-8"
...

additional arguments to be passed to sqlQuery

Value

On success, a data.frame containing the data that is internally requested by calling the RODBC function sqlQuery and that is provided by the database is returned. On error R stops execution and does not return anything.

See also

hsPutTable, hsGetTable

Examples

if (FALSE) {
## Get Q time series from table "tbl_Hyd" in example database

if (.Platform$OS.type == "windows") {

  tsQ <- hsSqlQuery(
    xmdb(), "SELECT Zeitst AS t, Q FROM tbl_Hyd WHERE Q > 1.0"
  )

  ## Show the first lines of the resulting data.frame
  head(tsQ)
}

## Output
# t     Q
# 1 2011-08-24 22:27:00 1.061
# 2 2011-08-24 22:28:00 1.091
# 3 2011-08-24 22:29:00 1.115
# 4 2011-08-24 22:30:00 1.092
# 5 2011-08-24 22:31:00 1.086
# 6 2011-08-24 22:32:00 1.074
}