| Title: | Functions supporting data base access |
|---|---|
| Description: | This package contains some useful functions, especially for simplifying data transfer between MS Access databases and R. With the functions of this package it is not needed any more to open and close a database connection explicitely; this is done 'behind the scenes' in the functions. Instead of a database connection the path to the database file needs to be passed to the functions as an argument. The main functions are hsGetTable and hsPutTable which transfer data from an MS Access database to a data frame in R and save data from a data frame in R into a table in an MS Access database, respectively. Take care when getting time series data from an MS Access database, see therefore hsMdbTimeSeries. Use hsTables to get a list of tables that are available in a database and hsFields to get a list of table fields that are contained in a database table. |
| Authors: | Hauke Sonnenberg [aut, cre] (ORCID: <https://orcid.org/0000-0001-9134-2871>), Michael Rustler [ctb] (0000-0003-0647-7726), Kompetenzzentrum Wasser Berlin gGmbH [cph] |
| Maintainer: | Hauke Sonnenberg <[email protected]> |
| License: | MIT + file LICENSE |
| Version: | 0.7.1 |
| Built: | 2026-06-24 14:58:07 UTC |
| Source: | https://github.com/KWB-R/kwb.db |
Connection String Access
connectionStringAccess(mdb, uid = "", pwd = "", globalPartialBulkOps = 0)connectionStringAccess(mdb, uid = "", pwd = "", globalPartialBulkOps = 0)
mdb |
full path to MS Access file |
uid |
user id, if any |
pwd |
password, if any |
globalPartialBulkOps |
A Long value (read/write) that determines the behavior of the Jet database engine when SQL DML bulk operations fail. When set to allow partial completion of bulk operations, inconsistent changes can occur, because operations on some records could succeed and others could fail. When set to allow no partial completion of bulk operations, all changes are rolled back if a single error occurs. The Jet OLEDB:Global Partial Bulk Ops property setting can be overridden for the current Recordset object by setting the Jet OLEDB:Partial Bulk Ops property.The Jet OLEDB:Global Partial Bulk Ops and Jet OLEDB:Partial Bulk Ops properties can be set to any of the following values: Default = 0, Partial = 1, No Partial = 2 |
http://msdn.microsoft.com/en-us/library/office/aa140022%28v=office.10%29.aspx
Create R-Function Interface to Database
createRDatabaseInterface( db = NULL, dbSchema = NULL, tableNamePattern = "", functionPrefix = "db_", pattern = "^", rfile = file.path(tempdir(), "dbInterface.R"), rfile.create = file.path(tempdir(), "dbInterfaceCreate.R"), create.create.functions = FALSE, dbg = TRUE, sql.dbg = TRUE )createRDatabaseInterface( db = NULL, dbSchema = NULL, tableNamePattern = "", functionPrefix = "db_", pattern = "^", rfile = file.path(tempdir(), "dbInterface.R"), rfile.create = file.path(tempdir(), "dbInterfaceCreate.R"), create.create.functions = FALSE, dbg = TRUE, sql.dbg = TRUE )
db |
name of ODBC data source or full path to MS Access or Excel file |
dbSchema |
list structure describing the database schema, as returned by kwb.odm::dbSchema_ODM() |
tableNamePattern |
pattern matching the names of tables/views for which an accessor function is to be generated |
functionPrefix |
prefix to be given to each generated function. Default: "db_" |
pattern |
pattern matching the part of the table name that will be replaced with functionPrefix. Default: "^", i.e. the prefix will be appended to the start of the table name |
rfile |
full path to the file in which to store the data access functions |
rfile.create |
full path to the file in which to store the data creation functions (only relevant if crete.create.functions is TRUE) |
create.create.functions |
logical. If TRUE (the default if FALSE) not only data access but also data creation functions are generated |
dbg |
if TRUE, progress messages are shown during the creation of the interface functions |
sql.dbg |
if TRUE, SQL strings used to access the database are shown when calling the interface functions |
Get Path to or name of current database (as set with
setCurrentDb)
currentDb(dbg = TRUE)currentDb(dbg = TRUE)
dbg |
if TRUE, a message obout setting the current database is printed |
Create SQL Tuples from Data Frame
dataFrameToSqlTuples(newData)dataFrameToSqlTuples(newData)
newData |
a data frame |
vector of character strings each of which represents one row in
newData
x <- data.frame( name = c("Peter", "Paul"), birthday = as.POSIXct(c("1981-12-13", "2003-01-16")) ) setCurrentSqlDialect("msaccess") dataFrameToSqlTuples(x) # Note that the representation of a date and time is different in MySQL setCurrentSqlDialect("mysql") dataFrameToSqlTuples(x)x <- data.frame( name = c("Peter", "Paul"), birthday = as.POSIXct(c("1981-12-13", "2003-01-16")) ) setCurrentSqlDialect("msaccess") dataFrameToSqlTuples(x) # Note that the representation of a date and time is different in MySQL setCurrentSqlDialect("mysql") dataFrameToSqlTuples(x)
Exports all tables of a database of which the names match a given pattern to csv files.
dumpDatabase( db, pattern = "^tbl", target_dir = NULL, create_target_dir = FALSE, sep = ",", dec = ".", as.is = FALSE, qmethod = "double", row.names = FALSE, ... )dumpDatabase( db, pattern = "^tbl", target_dir = NULL, create_target_dir = FALSE, sep = ",", dec = ".", as.is = FALSE, qmethod = "double", row.names = FALSE, ... )
db |
full path to database or name of ODBC data source |
pattern |
pattern matching names of tables to be exported. Default: "^tbl", i.e. tables starting with "tbl" |
target_dir |
target directory. By default a new directory is created in the same directory as mdb resides in. The new directory has the same name as the database file with dots substituted with underscores |
create_target_dir |
if |
sep |
passed to |
dec |
passed to |
as.is |
passed to |
qmethod |
passed to |
row.names |
passed to |
... |
further arguments passed to |
Get Current SQL Dialect
getCurrentSqlDialect(warn = TRUE, dbg = FALSE)getCurrentSqlDialect(warn = TRUE, dbg = FALSE)
warn |
if TRUE and if no current SQL dialog is stored in the options, the program stops with an error message |
dbg |
if TRUE, a message about the current SQL dialect is printed |
Get Information on Table Field Names and Types
getDatabaseFieldInfo(db)getDatabaseFieldInfo(db)
db |
path to MS Access or MS Excel file or name of ODBC data source |
data frame with columns TABLE_NAME, COLUMN_NAME,
TYPE_NAME, DECIMAL_DIGITS
Get Database Schema
getDatabaseSchema(db, tableNames = NULL, tableTypes = c("TABLE", "VIEW"))getDatabaseSchema(db, tableNames = NULL, tableTypes = c("TABLE", "VIEW"))
db |
full path to database (*.mdb, *.xls) or name of ODBC database |
tableNames |
optional. Vector of table names of tables to be included |
tableTypes |
types of database objects to be included. Default:
|
list with elements tables and relationships. Element tables is a list o named elements with the name representing the table names and the elements being lists describing the table...
Get Filtered Records
getFilteredRecords(db, tableName, keyValues, fields, like, ...)getFilteredRecords(db, tableName, keyValues, fields, like, ...)
db |
database name or database file |
tableName |
name of the table from which to read records |
keyValues |
list of |
fields |
table fields to be selected |
like |
if |
... |
additonal arguments passed to |
Get Named Ranges from Excel File
getNamedExcelRanges( file, pattern = "^range", dbg = TRUE, stringsAsFactors = FALSE, ... )getNamedExcelRanges( file, pattern = "^range", dbg = TRUE, stringsAsFactors = FALSE, ... )
file |
path to Excel file |
pattern |
pattern matching the names of named cell ranges to be read.
By default, all ranges starting with |
dbg |
logical. If |
stringsAsFactors |
passed to |
... |
further arguments passed to |
list of data frames each of which represents the content a named cell
range in the Excel file and each of which was read with
hsGetTable.
get SQL dialect ("mysql" or "msaccess") from given database
getSqlDialect(db, use2007Driver = NULL)getSqlDialect(db, use2007Driver = NULL)
db |
ODBC database name or full path to database (mdb or xls) |
use2007Driver |
passed to |
Get Type Identifier
getTypeIdentifier(x)getTypeIdentifier(x)
x |
R object for which to find an adequate database object type |
Returns "int", "double", "text", "date_time" or "boolean" depending on the data type of x
Deletes all the rows of the table . This function performs opening
of the connection, clearing of the table and closing of the connection. If
an error occurs the program stops and an error description is shown.
hsClearTable(mdb, tbl, cond = TRUE, ...)hsClearTable(mdb, tbl, cond = TRUE, ...)
mdb |
full path to MS Access database (*.mdb). |
tbl |
table name. |
cond |
optional: condition. |
... |
additional arguments passed to hsSqlQuery, e.g. "errors=TRUE" |
Closes the database connection.
hsCloseDb(con)hsCloseDb(con)
con |
Open database connection as returned by
hsOpenDb/ |
Deprecated. Use codehsCloseDb instead.
hsCloseMdb(con)hsCloseMdb(con)
con |
Open database connection as returned by hsOpenMdb/odbcConnect |
Removes the table (if permitted). This function performs opening of
the connection, dropping of the table and closing of the connection. If an
error occurs the program stops and an error description is shown.
hsDropTable(mdb, tbl, isPtrn = FALSE, dbg = TRUE)hsDropTable(mdb, tbl, isPtrn = FALSE, dbg = TRUE)
mdb |
full path to MS Access database (*.mdb). |
tbl |
table name. |
isPtrn |
if TRUE, tbl is interpreted as a regular expression matching the names of the tables to be deleted. |
dbg |
if TRUE, debug messages are shown |
Deprecated. Please use dumpDatabase instead.
hsDumpMdb( mdb, ptrn = "^tbl", tdir = file.path(dirname(mdb), gsub("\\.", "_", basename(mdb))), create_target_dir = FALSE )hsDumpMdb( mdb, ptrn = "^tbl", tdir = file.path(dirname(mdb), gsub("\\.", "_", basename(mdb))), create_target_dir = FALSE )
mdb |
full path to database |
ptrn |
pattern matching names of tables to be exported. Default: "^tbl", i.e. tables starting with "tbl" |
tdir |
target directory. By default a new directory is created in the same directory as mdb resides in. The new directory has the same name as the database file with dots substituted with underscores |
create_target_dir |
if |
Returns a vector containing the field names of a database table.
hsFields( mdb, tbl, namesOnly = TRUE, chopDollar = TRUE, ignore.case = (!isMySQL(mdb, use2007Driver = use2007Driver)), use2007Driver = NULL, dbg = FALSE )hsFields( mdb, tbl, namesOnly = TRUE, chopDollar = TRUE, ignore.case = (!isMySQL(mdb, use2007Driver = use2007Driver)), use2007Driver = NULL, dbg = FALSE )
mdb |
full path to MS Access database file (extension “.mdb” or “.accdb”) or MS Excel file (extension “.xls” or “.xlsx”). |
tbl |
table name. |
namesOnly |
if TRUE, only field names are returned, otherwise all available information on the fields. Default: TRUE |
chopDollar |
if TRUE (default), a dollar sign at the end of the table
name is removed before sending it to |
ignore.case |
if TRUE, case is ignored when comparing the given table with the names of the existing tables. Default: FALSE |
use2007Driver |
passed to |
dbg |
if TRUE, debug messages are shown |
Vector containing the field names of the database table (if namesOnly = TRUE) or data.frame with columns COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH, DECIMAL_DIGITS, NUM_PREC_RADIX, NULLABLE describing the database fields in detail, otherwise.
## Not run: ## List the fields of table "tbl_Hyd" in the example database ## (only on Windows!) if (.Platform$OS.type == "windows") { setCurrentSqlDialect("msaccess") fields <- hsFields(xmdb(), "tbl_Hyd") fields } ## Ouput: # [1] "Zeitst" "Q" "v" "H" "T_Kanal" ## End(Not run)## Not run: ## List the fields of table "tbl_Hyd" in the example database ## (only on Windows!) if (.Platform$OS.type == "windows") { setCurrentSqlDialect("msaccess") fields <- hsFields(xmdb(), "tbl_Hyd") fields } ## Ouput: # [1] "Zeitst" "Q" "v" "H" "T_Kanal" ## End(Not run)
Provides data from an MS Access database table in forms of a data frame.
hsGetTable( mdb, tbl, cond = "TRUE", fields = "*", dbg = TRUE, check = TRUE, use2007Driver = NULL, ... )hsGetTable( mdb, tbl, cond = "TRUE", fields = "*", dbg = TRUE, check = TRUE, use2007Driver = NULL, ... )
mdb |
full path to MS Access database file (extension “.mdb” or “.accdb”) or MS Excel file (extension “.xls” or “.xlsx”). |
tbl |
Table name. Put it into brackets [] if it contains spaces and if it mdb does not point to a MySQL database |
cond |
Condition string. |
fields |
Comma separated list of names of fields to be selected. |
dbg |
if TRUE, debug messages are shown, else not |
check |
if TRUE (default), tbl is checked for existence in mdb before trying to get the data and a list of available tables is shown in the case that the table does not exist. |
use2007Driver |
passed to |
... |
Additional arguments to be passed to hsSqlQuery |
ATTENTION: This function may not return what you want if the table contains a
timestamp field. Use hsMdbTimeSeries instead.
data.frame containing data from table in database
hsSqlQuery, hsPutTable, hsGetTimeSeries,
hsMdbTimeSeries
## Not run: ## Get all datasets from tbl_Hyd in example database where ## Q > 1.0 m3/s and temperature > 20 degree Celsius ## (only on Windows!) if (.Platform$OS.type == "windows") { ts <- hsGetTable(xmdb(), "tbl_Hyd", "Q > 1.0 AND T_Kanal > 20") head(ts) } ## Output: # Zeitst Q v H T_Kanal # 1 2011-08-24 22:33:00 1.075 0.459 1.366 20.1 # 2 2011-08-24 22:34:00 1.062 0.453 1.370 20.2 # 3 2011-08-24 22:35:00 1.050 0.449 1.364 20.2 # 4 2011-08-24 22:36:00 1.042 0.446 1.361 20.3 # 5 2011-08-24 22:37:00 1.032 0.443 1.354 20.3 # 6 2011-08-24 22:38:00 1.010 0.436 1.348 20.4 ## TAKE CARE when getting time-series data: if (.Platform$OS.type == "windows") { hsGetTable(xmdb(), "tblTimestampTest_DST") } ## Output: # tstamp # 1 2011-03-27 01:00:00 # 2 2011-03-27 01:30:00 # 3 <NA> # 4 <NA> # 5 2011-03-27 03:00:00 # 6 2011-03-27 03:30:00 ## As the output shows the timestamps between 02:00:00 and ## 02:59:59 have been set to <NA>. Reason: When retrieving ## date/time data from MS Access, R converts the timestamps ## from a text representation into POSIXct objects. As POSIXct's ## standard time zone seems to be taken from the Windows system ## R tries to convert to Central European Time (CET) which ## does not exist for the hour in which time is switched to ## daylight-saving time (as in the example). ## This standard behaviour can be changed by setting the ## standard time zone: tz <- Sys.getenv("tz") # save current standard time zone Sys.setenv(tz = "UTC") # set standard time zone to UTC ## The same command as above now delivers all timestamps ## (in Coordinated Universal Time, UTC): if (.Platform$OS.type == "windows") { hsGetTable(xmdb(), "tblTimestampTest_DST") } ## Output: # tstamp # 1 2011-03-27 01:00:00 # 2 2011-03-27 01:30:00 # 3 2011-03-27 02:00:00 # 4 2011-03-27 02:30:00 # 5 2011-03-27 03:00:00 # 6 2011-03-27 03:30:00 ## Reset standard time zone Sys.setenv(tz = tz) ## End(Not run)## Not run: ## Get all datasets from tbl_Hyd in example database where ## Q > 1.0 m3/s and temperature > 20 degree Celsius ## (only on Windows!) if (.Platform$OS.type == "windows") { ts <- hsGetTable(xmdb(), "tbl_Hyd", "Q > 1.0 AND T_Kanal > 20") head(ts) } ## Output: # Zeitst Q v H T_Kanal # 1 2011-08-24 22:33:00 1.075 0.459 1.366 20.1 # 2 2011-08-24 22:34:00 1.062 0.453 1.370 20.2 # 3 2011-08-24 22:35:00 1.050 0.449 1.364 20.2 # 4 2011-08-24 22:36:00 1.042 0.446 1.361 20.3 # 5 2011-08-24 22:37:00 1.032 0.443 1.354 20.3 # 6 2011-08-24 22:38:00 1.010 0.436 1.348 20.4 ## TAKE CARE when getting time-series data: if (.Platform$OS.type == "windows") { hsGetTable(xmdb(), "tblTimestampTest_DST") } ## Output: # tstamp # 1 2011-03-27 01:00:00 # 2 2011-03-27 01:30:00 # 3 <NA> # 4 <NA> # 5 2011-03-27 03:00:00 # 6 2011-03-27 03:30:00 ## As the output shows the timestamps between 02:00:00 and ## 02:59:59 have been set to <NA>. Reason: When retrieving ## date/time data from MS Access, R converts the timestamps ## from a text representation into POSIXct objects. As POSIXct's ## standard time zone seems to be taken from the Windows system ## R tries to convert to Central European Time (CET) which ## does not exist for the hour in which time is switched to ## daylight-saving time (as in the example). ## This standard behaviour can be changed by setting the ## standard time zone: tz <- Sys.getenv("tz") # save current standard time zone Sys.setenv(tz = "UTC") # set standard time zone to UTC ## The same command as above now delivers all timestamps ## (in Coordinated Universal Time, UTC): if (.Platform$OS.type == "windows") { hsGetTable(xmdb(), "tblTimestampTest_DST") } ## Output: # tstamp # 1 2011-03-27 01:00:00 # 2 2011-03-27 01:30:00 # 3 2011-03-27 02:00:00 # 4 2011-03-27 02:30:00 # 5 2011-03-27 03:00:00 # 6 2011-03-27 03:30:00 ## Reset standard time zone Sys.setenv(tz = tz) ## End(Not run)
Reads time-series data from an MS Access database table and returns a data frame containing the data. In the data frame the timestamp column contains the timestamps as they are converted to by R from (text versions of) the original timestamps read from MS ACCESS. As this conversion may fail (e.g. the time information gets lost when transferring timestamps from large data sets between R and MS Access) this function may return different pieces of information on the timestamp in forms of additional columns, preceding the timestamp column, in the result data frame. Per default, eleven additional columns are returned: 1. <ts>_txt (timestamp as text), 2. <ts>_Date (date only), 3. <ts>_dSince18991230 (number of days since 1899-12-30), 4. <ts>_secInDay (number of seconds within the day), 5. <ts>_minInDay (number of minutes within the day), 6. <ts>_year (year), 7. <ts>_month (number of month), 8. <ts>_day (number of day within the month), 9. <ts>_h (hours within day), 10. <ts>_min (minutes within hour), 11. <ts>_s (seconds within minute) where in each case <ts> is the name of the timestamp field.
hsGetTimeSeries( mdb, tbl, tsField = hsTsField(mdb, tbl), fields = "*", minDate = NULL, maxDate = NULL, xTsFields = c(1:11), inclLast = TRUE, sqlFilter = "TRUE", dbg = FALSE )hsGetTimeSeries( mdb, tbl, tsField = hsTsField(mdb, tbl), fields = "*", minDate = NULL, maxDate = NULL, xTsFields = c(1:11), inclLast = TRUE, sqlFilter = "TRUE", dbg = FALSE )
mdb |
Full path to MS Access database file (*.mdb) |
tbl |
Name of table containing the time-series data. |
tsField |
Name of table field containing the timestamps. |
fields |
Vector containing names of value fields to be selected from the table. This vector may or may not contain the name of the timetamp field. |
minDate |
Minimum date (and time) of time interval to be selected in ISO-Syntax: yyyy-mm-dd [HH:MM:SS], where the part in brackets in optional. |
maxDate |
Day following the maximum date of the time interval to be selected, in ISO-Syntax: yyyy-mm-dd [HH:MM:SS], where the part in brackets in optional. |
xTsFields |
Extra timestamp fields to be selected. Vector containing
numbers between 1 and 11, where each number represents a type of date/time
information as described for function |
inclLast |
If TRUE, maxDate will be included in result data set, else excluded. |
sqlFilter |
additional SQL filter criterion |
dbg |
if TRUE, debug messages are shown |
This function is called internally by the higher-level function
hsMdbTimeSeries that reconstructs the correct timestamps from
the different pieces of timestamp information and provides them in forms of
POSIXct objects in UTC timezone.
Use hsMdbTimeSeries instead if you do not want to care about
any timestamp conversion problems!
data frame containing the requested data (timestamp and value columns) and additional columns preceding the timestamp column containing different pieces of information on the timestamp.
hsMdbTimeSeries, hsGetTable,
hsSqlExTsFields
## Not run: ## Get flow time series of 24 of July 2011 from tbl_Hyd in example database ## Additionally to the timestamp that is created by R, return the date only ## (timestamp info id = 2) and the number of minutes within the day ## (timestamp info id = 5). setCurrentSqlDialect("msaccess") if (.Platform$OS.type == "windows") { ts <- hsGetTimeSeries( mdb = xmdb(), tbl = "tbl_Hyd", tsField = "Zeitst", fields = c("Q", "v"), minDate = "2011-08-24", maxDate = "2011-08-25", xTsFields = c(2, 5), dbg = TRUE ) ## Show the last records of the returned dataset. tail(ts) } ## Output: # Zeitst_Date Zeitst_minInDay Zeitst Q v # 1435 2011-08-24 1435 2011-08-24 23:55:00 0.638 0.281 # 1436 2011-08-24 1436 2011-08-24 23:56:00 0.601 0.265 # 1437 2011-08-24 1437 2011-08-24 23:57:00 0.564 0.249 # 1438 2011-08-24 1438 2011-08-24 23:58:00 0.536 0.237 # 1439 2011-08-24 1439 2011-08-24 23:59:00 0.504 0.223 # 1440 2011-08-25 0 2011-08-25 00:00:00 0.483 0.214 ## End(Not run)## Not run: ## Get flow time series of 24 of July 2011 from tbl_Hyd in example database ## Additionally to the timestamp that is created by R, return the date only ## (timestamp info id = 2) and the number of minutes within the day ## (timestamp info id = 5). setCurrentSqlDialect("msaccess") if (.Platform$OS.type == "windows") { ts <- hsGetTimeSeries( mdb = xmdb(), tbl = "tbl_Hyd", tsField = "Zeitst", fields = c("Q", "v"), minDate = "2011-08-24", maxDate = "2011-08-25", xTsFields = c(2, 5), dbg = TRUE ) ## Show the last records of the returned dataset. tail(ts) } ## Output: # Zeitst_Date Zeitst_minInDay Zeitst Q v # 1435 2011-08-24 1435 2011-08-24 23:55:00 0.638 0.281 # 1436 2011-08-24 1436 2011-08-24 23:56:00 0.601 0.265 # 1437 2011-08-24 1437 2011-08-24 23:57:00 0.564 0.249 # 1438 2011-08-24 1438 2011-08-24 23:58:00 0.536 0.237 # 1439 2011-08-24 1439 2011-08-24 23:59:00 0.504 0.223 # 1440 2011-08-25 0 2011-08-25 00:00:00 0.483 0.214 ## End(Not run)
Returns a date in MS Jet SQL syntax: mm/dd/yyyy HH:MM:SS
hsJetDate(datetime, dbg = FALSE)hsJetDate(datetime, dbg = FALSE)
datetime |
Date (and time) information in forms of Date object or POSIX object or string. |
dbg |
if TRUE, debug messages are shown |
Looks up a record in a database table and returns the ID of the record. If the record is not found it is inserted to the table and the ID of the new record is returned.
hsLookupOrAddRecord( mdb, tbl, keyAssigns, fieldAssigns = NULL, idField = hsFields(mdb, tbl)[1], dbg = FALSE )hsLookupOrAddRecord( mdb, tbl, keyAssigns, fieldAssigns = NULL, idField = hsFields(mdb, tbl)[1], dbg = FALSE )
mdb |
full path to MS Access database |
tbl |
name of table in which record is to be looked up |
keyAssigns |
key-value-assignements used to identify the record to be looked up. The assignments are defined in forms of a list, e.g. list(key1 = "value1", key2 = "value2"). |
fieldAssigns |
further field-value-assignements used when a new record needs to be inserted. The assignments are defined in forms of a list, e.g. list(field1 = "value1", field2 = "value2"). |
idField |
name of ID field, default: name of first table field |
dbg |
if TRUE, debug messages are shown |
Reads time-series data from an MS Access database table and returns a data
frame containing the data. The name of the timestamp field must be given in
tsField and the names of the value fields to be selected from the
table must be given in vector fields. Instead of an ODBC channel the
name of the database must be given. This function takes care that the
timestamps are transferred correctly between MS Access and R by requesting
date and time information separately from MS Access and putting both together
to a POSIXct object in UTC timezone. This is necessary because with very long
data sets the RODBC function sqlQuery (or the function
hsSqlQuery that calls this function) may deliver timestamps in
which time information is lacking!
hsMdbTimeSeries( mdb, tbl, tsField = hsTsField(mdb, tbl), fields = "*", minDate = NULL, maxDate = NULL, resolution = "min", inclLast = TRUE, sqlFilter = "TRUE", dbg = FALSE, calcType = 1 )hsMdbTimeSeries( mdb, tbl, tsField = hsTsField(mdb, tbl), fields = "*", minDate = NULL, maxDate = NULL, resolution = "min", inclLast = TRUE, sqlFilter = "TRUE", dbg = FALSE, calcType = 1 )
mdb |
Full path to MS Access database file (*.mdb) |
tbl |
Name of table containing the time-series data. |
tsField |
Name of table field containing the timestamps. |
fields |
Vector containing names of value fields to be selected from the table. This vector may or may not contain the name of the timetamp field. |
minDate |
Minimum date (and time) of time interval to be selected in ISO-Syntax: yyyy-mm-dd [HH:MM:SS], where the part in brackets in optional. |
maxDate |
Day following the maximum date of the time interval to be selected, in ISO-Syntax: yyyy-mm-dd [HH:MM:SS], where the part in brackets in optional. |
resolution |
time resolution: “min” = minutes, “s” = seconds. If time resolution is “min” timestamps are rounded to the next full minute. |
inclLast |
If TRUE, maxDate will be included in result data set, else excluded. |
sqlFilter |
additional SQL filter criterion |
dbg |
if TRUE, debug messages are shown |
calcType |
for internal use only, do not change! |
data.frame with POSIXct timestamp column <strTimestamp> (UTC time zone) and value columns as selected in <strFieldList>
## Not run: ## Get flow time series of 24 of August 2011 from tbl_Hyd in example database if (.Platform$OS.type == "windows") { ts <- hsMdbTimeSeries( xmdb(), "tbl_Hyd", "Zeitst", c("Q", "v"), "2011-08-24", "2011-08-25" ) ## Show the last records of the returned dataset. tail(ts) } ## Output: # Zeitst Q v # 1435 2011-08-24 23:55:00 0.638 0.281 # 1436 2011-08-24 23:56:00 0.601 0.265 # 1437 2011-08-24 23:57:00 0.564 0.249 # 1438 2011-08-24 23:58:00 0.536 0.237 # 1439 2011-08-24 23:59:00 0.504 0.223 # 1440 2011-08-25 00:00:00 0.483 0.214 ## End(Not run)## Not run: ## Get flow time series of 24 of August 2011 from tbl_Hyd in example database if (.Platform$OS.type == "windows") { ts <- hsMdbTimeSeries( xmdb(), "tbl_Hyd", "Zeitst", c("Q", "v"), "2011-08-24", "2011-08-25" ) ## Show the last records of the returned dataset. tail(ts) } ## Output: # Zeitst Q v # 1435 2011-08-24 23:55:00 0.638 0.281 # 1436 2011-08-24 23:56:00 0.601 0.265 # 1437 2011-08-24 23:57:00 0.564 0.249 # 1438 2011-08-24 23:58:00 0.536 0.237 # 1439 2011-08-24 23:59:00 0.504 0.223 # 1440 2011-08-25 00:00:00 0.483 0.214 ## End(Not run)
Opens database connection to MS Access or MS Excel, checks the connection and stops on error.
hsOpenDb(src, use2007Driver = NULL, dbg = FALSE, DBMSencoding = "", ...)hsOpenDb(src, use2007Driver = NULL, dbg = FALSE, DBMSencoding = "", ...)
src |
full path to MS Access database file (extension “.mdb” or “.accdb”) or MS Excel file (extension “.xls” or “.xlsx”) or name of ODBC data source. |
use2007Driver |
if TRUE the functions odbcConnectAccess2007 and odbcConnectExcel2007 are used instead of odbcConnectAccess and odbcConnectExcel, respectively |
dbg |
if TRUE and if the connection could be established details of the connection are shown. |
DBMSencoding |
finally passed to |
... |
additional arguments passed to the odbcConnect...()-function |
On success an object of class RODBC describing the connection is returned. On failure nothing is returned and program execution stops.
## Not run: ## Open a connection to the example database ## (only on Windows!) if (.Platform$OS.type == "windows") { con <- hsOpenDb(xmdb()) con } ## Details on the established connection are shown: # RODBC Connection 9 # Details: # case=nochange # DBQ=C:\Users\hsonne\Documents\R\win-library\2.14\kwb.base\... # Driver={Microsoft Access Driver (*.mdb)} # DriverId=25 # FIL=MS Access # MaxBufferSize=2048 # PageTimeout=5 # UID=admin ## Close the connection again if (.Platform$OS.type == "windows") { hsCloseDb(con) } ## End(Not run)## Not run: ## Open a connection to the example database ## (only on Windows!) if (.Platform$OS.type == "windows") { con <- hsOpenDb(xmdb()) con } ## Details on the established connection are shown: # RODBC Connection 9 # Details: # case=nochange # DBQ=C:\Users\hsonne\Documents\R\win-library\2.14\kwb.base\... # Driver={Microsoft Access Driver (*.mdb)} # DriverId=25 # FIL=MS Access # MaxBufferSize=2048 # PageTimeout=5 # UID=admin ## Close the connection again if (.Platform$OS.type == "windows") { hsCloseDb(con) } ## End(Not run)
Deprecated. Use hsOpenDb instead.
hsOpenMdb(mdb, dbg = FALSE)hsOpenMdb(mdb, dbg = FALSE)
mdb |
full path to MS Access database file. |
dbg |
if TRUE and if the connection could be established details of the connection are shown. |
Writes data to a database table. This function performs opening of the
connection, saving of the data to a table and closing of the connection. If
an error occurs the program stops and an error description is shown. If a
table named tbl already exists in the database mdb the existing
table is only overwritten if overwrite is TRUE. Otherwise a
hsSafeName will be found for the table.
hsPutTable( mdb, myData, tbl = "tblTmp", types = NULL, overwrite = FALSE, DBMSencoding = "", dbg = TRUE )hsPutTable( mdb, myData, tbl = "tblTmp", types = NULL, overwrite = FALSE, DBMSencoding = "", dbg = TRUE )
mdb |
full path to MS Access database file (*.mdb) |
myData |
data.frame containing data to be written to database table |
tbl |
Name of table to be created in the database |
types |
field types to be passed to sqlSave as argument varTypes, see ?sqlSave for details. |
overwrite |
shall existing table be overwritten? |
DBMSencoding |
finally passed to |
dbg |
if TRUE, debug messages are shown |
In case of success the name of the created table is returned.
## Not run: ## Create a data.frame df1 <- data.frame(id = 1:4, rnd = sample(1:100)[1:4]) ## Write data.frame into a table in the example database; as no ## table name is specified, a table name is generated. The name ## of the table is returned. ## (only on Windows!) if (.Platform$OS.type == "windows") { tbl <- hsPutTable(xmdb(), df1) tbl # table name here: [1] "tblTmp" ## Get the data from the created table back again and print the ## data. As we see, a table containing four different random ## numbers between one and 100 has been created. df2 <- hsGetTable(xmdb(), tbl) df2 } ## Output: # id rnd # 1 1 82 # 2 2 14 # 3 3 99 # 4 4 6 ## End(Not run)## Not run: ## Create a data.frame df1 <- data.frame(id = 1:4, rnd = sample(1:100)[1:4]) ## Write data.frame into a table in the example database; as no ## table name is specified, a table name is generated. The name ## of the table is returned. ## (only on Windows!) if (.Platform$OS.type == "windows") { tbl <- hsPutTable(xmdb(), df1) tbl # table name here: [1] "tblTmp" ## Get the data from the created table back again and print the ## data. As we see, a table containing four different random ## numbers between one and 100 has been created. df2 <- hsGetTable(xmdb(), tbl) df2 } ## Output: # id rnd # 1 1 82 # 2 2 14 # 3 3 99 # 4 4 6 ## End(Not run)
Set foreign key constraint for a table field
hsSetForeignKey( mdb, tbl, field, ref.tbl, ref.field, key.name = .getForeignKeyName(tbl, field, ref.tbl, ref.field), dbg = FALSE )hsSetForeignKey( mdb, tbl, field, ref.tbl, ref.field, key.name = .getForeignKeyName(tbl, field, ref.tbl, ref.field), dbg = FALSE )
mdb |
full path to MS Access database file (*.mdb) |
tbl |
name of table containing the field for which the foreign key constraint is to be defined |
field |
name of table field for which the foreign key constraint is to be defined |
ref.tbl |
name of table containing the referenced foreign key field |
ref.field |
name of foreign key field in ref.tbl |
key.name |
optional. Name to be given to the foreign key |
dbg |
passed to |
Sets fields with names given in vector keyFields as key fields of table tbl in MS ACCESS database mdb.
hsSetPrimaryKey(mdb, tbl, keyFields, dbg = FALSE)hsSetPrimaryKey(mdb, tbl, keyFields, dbg = FALSE)
mdb |
Full path to MS Access database file (*.mdb). |
tbl |
Name of table in which key fields are to be defined. |
keyFields |
(Vector of) key field name(s) |
dbg |
if TRUE, debug messages are shown |
Creates a boolean expression of the form bFunc(x1) AND bFunc(x2)
AND ... AND bFunc(xn). This function can be used to create SQL queries where
many table fields have to be checked in the same way for some criterion (see
example).
hsSqlExAnd(x, bFunc)hsSqlExAnd(x, bFunc)
x |
vector of strings, e.g. representing table field names. |
bFunc |
name of a boolean function to be “applied” to each element of x. |
## Build SQL query finding records in table t in which all ## of the table fields f1 to f100 are NULL. sql <- sprintf("SELECT * FROM t WHERE %s", hsSqlExAnd(paste("f", 1:100, sep = ""), "isNull")) ## Show the SQL string sql ## Output (middle part omitted): # SELECT * FROM t WHERE (TRUE) AND isNull(f1) AND # isNull(f2) AND isNull(f3) AND ... AND isNull(f100)## Build SQL query finding records in table t in which all ## of the table fields f1 to f100 are NULL. sql <- sprintf("SELECT * FROM t WHERE %s", hsSqlExAnd(paste("f", 1:100, sep = ""), "isNull")) ## Show the SQL string sql ## Output (middle part omitted): # SELECT * FROM t WHERE (TRUE) AND isNull(f1) AND # isNull(f2) AND isNull(f3) AND ... AND isNull(f100)
Creates a boolean expression of the form bFunc(x1) OR bFunc(x2) OR
... OR bFunc(xn). This function can be used to create SQL queries where many
table fields have to be checked in the same way for some criterion (see
example).
hsSqlExOr(x, bFunc = "")hsSqlExOr(x, bFunc = "")
x |
vector of strings, e.g. representing table field names. |
bFunc |
name of a boolean function to be “applied” to each element of x. |
## Build SQL query finding records in table t in which at least ## one of the table fields f1 to f100 is NULL. sql <- sprintf("SELECT * FROM t WHERE %s", hsSqlExOr(paste("f", 1:100, sep = ""), "isNull")) ## Show the SQL string sql ## Output (middle part omitted): # SELECT * FROM t WHERE (FALSE) OR isNull(f1) OR # isNull(f2) OR isNull(f3) OR ... OR isNull(f100)## Build SQL query finding records in table t in which at least ## one of the table fields f1 to f100 is NULL. sql <- sprintf("SELECT * FROM t WHERE %s", hsSqlExOr(paste("f", 1:100, sep = ""), "isNull")) ## Show the SQL string sql ## Output (middle part omitted): # SELECT * FROM t WHERE (FALSE) OR isNull(f1) OR # isNull(f2) OR isNull(f3) OR ... OR isNull(f100)
WHERE-condition string in MS Jet SQL syntax filtering for a specific time interval
hsSqlExTimeCond( tsField, dateFirst = NULL, dateLast = NULL, inclLast = TRUE, sqlDialect = getCurrentSqlDialect(), dbg = FALSE )hsSqlExTimeCond( tsField, dateFirst = NULL, dateLast = NULL, inclLast = TRUE, sqlDialect = getCurrentSqlDialect(), dbg = FALSE )
tsField |
name of timestamp field |
dateFirst |
Date object representing the first date of the time interval to be selected. |
dateLast |
Date object representing the last date of the time interval to be selected. |
inclLast |
if |
sqlDialect |
one of |
dbg |
if |
Condition string in MS Jet SQL syntax to be used in WHERE clause
## Not run: ## Condition string to filter field "datetime" for timestamps ## between 21 and 22 of July 2010 from <- as.Date("2011-08-23") to <- as.Date("2011-08-24") cond <- hsSqlExTimeCond("Zeitst", from, to) cond ## Output: # TRUE AND Zeitst >= #08/23/2011 00:00:00# # AND Zeitst <= #08/24/2011 00:00:00# ## The condition string may now be used in an SQL query ## to select data from within the time interval. sql <- sprintf("SELECT * FROM tbl_Hyd WHERE %s", cond) if (.Platform$OS.type == "windows") { res <- hsSqlQuery(xmdb(), sql) head(res) } ## Output: # Zeitst Q v H T_Kanal # 1 2011-08-23 00:00:00 0 0 1.260 19.5 # 2 2011-08-23 00:01:00 0 0 1.259 19.5 # 3 2011-08-23 00:02:00 0 0 1.259 19.5 # 4 2011-08-23 00:03:00 0 0 1.259 19.5 # 5 2011-08-23 00:04:00 0 0 1.260 19.5 # 6 2011-08-23 00:05:00 0 0 1.260 19.5 ## End(Not run)## Not run: ## Condition string to filter field "datetime" for timestamps ## between 21 and 22 of July 2010 from <- as.Date("2011-08-23") to <- as.Date("2011-08-24") cond <- hsSqlExTimeCond("Zeitst", from, to) cond ## Output: # TRUE AND Zeitst >= #08/23/2011 00:00:00# # AND Zeitst <= #08/24/2011 00:00:00# ## The condition string may now be used in an SQL query ## to select data from within the time interval. sql <- sprintf("SELECT * FROM tbl_Hyd WHERE %s", cond) if (.Platform$OS.type == "windows") { res <- hsSqlQuery(xmdb(), sql) head(res) } ## Output: # Zeitst Q v H T_Kanal # 1 2011-08-23 00:00:00 0 0 1.260 19.5 # 2 2011-08-23 00:01:00 0 0 1.259 19.5 # 3 2011-08-23 00:02:00 0 0 1.259 19.5 # 4 2011-08-23 00:03:00 0 0 1.259 19.5 # 5 2011-08-23 00:04:00 0 0 1.260 19.5 # 6 2011-08-23 00:05:00 0 0 1.260 19.5 ## End(Not run)
Generates SQL code for grouping timestamps by years, months or days
hsSqlExTimeGroup(tbl, tsField, interval, cond = "TRUE")hsSqlExTimeGroup(tbl, tsField, interval, cond = "TRUE")
tbl |
name of the table |
tsField |
name of the table field containing the timestamp |
interval |
specifies the time period to group by ("y": years, "m": months, "d": days) |
cond |
additional condition in SQL syntax |
Returns SQL code for grouping timestamps by years, months or days
## Show SQL query that gets the number of datasets per ## day ("d") considering the condition "Q > 0" hsSqlExTimeGroup("myTable", "myTimestamp", "d", "Q > 0") ## Output (reformatted): ## SELECT DateSerial(Year(hsTS), Month(hsTS), Day(hsTS)) ## AS myInterval, Count(*) AS myCount ## FROM ( ## SELECT myTimestamp AS hsTS FROM myTable WHERE Q > 0 ## ) ## GROUP BY DateSerial(Year(hsTS), Month(hsTS), Day(hsTS)) ## ORDER BY DateSerial(Year(hsTS), Month(hsTS), Day(hsTS))## Show SQL query that gets the number of datasets per ## day ("d") considering the condition "Q > 0" hsSqlExTimeGroup("myTable", "myTimestamp", "d", "Q > 0") ## Output (reformatted): ## SELECT DateSerial(Year(hsTS), Month(hsTS), Day(hsTS)) ## AS myInterval, Count(*) AS myCount ## FROM ( ## SELECT myTimestamp AS hsTS FROM myTable WHERE Q > 0 ## ) ## GROUP BY DateSerial(Year(hsTS), Month(hsTS), Day(hsTS)) ## ORDER BY DateSerial(Year(hsTS), Month(hsTS), Day(hsTS))
Generates SQL code for selecting different parts of the timestamp
hsSqlExTsFields(tsField, extraTsFields = 0:11)hsSqlExTsFields(tsField, extraTsFields = 0:11)
tsField |
name of the table field containing the timestamp |
extraTsFields |
vector of integers representing different types of
time-stamp information: |
Returns SQL code for selecting different types of information on the time stamp.
hsSqlExTsFields("myTimestamp", c(6:11)) ## Output (re-formatted): # "Year(myTimestamp) AS myTimestamp_year, # Month(myTimestamp) AS myTimestamp_month, # Day(myTimestamp) AS myTimestamp_day, # Hour(myTimestamp) AS myTimestamp_h, # Minute(myTimestamp) AS myTimestamp_m, # Second(myTimestamp) AS myTimestamp_s"hsSqlExTsFields("myTimestamp", c(6:11)) ## Output (re-formatted): # "Year(myTimestamp) AS myTimestamp_year, # Month(myTimestamp) AS myTimestamp_month, # Day(myTimestamp) AS myTimestamp_day, # Hour(myTimestamp) AS myTimestamp_h, # Minute(myTimestamp) AS myTimestamp_m, # Second(myTimestamp) AS myTimestamp_s"
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 = "", ... )hsSqlQuery( mdb, sql, use2007Driver = NULL, dbg = TRUE, stopOnError = TRUE, DBMSencoding = "", ... )
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 |
... |
additional arguments to be passed to |
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.
## 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)## 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)
Returns a data.frame as returned by sqlTables, containing information on the tables contained in the database. Opening of the database connection, getting the list of tables and closing of the database connection is done within this function.
hsTables( mdb, excludeSystemTables = grepl("\\.(mdb|accdb)$", mdb), namesOnly = TRUE, use2007Driver = NULL, dbg = FALSE )hsTables( mdb, excludeSystemTables = grepl("\\.(mdb|accdb)$", mdb), namesOnly = TRUE, use2007Driver = NULL, dbg = FALSE )
mdb |
full path to MS Access database file (extension “.mdb” or “.accdb”) or MS Excel file (extension “.xls” or “.xlsx”). |
excludeSystemTables |
if TRUE (default), system tables are excluded from the table list, else included. |
namesOnly |
if TRUE, only table names are returned. Default: TRUE |
use2007Driver |
if TRUE the functions odbcConnectAccess2007 and odbcConnectExcel2007 are used instead of odbcConnectAccess and odbcConnectExcel, respectively |
dbg |
if TRUE, debug messages are shown |
data.frame with columns TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS, see sqlTables of RODBC package.
## Not run: ## Get names of tables in the example database ## (only on Windows) if (.Platform$OS.type == "windows") { tnames <- hsTables(xmdb(), namesOnly = TRUE) ## Exclude system tables by filtering for table names ## not starting with '^MSys' tNonSys <- grep("^MSys", tnames, invert = TRUE, value = TRUE) ## Print the names of the non-system tables. cat(paste(tNonSys, "\n")) } ## Ouput: # tbl_Hyd # tbl_Qua # ... ## End(Not run)## Not run: ## Get names of tables in the example database ## (only on Windows) if (.Platform$OS.type == "windows") { tnames <- hsTables(xmdb(), namesOnly = TRUE) ## Exclude system tables by filtering for table names ## not starting with '^MSys' tNonSys <- grep("^MSys", tnames, invert = TRUE, value = TRUE) ## Print the names of the non-system tables. cat(paste(tNonSys, "\n")) } ## Ouput: # tbl_Hyd # tbl_Qua # ... ## End(Not run)
Available timestamp-field(s) in database table
hsTsField(src, tbl, namesOnly = TRUE, all = FALSE)hsTsField(src, tbl, namesOnly = TRUE, all = FALSE)
src |
source file (MS Access or Excel) |
tbl |
table name |
namesOnly |
if TRUE, only the name(s) of the timestamp field(s) is (are) returned, otherwise additional information. |
all |
if TRUE, all timestamp fields are considiered, otherwise only the first timestamp field. |
Is this an XLS file?
isExcel2003File(filepath)isExcel2003File(filepath)
filepath |
(vector of) path(s) to the file(s) to be checked for .xls extension |
(vector of) logical.
Is this an XLSX file?
isExcel2007File(filepath)isExcel2007File(filepath)
filepath |
(vector of) path(s) to the file(s) to be checked for .xlsx extension |
(vector of) logical.
Is this an Excel file?
isExcelFile(filepath)isExcelFile(filepath)
filepath |
(vector of) path(s) to the file(s) to be checked for .xls or .xlsx extension |
(vector of) logical.
Is the Given Database of Type MySQL?
isMySQL(db, ..., con = NULL)isMySQL(db, ..., con = NULL)
db |
database file (*.mdb, *.accdb, *.xls, *.xlsx) or name of ODBC database |
... |
arguments passed to |
con |
connection object as returned by |
TRUE if db is a MySQL database, else FALSE
List of Key = Value Pairs to SQL Expressions
keyValuesToSql(keyValues, filter, like = filter)keyValuesToSql(keyValues, filter, like = filter)
keyValues |
list of |
filter |
logical. If |
like |
passed to |
keyValues <- list(name = "Peter", birth = as.POSIXct("1999-09-09")) setCurrentSqlDialect("msaccess") cat(keyValuesToSql(keyValues, filter = TRUE)) cat(keyValuesToSql(keyValues, filter = TRUE, like = FALSE)) cat(keyValuesToSql(keyValues, filter = FALSE)) setCurrentSqlDialect("mysql") cat(keyValuesToSql(keyValues, filter = TRUE)) cat(keyValuesToSql(keyValues, filter = TRUE, like = FALSE)) cat(keyValuesToSql(keyValues, filter = FALSE))keyValues <- list(name = "Peter", birth = as.POSIXct("1999-09-09")) setCurrentSqlDialect("msaccess") cat(keyValuesToSql(keyValues, filter = TRUE)) cat(keyValuesToSql(keyValues, filter = TRUE, like = FALSE)) cat(keyValuesToSql(keyValues, filter = FALSE)) setCurrentSqlDialect("mysql") cat(keyValuesToSql(keyValues, filter = TRUE)) cat(keyValuesToSql(keyValues, filter = TRUE, like = FALSE)) cat(keyValuesToSql(keyValues, filter = FALSE))
List of Key = Value Pairs to SQL Assignment
keyValuesToSqlAssignment(keyValues)keyValuesToSqlAssignment(keyValues)
keyValues |
list of |
Key Values to SQL Assignment (2)
keyValuesToSqlAssignment2(keyvalues)keyValuesToSqlAssignment2(keyvalues)
keyvalues |
list of |
list with elements fieldList and valueList
List of Key = Value Pairs to SQL Filter Expression
keyValuesToSqlFilter(keyValues, like = FALSE)keyValuesToSqlFilter(keyValues, like = FALSE)
keyValues |
list of |
like |
if |
Generate SQL Filter or Assignment Expression
keyValueToSql(cname, cvalue, like = TRUE, filter = TRUE)keyValueToSql(cname, cvalue, like = TRUE, filter = TRUE)
cname |
field name |
cvalue |
field value |
like |
if |
filter |
if |
(vector of) character representing an SQL expression
cat(kwb.db:::keyValueToSql("age", 1)) cat(kwb.db:::keyValueToSql("name", "peter")) cat(kwb.db:::keyValueToSql("name", "peter", like = FALSE))cat(kwb.db:::keyValueToSql("age", 1)) cat(kwb.db:::keyValueToSql("name", "peter")) cat(kwb.db:::keyValueToSql("name", "peter", like = FALSE))
Create SQL Tuples from a List
listValuesToSql(x)listValuesToSql(x)
x |
a list defining |
vector of character strings each of which represents one assignment
in x
x <- list(name = "Peter", birthday = as.POSIXct("1981-12-13")) setCurrentSqlDialect("msaccess") cat(listValuesToSql(x)) # Note that the representation of a date and time is different in MySQL setCurrentSqlDialect("mysql") cat(listValuesToSql(x))x <- list(name = "Peter", birthday = as.POSIXct("1981-12-13")) setCurrentSqlDialect("msaccess") cat(listValuesToSql(x)) # Note that the representation of a date and time is different in MySQL setCurrentSqlDialect("mysql") cat(listValuesToSql(x))
Looks up a record in a database table and returns the ID of the record or NULL if the record does not exist.
lookupRecord( db, tableName, keyAssignments, idField = hsFields(db, tableName)[1], dbg = FALSE, use2007Driver = NULL )lookupRecord( db, tableName, keyAssignments, idField = hsFields(db, tableName)[1], dbg = FALSE, use2007Driver = NULL )
db |
full path to MS Access/Excel database or name of ODBC data source |
tableName |
name of table in which record is to be looked up |
keyAssignments |
key-value-assignments used to identify the record to be looked up. The assignments are defined in forms of a list, e.g. list(key1 = "value1", key2 = "value2"). |
idField |
name of ID field, default: name of first table field |
dbg |
if TRUE, debug messages are shown |
use2007Driver |
passed to |
Merge Table Schema with Relationship Information
merge_relations(schemata, relations)merge_relations(schemata, relations)
schemata |
list as returned by |
relations |
data frame as returned by |
Open Adequate Connection or Stop
openAdequateConnectionOrStop( db, use2007Driver = NULL, dbg = FALSE, DBMSencoding = "", ... )openAdequateConnectionOrStop( db, use2007Driver = NULL, dbg = FALSE, DBMSencoding = "", ... )
db |
database name or file |
use2007Driver |
if TRUE the functions odbcConnectAccess2007 and odbcConnectExcel2007 are used instead of odbcConnectAccess and odbcConnectExcel, respectively |
dbg |
if |
DBMSencoding |
finally passed to |
... |
further arguments passed to |
Print Database Schema
printDatabaseSchema(dbSchema)printDatabaseSchema(dbSchema)
dbSchema |
database schema as returned by
|
R-code for Functions to Access Database Tables
rcode_DatabaseInterface( db = NULL, dbSchema = NULL, tableNamePattern = "", functionPrefix = "db_", pattern = "^", dbg = TRUE, sql.dbg = TRUE, create.create.functions = FALSE )rcode_DatabaseInterface( db = NULL, dbSchema = NULL, tableNamePattern = "", functionPrefix = "db_", pattern = "^", dbg = TRUE, sql.dbg = TRUE, create.create.functions = FALSE )
db |
database name or file |
dbSchema |
database schema as returned by
|
tableNamePattern |
pattern matching the names of tables/views for which an accessor function is to be generated |
functionPrefix |
prefix to be given to each generated function. Default: "db_" |
pattern |
pattern matching the part of the table name that will be replaced with functionPrefix. Default: "^", i.e. the prefix will be appended to the start of the table name |
dbg |
if TRUE, progress messages are shown during the creation of the interface functions |
sql.dbg |
if TRUE, SQL strings used to access the database are shown when calling the interface functions |
create.create.functions |
if TRUE, functions for creating new records in the database tables are created |
Read Relations from MSysRelationships Table CSV Export
read_relations(file)read_relations(file)
file |
path to "MSysRelationships.csv" as exported from MS Access by right clicking on the (hidden) MSysRelationships table and selecting "Export->Text file->OK" |
data frame with columns table, field, refTable,
refField
This function reads the *_schema.xml files exported to folder TBL by KWB tool mdbToTxt.exe
read_schemata(path, reduce = TRUE)read_schemata(path, reduce = TRUE)
path |
path to folder containing xml files |
reduce |
if |
Lines starting with "–" or "#" are ignored. SQL commands must be separated by semicolon and end of line character (\n).
readSqlCommandsFromFile(sqlScript)readSqlCommandsFromFile(sqlScript)
sqlScript |
full path to file containing SQL commands |
Convert a list of renamings to a field selection string that can be used as
fields argument in a call to hsGetTable
renamesToFieldList( renames, source.in.brackets = TRUE, target.in.brackets = TRUE )renamesToFieldList( renames, source.in.brackets = TRUE, target.in.brackets = TRUE )
renames |
list of key = value pairs defining renamings from the keys to the values |
source.in.brackets |
if |
target.in.brackets |
if |
Convert a list of renamings to a list of SQL queries each of which can be used to select and rename the given fields from a database.
renamesToQueries(renamesList)renamesToQueries(renamesList)
renamesList |
list of renaming lists. A renaming list is a list of
|
list of character each of which represents an SQL query
Run SQL Commands from File
runSqlCommandsFromFile(db, sqlFile, ...)runSqlCommandsFromFile(db, sqlFile, ...)
db |
Full path to MS Access mdb file or ODBC database name |
sqlFile |
full path to file containing Ms Access SQL commands |
... |
further arguments passed to |
MS Access: table name enclosed in brackets "[" and "]", else: table name enclosed in backquotes "'"
safeTableName(tableName, sqlDialect = getCurrentSqlDialect(warn = FALSE))safeTableName(tableName, sqlDialect = getCurrentSqlDialect(warn = FALSE))
tableName |
name of table to be quoted |
sqlDialect |
one of |
Select from Database
selectFromDb( tableName, fields = "*", whereClause = "TRUE", odbc, dbg = TRUE, ... )selectFromDb( tableName, fields = "*", whereClause = "TRUE", odbc, dbg = TRUE, ... )
tableName |
name of database table from which to load data |
fields |
names of fields to be selected |
whereClause |
SQL WHERE condition string |
odbc |
database name or file |
dbg |
if |
... |
additonal arguments passed to |
Select from Table
selectFromTable(db, tableName, arguments, run = TRUE, ...)selectFromTable(db, tableName, arguments, run = TRUE, ...)
db |
database name or file |
tableName |
name of table from which to read data |
arguments |
list with elements |
run |
if TRUE (default) the SQL SELECT statement is run otherwise returned as character string |
... |
further arguments passed to |
Set Current Database
setCurrentDb(db)setCurrentDb(db)
db |
full path to MS Access database or ODBC database name |
Set Current SQL Dialect
setCurrentSqlDialect(dialectName)setCurrentSqlDialect(dialectName)
dialectName |
one of "msaccess", "mysql" |
Generate SQL INSERT statement
sqlForInsert( tablename, fields, sqlSource, sourceAreValues = !grepl(sqlSource, "^SELECT"), ignore = FALSE )sqlForInsert( tablename, fields, sqlSource, sourceAreValues = !grepl(sqlSource, "^SELECT"), ignore = FALSE )
tablename |
table name |
fields |
field names, separated by comma |
sqlSource |
value tupels of form (value1.1, value1.2, value1.3, ...) (value2.1, value2.2, value2.3, ...) ... or SQL SELECT statement providing these tupels |
sourceAreValues |
if TRUE, sqlSource is expected to be an SQL query providing data to be inserted -> no keyword VALUES in generated SQL code |
ignore |
if TRUE the keyword IGNORE is inserted between INSERT and INTO in the SQL statement -> no error will be given if data to insert already exists |
Generate SQL INSERT statement to insert values in a data frame
sqlForInsertDataFrame(tablename, dataFrame, ignore = FALSE, ...)sqlForInsertDataFrame(tablename, dataFrame, ignore = FALSE, ...)
tablename |
table name |
dataFrame |
data frame with column names representing table field names |
ignore |
if TRUE the keyword IGNORE is inserted between INSERT and INTO in the SQL statement -> no error will be given if data to insert already exists |
... |
further arguments passed to |
Generate SQL INSERT statement of the form INSERT INTO target.table (fields) SELECT fields FROM source.table
sqlForInsertFromSelect(target.table, source.table, fields)sqlForInsertFromSelect(target.table, source.table, fields)
target.table |
name of target table |
source.table |
name of source table or SQL providing source data |
fields |
vector of character with field names |
Returns SQL for inserting all records from table.source that are not yet contained in table.target into table.target
sqlForInsertIgnoreInMsAccess(db, table.source, table.target, uniqueFields = NA)sqlForInsertIgnoreInMsAccess(db, table.source, table.target, uniqueFields = NA)
db |
database name or file |
table.source |
name of source table |
table.target |
name of target table |
uniqueFields |
names of unique fields |
Generate SQL SELECT statement#'
sqlForSelect( tablename, fields = "*", whereClause = "TRUE", groupBy = "", orderBy = "", sqlDialect = getCurrentSqlDialect() )sqlForSelect( tablename, fields = "*", whereClause = "TRUE", groupBy = "", orderBy = "", sqlDialect = getCurrentSqlDialect() )
tablename |
table name |
fields |
expression to select fields; field names are separated by comma and alias names may be used, just as SQL accepts, e.g.: "tstamp as myDateTime, parVal as myValue"; Default: "*" |
whereClause |
where condition; Default: "TRUE" |
groupBy |
GROUP BY-clause, Default: "" (no grouping) |
orderBy |
ORDER BY-clause, Default: "" (no sorting of results) |
sqlDialect |
one of |
Generate SQL SELECT statement (key field values instead of where clause)
sqlForSelectByKey(tablename, fields = "*", keyValues = NULL)sqlForSelectByKey(tablename, fields = "*", keyValues = NULL)
tablename |
table name |
fields |
expression to select fields; field names are separated by comma and alias names may be used, just as SQL accepts, e.g.: "tstamp as myDateTime, parVal as myValue"; Default: "*" |
keyValues |
list of "key=value" pairs with the keys being valid field names of table |
Generate SQL UPDATE Statement
sqlForUpdate(tablename, keyValues, whereClause, ignore = FALSE)sqlForUpdate(tablename, keyValues, whereClause, ignore = FALSE)
tablename |
table name |
keyValues |
assignments as list of key=value pairs with the keys representing valid fields of table tablename |
whereClause |
where condition |
ignore |
if TRUE the keyword IGNORE is inserted between UPDATE and INTO in the SQL statement -> no error will be given if updating fails, e.g. because of key constraints |
Merge SQL Queries to One Query That Performs a Full Left Join
sqlFullLeftJoin(sqls, key)sqlFullLeftJoin(sqls, key)
sqls |
list of SQL queries each of which is expected to contain an
attribute |
key |
name of the primary key field, being selected in each SQL query
contained in |
vector of character of length one representing the result of "left
join"-ing all sub-queries given in sqls
sql <- sqlFullLeftJoin(key = "id", list( structure("SELECT id, field_1 from table_1", alias = "t1"), structure("SELECT id, field_2, field_3 from table_2", alias = "t2"), structure("SELECT id, field_4 from table_3", alias = "t3") )) cat(sql)sql <- sqlFullLeftJoin(key = "id", list( structure("SELECT id, field_1 from table_1", alias = "t1"), structure("SELECT id, field_2, field_3 from table_2", alias = "t2"), structure("SELECT id, field_4 from table_3", alias = "t3") )) cat(sql)
Create an SQL JOIN Expression
sqlJoinExpression(left, right, condition, type = "INNER")sqlJoinExpression(left, right, condition, type = "INNER")
left |
left part of JOIN (e.g. table name) |
right |
right part of JOIN (e.g. table name) |
condition |
condition |
type |
one of c("LEFT", "RIGHT", "INNER") |
Generate SQL for LEFT JOIN
sqlLeftJoin(sqlSource, tablesAndIDs, fields = "*")sqlLeftJoin(sqlSource, tablesAndIDs, fields = "*")
sqlSource |
SQL of subquery that provides the "base" table on the left |
tablesAndIDs |
named character vector with the names representing the names of the tables to be joined and the values representing the ID fields of these tables |
fields |
fields to be selected |
Generate (Base Part of) SQL for LEFT JOIN
sqlLeftJoinBody( leftSql, rightTable, id, idLeft = id, useAlias = (id != idLeft), aliasName = "tbase" )sqlLeftJoinBody( leftSql, rightTable, id, idLeft = id, useAlias = (id != idLeft), aliasName = "tbase" )
leftSql |
SQL of subquery that provides the "base" table on the left |
rightTable |
name of "right" table |
id |
name of ID field of "right" table (must correspond to a field returned by leftSql) |
idLeft |
name of ID field of "left" table |
useAlias |
if TRUE, the alias given in aliasName is given to the subquery leftSql. Default: FALSE |
aliasName |
alias name to be used if useAlias is TRUE. Default: "tbase" |
Create an SQL LEFT JOIN Expression
sqlLeftJoinExpression(left, right, condition)sqlLeftJoinExpression(left, right, condition)
left |
left part of JOIN (e.g. table name) |
right |
right part of JOIN (e.g. table name) |
condition |
condition |
Returns full path to MS Access example database
xmdb()xmdb()