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