run_files {sqlhelper} | R Documentation |
Read, prepare and execute .SQL files
Description
Accepts a character vector of SQL file names and attempts to execute the queries in each one.
Usage
run_files(filenames, ..., include_params = FALSE)
runfiles(filenames, ..., include_params = FALSE)
Arguments
filenames |
name, or vector of names, of file(s) to be executed |
... |
Arguments to be passed to |
include_params |
|
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.
run_files()
calls read_sql()
on each file, and prepare_sql()
on the
queries read from those files. Prepared queries are executed with run_queries()
. The
behaviour of those functions can be controlled by passing the relevant
parameters to run_files()
as the ...
argument.
run_files()
also enables control of the arguments accepted by run_queries()
on
a per-query basis, by interpreting comments in SQL files as described
for read_sql()
. Interpreted comments precede the sql query to which they
refer. Each interpretable comment must be on a line by itself and take the
form:
-- keyword = value
Keywords and possible values for interpretable comments are:
- qname
A name for this query
- quotesql
"yes" or "no" - should interpolated characters be quoted?
- interpolate
"yes" or "no" - should sql be interpolated?
- execmethod
One of "get", "execute", "sendq", "sends" or "spatial" - which method should be used to execute the query? "get" means
DBI::dbGetQuery()
; "execute" meansDBI::dbExecute()
; "sendq" meansDBI::dbSendQuery
; "sends" meansDBI::dbSendStatement()
; "spatial" meanssf::st_read()
.- geometry
The name of a spatial column. Ignored if
execmethod
is not 'spatial'- conn_name
The name of a connection to execute this query against
All interpreted comments except qname
are cascaded within their file,
meaning that if you want to use the same values throughout, you need only set
them for the first query. See read_sql()
for details.
Value
A list of results of sql queries found in files
See Also
Other SQL runners:
run_queries()
Examples
library(sqlhelper)
config_filename <- system.file("examples/sqlhelper_db_conf.yml",
package="sqlhelper")
readLines( config_filename ) |> writeLines()
connect(
config_filename,
exclusive=TRUE)
DBI::dbWriteTable( default_conn(), "iris", iris)
sf::st_write(spData::congruent, default_conn(), "congruent")
sf::st_write(spData::incongruent, live_connection("pool_sqlite"), "incongruent")
run_files_ex1 <- system.file("examples/run_files_ex1.sql", package="sqlhelper")
readLines( run_files_ex1 ) |> writeLines()
run_files_ex2 <- system.file("examples/run_files_ex2.sql", package="sqlhelper")
readLines( run_files_ex2 ) |> writeLines()
n_longest_petals <- 5
results <- run_files( c( run_files_ex1, run_files_ex2 ) )
names(results)
results$how_many_irises
results$n_longest_setosa_petal_lengths
plot(results$get_congruent, border = "orange")
plot(results$get_incongruent, border = "blue", add=TRUE)