sql_varsub: Substitute Values into a SQL Query.

In ds4ci/ds4ciMisc: Miscellaneous Functions for DS4CI Projects

Description

Substitutes given values into a SQL query string containing embeded dbVis style variables. The simple variable formats are ${name}$ and, when a the variable has a default, ${name||default}$.

Usage

sql_varsub(query, values, collapse = TRUE)

Arguments

query	string The query with embeded dbVis variables.
values	A list of named value(s) where the name(s) are variable names in the query.
collapse	= TRUE Run sql_collapse() as first step.

Details

dbVisualizer is Jim's prefered SQL front end tool for SQL DBMS work. One nice feature is it supports named variables in query which are filled in at run time via a dialog box. This makes it very easy to develop paramertized general purpose SQL queries which can be cut & pasted into R or, preforably, stored in your project's SQL folder ready for readLines() into a string and then paramatized with sql_varsub.

If the query string has a variable which is not named in the values list, it is filled with the default for the variable. If a variable is used multiple times in the query, only one instance will need to have the default. When there is no defaults for a variable not named in the values list, an an error is thrown.

See dbVis help page by Googling #' "dbvisualizer variables x.x", where x.x is the verion of dbVis.

Note that the list values may have unused elements. Thus, best practice is to have a common list for a set of similar queries (eg SELECT, COPY, TRUNCATE, etc)

Examples

## Not run: 
# simple query
sql_varsub("SELECT COUNT(*) FROM ${table}", list(table = "my_table"))
# more realistic
(q <- readLines('./SQL/qVariablesInQueryExample1.sql'))
[1] "-- qVariablesInQueryExample1.sql"
[2] ""
[3] "SELECT COUNT(DISTINCT ${column||product_id}$) AS number_unique_${column}$"
[4] "  FROM ${table}$"
[5] ";"
v <- list(table = "sleeping_dogs.sd_text", column = "date_is")
(q <- sql_varsub(q, v))
[1] "SELECT COUNT(DISTINCT date_is) AS number_unique_date_is   FROM sleeping_dogs.sd_text ;"
dbGetQuery(conn, q)    ## assuming connection is set up already

## End(Not run)

ds4ci/ds4ciMisc documentation built on May 15, 2019, 2:56 p.m.