sendQuery: Send query to database and fetch result

In INWT/dbtools: Tools for Querying Databases

Send query to database and fetch result

Description

This functions sends a query to a database and fetches the result.

Usage

sendQuery(db, query, ...)

## S4 method for signature 'CredentialsList,SingleQueryList'
sendQuery(db, query, ..., applyFun = lapply, simplify = TRUE)

## S4 method for signature 'CredentialsORCredentialsList,character'
sendQuery(db, query, ...)

## S4 method for signature 'Credentials,SingleQueryList'
sendQuery(db, query, ..., simplify = TRUE)

## S4 method for signature 'DBIConnection,SingleQuery'
sendQuery(db, query, ...)

## S4 method for signature 'MySQLConnection,SingleQuery'
sendQuery(db, query, ..., encoding = "utf8mb4")

## S4 method for signature 'MariaDBConnection,SingleQuery'
sendQuery(db, query, ..., encoding = "utf8mb4", tz = "Europe/Berlin")

Arguments

db	one in: (Credentials) the credentials to get a connection to a database. (DBIConnection) DBIConnection-class (MySQLConnection) MySQLConnection-class
query	one in: (character, length >= 1) a query (SingleQuery | SingleQeuryList) SingleQuery-class is mostly used internally.
...	one in: for signature (Credentials, character | SingleQueryList) arguments are passed to reTry for signature (CredentialsList) arguments are passed to the (Credentials) method, so implicitly to reTry else ignored
applyFun	(function) something like lapply or mclapply
simplify	(logical(1)) whether to simplify results. See details.
encoding	(character | NULL) the encoding used in a SET NAMES statement. Currently only implemented for MySQL connections. The default is 'utf8'. Use NULL if you do not want to set the encoding.
tz	timezone of the server. Only used for MariaDB driver

Details

simplify the default is to simplify results. If you send multiple queries to one database it is tried to rbind the results - when you have different column names this can be like a full join. If you send one query to multiple databases it is tried to rbind the results. If you send multiple queries to multiple databases, then first the results of the same query are tried to be rbind, and if possible also the results of each query. It is considered to be possible iff the names of all data frames belonging to each query are the same.

Value

one in:
simplify = TRUE (list | data.frame)
simplify = FALSE: (list) with data frames or nested list of data frames
On error: (list) with 'try-catch' objects

Examples

## For an example database:
library("RSQLite")
con <- dbConnect(SQLite(), "example.db")
USArrests$State <- rownames(USArrests)
dbWriteTable(con, "USArrests", USArrests, row.names = FALSE)
dbDisconnect(con)

## Simple Query
cred <- Credentials(drv = SQLite, dbname = "example.db")
dat <- sendQuery(cred, "SELECT * FROM USArrests;")

## Multiple Similar Queries
queryFun <- function(state) {
  paste0("SELECT * FROM USArrests WHERE State = '", state, "';")
}

sendQuery(cred, queryFun(dat$row_names))

## For the Paranoid
### be a bit more cautious with connections
dat <- try(sendQuery(
  cred,
  "SELECT * FROM USArrest;", # wrong name for illustration
  tries = 2,
  intSleep = 1
))

INWT/dbtools documentation built on Aug. 21, 2022, 9:37 p.m.