Hybrid Script Syntax
This material does not describe a function, but (instead) the SQRL
scripting syntax for SQL with embedded .
For instructions on how to submit (run) these scripts from file, refer to sqrlUsage.
use database;
select * from table;
Multi-statement SQL scripts can be taken directly from SQL Developer (or similar application), and (usually) run without modification. The SQRL parser identifies statement-terminating semicolons, and submits each statement in turn, as those semicolons (or the end of the script) are encountered.
select isotope from periodic_table
where atomic_number = <R> Z </R>
Scripts can be parameterised, via embedded . In SQRL , XML -style tags mark the beginning and end of an
block. As shown in sqrlUsage, values can either be passed explicitly, or inherited from the calling environment. Embedded is not restricted to the insertion of values for filtering, and can be used to specify column names, clauses, or entire SQL statements.
Tags are not case sensitive.
/* SQL-comment within SQL */
-- SQL-comment within SQL
select
<R>
/* SQL-comment within R */
-- SQL-comment within R
# R comment within R
letter <- sample(letters, 1)
paste0("'", letter, "'")
</R>
The parser supports comments, and two kinds of SQL comments. For syntax-highlighting purposes, SQL comments may be used within
sections (but comments cannot be used within SQL ). Since a -- b is legitimate , it would need to be rewritten with a space between the minus signs, or as a + b, to avoid being interpreted as a comment.
Otherwise-arbitrary script is allowed within (tag-delimited) sections. Evaluation of such script takes place in a temporary child environment of the calling environment. The final result of that evaluation is then pasted into the surrounding SQL , before its submission. This mechanism cannot be used to insert additional tags into the SQRL
script.
<R>
threshold <- 9000
sq <- function(x) paste0("'", x, "'")
<do> -- This tag terminates the R section.
<R>
if (!exists("date", inherits = FALSE))
date <- format(Sys.Date(), "%Y-%m-%d");
; -- This semicolon terminates the R section.
select * from database.table
where event_date = <R> sq(date) </R>
and event_size > <R> threshold </R>
As in scripts #02 and #03, when an section ends with </R>, the result of evaluating that section is pasted into the surrounding SQL (or implied SQL , if those surroundings are blank). However, an section can also be terminated by a <do> tag, or by a non-syntactical semicolon (that being one with nothing but whitespace between it and the previous semicolon, newline, or <R> tag). Such sections are evaluated in the same way as those ending in </R> tags, but the results are not pasted into SQL . These sections can only appear between, not within, SQL statements.
In this script, the first out-of-SQL section defines a numerical constant, and also a function for wrapping strings in single quotes. The second out-of-SQL section supplies a default value for a variable, date , if none was explicitly passed in (see sqrlUsage). These two sections could be combined into one. The function and values from these sections are applied in the subsequent SQL .
<with>
na.strings = c('NA', "-") -- comment
x <- 5; max = x + 1 /* comment */
verbose = TRUE # comment
as.is = "configfile.txt" # read from file
</with>
-- Run this query with the above settings.
select * from database.table
Tags <with> and </with> delimit special-purpose blocks of . They are used to set temporary parameter values, which remain in effect only for the duration of the script. To be clear, temporary values survive beyond the </with> tag, but not beyond the end of the script. The exception is that temporary values of ping are retained if and only if the connection would otherwise be left open without a defined ping .
This mechanism only works for a limited number of parameters: aCollapse , as.is , buffsize , dec , errors , lCollapse , max , na.strings , nullstring , ping , pwd , retry , scdo , stringsAsFactors , and verbose .
select 1 from dual; -- This semicolon terminates the statement.
declare
x integer := 0;
begin
for i in 1..10 loop
x := x + 1;
if x > 7 then
x := 0;
else
begin
null;
end;
end if;
end loop;
end; -- This semicolon terminates the block.
select 2 as N from dual
As with statement-terminating semicolons (refer to script #01), the SQRL
parser attempts to identify procedural-language block-terminating semicolons, and submits each such block as those semicolons are encountered. The parser also submits (when necessary) upon reaching the end of the script. Although trailing semicolons are not usually mandatory for SQL
statements, they usually are mandatory at the end of a procedural block.
If you have a script that came with forward-slashes, /, at the end of procedural blocks, those slashes will normally need to be removed (as, say, SQL Developer would do for you).
When a script contains multiple statements and/or blocks, only the final result is returned. In this case, that would be data.frame(N = 2L).
-- Change parser to fallback mode.
<with>
scdo = FALSE
</with>
-- An outside-of-SQL R section.
<R>
N <- 2;
<do> -- This tag terminates the section.
-- The default (scdo = TRUE) SQRL parser would
-- not find the end of this procedural block.
begin
null;
end /**/ ;
<do> -- This tag terminates the block.
select 1 from dual; -- Semicolon ignored.
<do> -- This tag terminates the statement.
select <R> N </R> from dual
While the parser detects terminal semicolons at the ends of SQL
statements and sections, it remains unsophisticated, and ignorant of DBMS -specific procedural-language syntaxes. That being the case, if you are working with procedural-language extensions to SQL , sooner or later the parser will fail to detect the end of a block. Presently, a simple thing that causes this is the presence of a comment between an end and its semicolon (as appears, above).
Setting the scdo parameter to FALSE causes the parser to ignore semicolons and to conclude (out of SQL ) sections, SQL statements, and procedural blocks only upon encountering a <do> tag. This provides a robust multi-statement capability when need be, but will require appropriate modification of any scripts originally developed for some other application.
Note that <do> tags function irrespective of the scdo setting. That being the case, the use of these can be preferable to semicolons when a SQRL script is developed from scratch. When scdo is TRUE, and a terminating semicolon is followed by a <do> tag, with nothing but whitespace between them, they are treated as a single <do> (only one submission is made). An implied do tag exists at the end of every script.
select
calDate, Snowfall
from
Weather.SparseDailySnowfall
where
calYear = <R> year </R>
-- Submit the above, assign the result to 'a',
-- and immediately begin an R section.
<result -> var>
names(var)[names(var) == "calDate"] <- "Date"
var$Snowfall <- as.numeric(var$Snowfall)
first <- as.Date(paste(year, 1, 1, sep = "-"))
last <- as.Date(paste(year, 12, 31, sep = "-"))
alldates <- data.frame(Date = seq(first, last, 1))
merge(alldates, var, all.x = TRUE)
A <result> tag acts as a combination of the <do> and <R>
tags, wherein the result of the query is assigned to an object within the working (script evaluation) environment. Any syntactically valid variable name could be used in place of var . Whilst the main tag is not case sensitive, the name of the variable is.
An section begins immediately after the <result> tag. This can be useful when preferred column names are reserved SQL
keywords (such as date ), when dates come back in unconventional formats (and need conversion), when strict type-conversion control is required (typically in combination with as.is = TRUE), or when data is sparse (zero-valued entries are not stored) and results need to be expanded to explicitly include the implied zero-valued data. The net effect is a single script, combining SQL data extraction with reformatting.
Using null or NULL as the variable name stops assignment of the query result (but the section still begins).
Only the final value of any SQRL script is returned. In this example, that value is the merged data frame.
Note that <result> tags cannot be used to conclude sections. Also, sections beginning with <result> tags cannot end in </R>.
When scdo is TRUE, and a terminating semicolon is followed by a <result> tag, with nothing but whitespace between them, they are treated as a single <result> tag (only one query submission is made). However, when a <do> tag is followed by a <result> tag, two submissions are made, irrespective of scdo . First, the query before the <do> is submitted, and then the (blank) query between the <do> and the <return>. In SQRL, blank queries (blank SQL statements) are always allowed, and return no value.
select distinct customer_number from our.customers
where town in (<R> paste0("'", towns, "'") </R>)
<result -> z>; -- Same thing as <result -> z><do>
select sum(transactions) from online.orders
where customer_number in (<R> z$customer_number </R>)
This script takes one parameter, towns , a character vector. By default, atomic vectors are pasted into SQL as comma-separated values. The first query returns a data frame, which we save, as z , but have no need to manipulate. Its (integer-valued) customer_number column is then applied to the second query, once again as comma-separated values. Only the final result (of the second query) is returned.
Of course, this particular example could instead have been implemented with a join, or temp table.
The pasting behaviour of atomic objects is controlled by the aCollapse
parameter. Similarly, the pasting behaviour of list-like objects is controlled by the lCollapse parameter (which defaults to the empty string). To restore SQRL-0.6.3 pasting rules, insert <with> aCollapse = "\n"; lCollapse = ", " </with> at the top of a script. See sqrlUsage and sqrlParams for further detail.
<R>
n <- OtherSource("select distinct customer_number ",
"from our.customers where town in "
"(" paste0("'", towns, "'") ")")
<do>
select sum(transactions) from online.orders
where customer_number in (<R> n$customer_number </R>)
This is a repeat of script #09, except now the tables of the first and second queries are hosted on two entirely separate data sources. The function OtherSource is the SQRL interface to the data source of the first query, while this script should be run from the interface to the final source. Again, there are other ways to do this, but the general idea is for the main
script (performing the modelling and analysis) to make a single call get me this data , without clutter, or concern for the true horror of where that data comes from.
<R>
-- Check this is the intended source.
stopifnot(sqrl("name") == "MySource")
-- Remember initial parameter values.
initials <- sqrl("settings")
-- Set some parameter values.
sqrl(stringsAsFactors = FALSE,
wintitle = "[MySource]",
visible = TRUE)
;
-- Submit the query, record the result.
select * from database.table
<result -> out>;
<R>
-- Restore initial parameter values.
sqrl(initials)
-- Return the query result.
out
The function sqrl(...) is a special interface that is automatically defined into the temporary script-evaluation (working) environment. It acts as a cut-down sqrlAll, relaying its arguments only to whichever SQRL data source is executing the script. It works even when the name of the invoking interface function changes, or when that interface function does not exist. Consequently, sqrl should be adopted in preference to hard-coding interface names into scripts.
Here, the sqrl function is first used to provide an assert, which, in this case, verifies the script is being run only on the intended data source. A second call then takes a snapshot of the current RODBC/SQRL
parameter settings (the settings command is described in sqrlUsage). A third call then sets new values for three of those parameters, prior to submission of the SQL query.
Due to SQRL's set-and-forget approach, these values are persistent, and (unless they are changed again) will remain in effect after execution of the script has completed. That being the case, another call of sqrl is made after the query, in order to restore the original parameter values.
Note that (temporary) parameter values set via <with> ... </with> take precedence over (persistent) values set via sqrl(...), irrespective of the order in which those settings are made within the script. Also, whilst (for example) sqrl(max = 1) sets the persistent value for parameter max , sqrl("max") returns the value currently in effect (which might be a temporary value). Hence, for purposes of setting script-specific parameter values, using <with> is, when possible, generally preferable to calling sqrl.
As shown in script #17, the sqrl function can also be used to make nested queries (its arguments could just as easily be another script).
When a script is being run from some particular interface, explicit calls to that same interface function (as opposed to sqrl) are normally blocked. Calls to sqrlAll, sqrlInterface, sqrlOff, sqrlSource, and sqrlSources
are also normally blocked. If you really want a way around these blocks, calls can be made to SQRL::sqrlAll, instead of just sqrlAll, and so on. Alternatively, <R> rm("sqrlAll") <do> (et cetera) removes the block (this also works for unblocking the interface function).
-- Ensure readOnlyOptimise is TRUE.
<R>
if (!sqrl("readOnlyOptimize"))
{
sqrl("close")
sqrl(readOnlyOptimize = TRUE)
}
<do>
-- Pull data (reopening is automatic).
select * from database.table;
-- Close the connection.
<close>
Many communications parameters are locked while open , and cannot be changed while a connection (channel) exists to the source (see sqrlParams). In this example, we want a particular value for one such parameter, namely readOnlyOptimize . If its value needs changing, we must first ensure the channel is closed. Within the section, this is achieved with sqrl("close") (see sqrlUsage).
When the SQL query is submitted, SQRL will automatically open a new connection, if need be.
After the data is pulled, the <close> tag closes the channel (because, in this example, we do not want to leave it open). Unlike <R>sqrl("close")<do>, <close> tags return no value of their own, which means the final value of this SQRL script is still that of the query (as we require).
Be aware that <close> does not imply <do>, and it is an error to use <close> in the presence of partially-formed, or unsubmitted, SQL . Conversely, <R>sqrl("close")</R> is allowed within SQL .
An alternative to putting <close> at the end of a script is to set the autoclose parameter to TRUE (see sqrlParams). Placing <close> at the beginning of a script can be used to ensure no temporary tables are in existence (when no better mechanism is available).
When user-input is required for authentication on the opening of a new channel, connection closures should be kept to a bare minimum.
-- This selects 1 (the result of the embedded R expression).
select <R> return(1); 2 </R>
-- This return doesn't exit the script either (only the R section).
<R>
return(1)
print("this won't be printed")
<do>
-- Pull from a temporary table, and save the result.
select some_columns from temp_table
<result -> a>;
-- Drop the temp table (this returns a value).
drop temp_table;
-- Return the value of interest (exit the script).
<return (a)>
-- This is unreachable.
select 1
The difference between a <return (a)> and <R>return(a)<do>, is that the former exits the SQRL script, while the latter only exits from the (embedded) section (back into SQL ), before continuing with the script.
Almost any valid expression is allowed between the (mandatory) parentheses of a <return> tag (see script #03). Note that <return> tags are not recognised within sections. Also, <return> tags do not imply <do>, and it is an error to attempt a <return> within partially-formed (unsubmitted) SQL . Because x <- y returns invisibly, so too do <R> x <- y <do> and <return (x <- y)> (see example script #16).
The next example shows how <return> tags become much more useful when combined with conditional expressions.
-- Submit this when we have a 9-digit code.
<if (nchar(code) == 9)>
select category from item_category
where long_item_code = <R> code </R>
<result -> k> k <- as.integer(k) <do>
-- Submit this when we have a 6-digit code.
<else if (nchar(code) == 6)>
select category from item_category
where short_item_code = <R> code </R>
<result -> k> k <- as.integer(k) <do>
-- Exit here when we have neither
-- (because the next query would fail).
<else>
<return (NULL)>
</if>
-- Obtain all items in the same category,
-- in the original (long or short) format.
select
category,
<if (nchar(code) == 9)>
long_item_code
<else>
short_item_code
</if>
from item_category
where category = <R> k </R>
In this example, we have a table of (say) stock items, each of which has both a long (9 digit) and short (6 digit) identity code, and is assigned to some kind of category (with another integer identifier). The script takes a single integer argument, code , which might be in either the long or short format.
The <if> and <else if> tags are used to submit an appropriate query, according to the type of identity code supplied. In the event that the code is of an unrecognised type, the <else> tag is used, with <return>, to exit cleanly (without submitting any query). Provided that the code is of a recognised type, a second query is submitted, wherein <if> and <else> control which column is selected.
In the final query, the appropriate column could instead have been named from within <R> and </R> tags, but the earlier conditional submission and conditional return cannot easily be achieved in that way. Essentially arbitrary is allowed between the (mandatory) parentheses of an <if> or <else if> tag (see script #03), but the final result of evaluating that must be either TRUE or FALSE. None of these tags are recognised within sections. To enable their intra-statement application (as in the final query of the example script), none of these tags imply <do>. The space in else if is optional (i.e., elseif is equally valid).
-- If we already have a cached copy of the
-- data, return that.
<if (exists(.cached, .GlobalEnv))>
<return (get(.cached, .GlobalEnv))>
</if>
-- Otherwise, submit the query and cache the
-- result before returing it.
select * from table
<result -> x>
assign(.cached, x, .GlobalEnv)
x
In this example, the result of a large query is cached locally, with <if>
and <return> logic being used to return that cached result should the script be run again.
This is most useful when the data changes infrequently, the query takes a long time to run, and the script is stored as a procedure in the library (see script #18).
<R>
batch <- split(ID, ceiling(seq_along(ID) / 1000))
x <- NULL
i <- 1
<do>
-- Pull and accumulate results, a thousand at a time.
<while (i <= length(batch))>
select idnumber, name from identity_lookup
where idnumber in (<R> batch[[i]] </R>)
<result -> y>
i <- i + 1
x <- rbind(x, y)
<do>
</while>
In this example, we have a script with a single argument; ID, a vector of integer codes. Supposing that vector might be too long for the SQL in
operator, a <while> loop is used to pull the results in batches.
Essentially arbitrary is allowed between the (mandatory) parentheses of a <while> tag (see script #03), but the final result of evaluating it must be either TRUE or FALSE. As with the <if> family, <while> and </while> tags are not recognised within sections, and do not imply <do>. Ordinarily, loops over SQL should be avoided, or used only as a last resort, but there are use-cases (see script #18), including for the insertion of rows.
Referring back to script #13, the final result of script #16 (that of x <- rbind(x, y)) is invisible. If visible output is required, <R>x or <return (x)> could be appended to the script.
The parser is simple, and does not verify or enforce correct nesting structure. Unintuitive output may appear when nesting violations occur.
<proc "random patients">
select patient_number
from patient_details
order by rand()
limit <R> N </R>
<result -> a>
a[, 1L]
</proc>
select days_in_hospital
from patient_history
where patient_number in
(<R> sqrl("random patients", N = 200) </R>)
It is possible to define reusable SQRL procedures within (between) <proc> and </proc> tags. Here, a (parameterised) procedure is employed as a nested-query alternative to the sequential feedback mechanism of script #09.
Each procedure must be named (in its <proc> tag), with quotes (either single or double) being mandatory about that name. The end of a procedure definition acts in the same way as the end of a script (as an implied <do>). Note that </proc> tags are only recognised within sections, when (as is the case in this example) the section is within a procedure definition (i.e., under a <proc> tag). Opening <proc> tags are never recognised within sections.
<proc "add na.strings">
-- Takes one argument, 'add' (a character vector),
-- and adds its strings to the na.strings parameter.
<R>
sqrl(na.strings = unique(c(sqrl("na.strings"), add)))
<do>
</proc>
<proc "drop if exists">
/* Takes one argument, 'tables', being a
character vector of tables to be dropped. */
-- Force as.is to be TRUE (for the query).
<with>
as.is = TRUE
</with>
-- Pull details of temporary tables.
help volatile table
<result -> v>
<do>
-- Exit here when the above query did not return
-- a data frame (when no volatile table exists).
<if (class(v) != class(data.frame()))>
<return (invisible())>
</if>
-- Extract the names of all volatile tables
-- in existence, retain only those (unique)
-- tables to be dropped that actually exist,
-- and initialise the iterator.
<R>
volatiles <- as.character(v[, "Table SQL Name"])
tables <- unique(tables[tables %in% volatiles])
i <- 0L
<do>
-- Drop each requested table (that exists).
<while (i <- i + 1L; i <= length(tables))>
drop table <R> tables[i] </R> <do>
</while>
-- Return invisible NULL.
<return (invisible())>
</proc>
The previous example, script #17, exhibited a procedure defined as a utility within a larger SQRL script. Such definitions are not persistent, with the procedures vanishing upon the conclusion of their parent script.
However, as shown in sqrlUsage, it is possible to construct a persistent library of procedures. As is the case above, SQRL scripts intended for libraries must consist only of procedure definitions; no other SQL or is allowed. This example script defines two procedures (both parameterised). The first, add na.strings , adds strings to the existing na.strings vector (refer to sqrlParams). The second, drop if exists , implements that capability for Teradata SQL (which doesn't).
Following sqrlUsage, let's say the above script is recorded in a file library.sqrl , and that we have a SQRL interface function called owl. The library is then established with owl(library = "library.sqrl"), and the procedures are called with (for instance) owl("add na.strings", add = c("N/A", "-")), and owl("drop if exists", tables = c("tableA", "tableB")).
Ultimately, procedures do not confer any capability beyond that of SQRL files. Procedure libraries merely allow the consolidation of multiple files into one. They also offer another slight advantage in that when you change working directory, they come along.
select 1
-- This ends the script.
<stop>
-- This is unreachable.
select 2
Lastly, <stop> tags act as an early end-of-script (with its implied <do>). They apply within sections, as well as SQL , and even on the inside of a FALSE conditional block (i.e., the <stop> still functions in <if (FALSE)><R><stop>). They are intended as a troubleshooting aid.
<R>:: Begins an section (leaves SQL ). Once begun, only </R>, <do>, ;, <stop>, EOS, and </proc> are recognised (and can end the section). The </proc> case is only recognised when the initiating <R> tag is within a procedure definition (beneath a <proc> tag).
</R>:: Ends an section, causing that section to be evaluated. The result of that evaluation is pasted back into the surrounding SQL .
<do>:: If inside an section, ends and evaluates that section (without pasting the result back into SQL ). If outside of an section, causes the preceding SQL (which may be blank) to be submitted.
<result -> name>:: Submits the preceding SQL , and assigns the result of that submission to the variable name. Any syntactically valid name, or NULL , is allowed (in place of name). An section begins immediately after the tag. That section concludes with any of the tags listed (above) for <R>, besides </R>.
<close>:: Closes the ODBC channel (connection) to the data source.
<return (Rexp)>:: Evaluates Rexp, which can be any expression, and returns the resulting value (exits the script). The parentheses are mandatory.
<with>:: Begins a special section, for assigning temporary values to RODBC/SQRL parameters. Once begun, only </with>, <stop>, EOS, and </proc>
are recognised (and can end the section). The `</proc>` case is only recognised when the initiating `<with>` tag lies within a procedure definition (beneath a `<proc>` tag). Only the aCollapse , as.is , buffsize , dec , errors , lCollapse , max , na.strings , nullstring , ping , pwd , retry , scdo , stringsAsFactors , and verbose
parameters permit temporary value assignments.
</with>:: Ends an section begun by <with>. Causes the section to be evaluated, and assigns temporary parameter values accordingly.
<if (Rexp)>:: Evaluates Rexp, which can be any expression. If that expression evaluated to TRUE, the (arbitrary) script beneath the tag is acted upon. If that expression evaluated to FALSE, the (arbitrary) script beneath the tag is ignored (except for any <stop> tags). The parentheses are mandatory.
<else if (Rexp)>:: Acts as <if (Rexp)>, when the (mandatory) previous <if ((Rexp)>, and all the (multiple, optional) previous <else if (Rexp)> tags, evaluated to FALSE. Otherwise (when any of those evaluated to TRUE), acts as <if (FALSE)>. The parentheses are mandatory.
<else>:: Acts as <else if (TRUE)>.
</if>:: Marks the end of an <if (Rexp)> - <else if (Rexp)> - <else>
flow-control structure.
<while (Rexp)>:: Acts in the manner of <if (Rexp)>. The parentheses are mandatory.
</while>:: Marks the end of a <while (Rexp)> flow-control loop. If the previous <while (Rexp)> evaluated to TRUE, the parser returns to that <while (Rexp)> tag, and re-evaluates the expression. Otherwise (if the previous <while (Rexp)> evaluated to FALSE), no action is performed (the parser continues from this point).
<proc "name">:: Marks the beginning of the definition of a procedure called name . Any character string can be used in place of name . The quotation marks are mandatory (but can be singles or doubles).
</proc>:: Marks the end of a procedure definition (acts as an EOS for that procedure).
<stop>:: Acts as EOS . Applies even from within a FALSE conditional block. Intended for debugging, only.
;:: When the scdo parameter is at its default value of TRUE (see sqrlParams), the SQRL parser attempts to identify SQL statement terminating semicolons, procedural language block terminating semicolons, and any extra semicolons within sections. When detected, these all act as <do>.
EOS :: The end of the script (EOS ), acts as <do>. As necessary, it also acts as </if> and </proc> (it does not act as </while>).
The verbose parameter toggles extended output when running scripts (see sqrlParams). This includes the display of intermediate values.
sqrlUsage
Useful links