run_queries: Execute a sequence of SQL queries

Description

Accepts a character vector of SQL queries and attempts to execute each

Usage

run_queries(sql, ..., default.conn = default_conn(), include_params = FALSE)
runqueries(sql, ..., default.conn = default_conn(), include_params = FALSE)

Value

If include_params is FALSE and the sql argument is a vector, a list containing the results of each query; element names will be taken from the sql argument.
If the length of the sql argument is 1 and is not named, the result of that query is returned as-is (e.g. a data.frame), not as a 1-element list.
If include_params is TRUE, a tibble is returned containing 1 row per query with the following fields:

qname: character. A name for this query

quotesql

"yes" or "no". Should parameterized character values be quoted for this query?

interpolate

"yes" or "no". Should this query be parameterized with values from R?

execmethod

The method to execute this query. One of "get" (DBI::dbGetQuery()), "execute" (DBI::dbExecute()), "sendq" (DBI::dbSendQuery()), "sends" (DBI::dbSendStatement()) or "spatial" (sf::st_read())

geometry

character. If execmethod is "spatial", this should be the name of the geometry column.

conn_name

character. The name of the database connection against which to execute this query. Must be the name of a configured sqlhelper connection.

sql

The sql query to be executed

filename

The value of file_name

prepared_sql

The sql query to be executed, i.e. with interpolations and quoting in place

result

The result of the query

Arguments

sql: An optionally-named list or character vector containing sql strings, or a tibble returned by read_sql() or prepare_sql().
...: Arguments to be passed to read_sql() or prepare_sql()
default.conn: Either the name of a sqlhelper connection, or a database connection returned by DBI::dbConnect() or pool::dbPool(). This connection is used as a fall-back when the sql parameter is a tibble and no per-query connection name is supplied, or the connection name is default (see prepare_sql()). It may be used by glue::glue_sql() to interpolate SQL strings, and as the connection against which to execute SQL queries.
include_params: TRUE or FALSE. Should the parameters be included in the output? Mainly useful for debugging.

Details

If no default connection is supplied via default.conn and no connections have been configured using connect(), an attempt will be made to configure connections via connect() using the configuration search path. If no database connections are available after this attempt, an error will be raised. See vignette("connections") for details about the configuration search path.

Examples

Run this code

library(sqlhelper)

readLines(
    system.file("examples/sqlhelper_db_conf.yml",
                package="sqlhelper")
    ) |>
writeLines()

connect(
    system.file("examples/sqlhelper_db_conf.yml", package="sqlhelper"),
    exclusive=TRUE)

DBI::dbWriteTable( default_conn(),
                  "iris",
                  iris)

n <- 5

run_queries(
    c(top_n = "select * from iris limit {n}",
      uniqs = "select distinct species as species from iris")
)

## use include_params to review the execution context
run_queries(
    c(top_n = "select * from iris limit {n}",
      uniqs = "select distinct species as species from iris"),
   include_params = TRUE
)

## pass an env of interpolation values to the 'values' parameter
## result of a single, unnamed query is returned as an object, not a
## 1-element list
e <- new.env()
e$n <- 2
run_queries(
    "select * from iris limit {n}",
    values = e
)

## Use the execmethod parameter for statements
run_queries("create table iris_setosa as select * from iris where species = 'setosa'",
          execmethod = 'execute')

run_queries("select distinct species as species from iris_setosa")