Parse and execute a tagged SQL workflow

qryflow_run() reads a SQL workflow from a file path or character string, parses it into tagged statements, and executes those statements against a database connection.

This function might be preferable for those who want a qryflow execution to consistently return a qryflow object. Whereas the qryflow() function may return a list or other objects, depending on the arguments, qryflow_run() always returns a qryflow object. Results can be extracted using qryflow_results().

Usage

qryflow_run(
  con,
  sql,
  ...,
  on_error = c("stop", "warn", "collect"),
  verbose = getOption("qryflow.verbose", FALSE),
  default_type = getOption("qryflow.verbose", "query")
)

Arguments

con: A database connection from DBI::dbConnect()
sql: A character string representing either the path to a .sql file or raw SQL content.
...: Additional arguments passed to qryflow_execute().
on_error: Controls behaviour when a chunk fails during execution. One of "stop" (default), "warn", or "collect". "stop" halts execution immediately and returns the partially executed workflow. "warn" records the error in the chunk's meta, signaling immediately. "collect" gathers all errors from across all chunks and reports them at the end.
verbose: Logical. If TRUE, emits a message before each chunk identifying its name and type, and prints a summary on completion reporting total chunks run, successes, errors, and elapsed time. Defaults to FALSE. The global default can be set with options(qryflow.verbose = TRUE).
default_type: The default chunk type (defaults to "query"). The global default can be set with options(qryflow.verbose = TRUE).

Value

(Invisibly) A list representing the evaluated workflow, containing query results, execution metadata, or both, depending on the contents of the SQL script.

Examples

con <- example_db_connect(mtcars)

filepath <- example_sql_path("mtcars.sql")

obj <- qryflow_run(con, filepath)

obj$df_mtcars$sql
#> [1] "SELECT *\nFROM mtcars;\n"
obj$df_mtcars$results
#>     mpg cyl  disp  hp drat    wt  qsec vs am gear carb
#> 1  21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
#> 2  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
#> 3  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
#> 4  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
#> 5  18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2
#> 6  18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1
#> 7  14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4
#> 8  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
#> 9  22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
#> 10 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
#> 11 17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4
#> 12 16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3
#> 13 17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3
#> 14 15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3
#> 15 10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4
#> 16 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4
#> 17 14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4
#> 18 32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
#> 19 30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
#> 20 33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
#> 21 21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
#> 22 15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2
#> 23 15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2
#> 24 13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4
#> 25 19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2
#> 26 27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
#> 27 26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
#> 28 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
#> 29 15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4
#> 30 19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6
#> 31 15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8
#> 32 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2

results <- qryflow_results(obj)

head(results$df_mtcars$results)
#> NULL

DBI::dbDisconnect(con)

Usage

Arguments

Value

See also

Examples