hsSqlQuery: Send SQL Query to Database

In KWB-R/kwb.db: Functions supporting data base access

Send SQL Query to Database

Description

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.

Usage

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

## Not run: 
## 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

## End(Not run)

KWB-R/kwb.db documentation built on Oct. 1, 2023, 4:10 a.m.