# 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`. ## Script #01 (Pure SQL) ``` use database; select * from table; ``` ### Commentary on Script #01 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. ## Script #02 (SQL, with R in it) ``` select isotope from periodic_table where atomic_number = Z ``` ### Commentary on Script #02 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. ## Script #03 (Comments, and Arbitrary R) ``` /* SQL-comment within SQL */ -- SQL-comment within SQL select /* SQL-comment within R */ -- SQL-comment within R # R comment within R letter <- sample(letters, 1) paste0("'", letter, "'") ``` ### Commentary on Script #03 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. ## Script #04 (R, out of SQL) ``` threshold <- 9000 sq <- function(x) paste0("'", x, "'") -- This tag terminates the R section. 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 = sq(date) and event_size > threshold ``` ### Commentary on Script #04 As in scripts #02 and #03, when an section ends with ``, 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 `` tag, or by a non-syntactical semicolon (that being one with nothing but whitespace between it and the previous semicolon, newline, or `` tag). Such sections are evaluated in the same way as those ending in `` 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 . ## Script #05 (Temporary Parameter Settings) ``` na.strings = c('NA', "-") -- comment x <- 5; max = x + 1 /* comment */ verbose = TRUE # comment as.is = "configfile.txt" # read from file -- Run this query with the above settings. select * from database.table ``` ### Commentary on Script #05 Tags `` and `` 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 `` 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 . ## Script #06 (Procedural Language Blocks) ``` 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 ``` ### Commentary on Script #06 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)`. ## Script #07 (Parser Control) ``` -- Change parser to fallback mode. scdo = FALSE -- An outside-of-SQL R section. N <- 2; -- This tag terminates the section. -- The default (scdo = TRUE) SQRL parser would -- not find the end of this procedural block. begin null; end /**/ ; -- This tag terminates the block. select 1 from dual; -- Semicolon ignored. -- This tag terminates the statement. select N from dual ``` ### Commentary on Script #07 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 `` 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 `` 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 `` tag, with nothing but whitespace between them, they are treated as a single `` (only one submission is made). An implied `do` tag exists at the end of every script. ## Script #08 (Manipulation of Results) ``` select calDate, Snowfall from Weather.SparseDailySnowfall where calYear = year -- Submit the above, assign the result to 'a', -- and immediately begin an R section. 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) ``` ### Commentary on Script #08 A `` tag acts as a combination of the `` and `` 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 `` 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 `` tags cannot be used to conclude sections. Also, sections beginning with `` tags cannot end in ``. When scdo is `TRUE`, and a terminating semicolon is followed by a `` tag, with nothing but whitespace between them, they are treated as a single `` tag (only one query submission is made). However, when a `` tag is followed by a `` tag, two submissions are made, irrespective of scdo . First, the query before the `` is submitted, and then the (blank) query between the `` and the ``. In `SQRL`, blank queries (blank SQL statements) are always allowed, and return no value. ## Script #09 (Feedback of Intermediate Results) ``` select distinct customer_number from our.customers where town in ( paste0("'", towns, "'") ) z>; -- Same thing as z> select sum(transactions) from online.orders where customer_number in ( z$customer_number ) ``` ### Commentary on Script #09 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 ` aCollapse = "\n"; lCollapse = ", " ` at the top of a script. See `sqrlUsage` and `sqrlParams` for further detail. ## Script #10 (Combining Data Sources) ``` n <- OtherSource("select distinct customer_number ", "from our.customers where town in " "(" paste0("'", towns, "'") ")") select sum(transactions) from online.orders where customer_number in ( n$customer_number ) ``` ### Commentary on Script #10 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. ## Script #11 (The sqrl Function) ``` -- 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 out>; -- Restore initial parameter values. sqrl(initials) -- Return the query result. out ``` ### Commentary on Script #11 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 ` ... ` 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 `` 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, ` rm("sqrlAll") ` (et cetera) removes the block (this also works for unblocking the interface function). ## Script #12 (Closing Connections) ``` -- Ensure readOnlyOptimise is TRUE. if (!sqrl("readOnlyOptimize")) { sqrl("close") sqrl(readOnlyOptimize = TRUE) } -- Pull data (reopening is automatic). select * from database.table; -- Close the connection. ``` ### Commentary on Script #12 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 `` tag closes the channel (because, in this example, we do not want to leave it open). Unlike `sqrl("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 `` does not imply ``, and it is an error to use `` in the presence of partially-formed, or unsubmitted, SQL . Conversely, `sqrl("close")` is allowed within SQL . An alternative to putting `` at the end of a script is to set the autoclose parameter to `TRUE` (see `sqrlParams`). Placing `` 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. ## Script #13 (Returns) ``` -- This selects 1 (the result of the embedded R expression). select return(1); 2 -- This return doesn't exit the script either (only the R section). return(1) print("this won't be printed") -- Pull from a temporary table, and save the result. select some_columns from temp_table a>; -- Drop the temp table (this returns a value). drop temp_table; -- Return the value of interest (exit the script). -- This is unreachable. select 1 ``` ### Commentary on Script #13 The difference between a `` and `return(a)`, 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 `` tag (see script #03). Note that `` tags are not recognised within sections. Also, `` tags do not imply ``, and it is an error to attempt a `` within partially-formed (unsubmitted) SQL . Because `x <- y` returns invisibly, so too do ` x <- y ` and `` (see example script #16). The next example shows how `` tags become much more useful when combined with conditional expressions. ## Script #14 (If, Else If, Else) ``` -- Submit this when we have a 9-digit code. select category from item_category where long_item_code = code k> k <- as.integer(k) -- Submit this when we have a 6-digit code. select category from item_category where short_item_code = code k> k <- as.integer(k) -- Exit here when we have neither -- (because the next query would fail). -- Obtain all items in the same category, -- in the original (long or short) format. select category, long_item_code short_item_code from item_category where category = k ``` ### Commentary on Script #14 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 `` and `` 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 `` tag is used, with ``, to exit cleanly (without submitting any query). Provided that the code is of a recognised type, a second query is submitted, wherein `` and `` control which column is selected. In the final query, the appropriate column could instead have been named from within `` and `` 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 `` or `` 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 ``. The space in `else if` is optional (i.e., `elseif` is equally valid). ## Script #15 (Caching) ``` -- If we already have a cached copy of the -- data, return that. -- Otherwise, submit the query and cache the -- result before returing it. select * from table x> assign(.cached, x, .GlobalEnv) x ``` ### Commentary on Script #15 In this example, the result of a large query is cached locally, with `` and `` 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). ## Script #16 (While Loops) ``` batch <- split(ID, ceiling(seq_along(ID) / 1000)) x <- NULL i <- 1 -- Pull and accumulate results, a thousand at a time. select idnumber, name from identity_lookup where idnumber in ( batch[[i]] ) y> i <- i + 1 x <- rbind(x, y) ``` ### Commentary on Script #16 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 `` loop is used to pull the results in batches. Essentially arbitrary is allowed between the (mandatory) parentheses of a `` tag (see script #03), but the final result of evaluating it must be either `TRUE` or `FALSE`. As with the `` family, `` and `` tags are not recognised within sections, and do not imply ``. 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, `x` or `` 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. ## Script #17 (Procedures) ``` select patient_number from patient_details order by rand() limit N a> a[, 1L] select days_in_hospital from patient_history where patient_number in ( sqrl("random patients", N = 200) ) ``` ### Commentary on Script #17 It is possible to define reusable SQRL procedures within (between) `` and `` 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 `` 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 ``). Note that `` 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 `` tag). Opening `` tags are never recognised within sections. ## Script #18 (Libraries) ``` -- Takes one argument, 'add' (a character vector), -- and adds its strings to the na.strings parameter. sqrl(na.strings = unique(c(sqrl("na.strings"), add))) /* Takes one argument, 'tables', being a character vector of tables to be dropped. */ -- Force as.is to be TRUE (for the query). as.is = TRUE -- Pull details of temporary tables. help volatile table v> -- Exit here when the above query did not return -- a data frame (when no volatile table exists). -- Extract the names of all volatile tables -- in existence, retain only those (unique) -- tables to be dropped that actually exist, -- and initialise the iterator. volatiles <- as.character(v[, "Table SQL Name"]) tables <- unique(tables[tables %in% volatiles]) i <- 0L -- Drop each requested table (that exists). drop table tables[i] -- Return invisible NULL. ``` ### Commentary on Script #18 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. ## Script #19 (Stop) ``` select 1 -- This ends the script. -- This is unreachable. select 2 ``` ### Commentary on Script #19 Lastly, `` tags act as an early end-of-script (with its implied ``). They apply within sections, as well as SQL , and even on the inside of a `FALSE` conditional block (i.e., the `` still functions in ``). They are intended as a troubleshooting aid. ## Summary of Tags - **``:**: Begins an section (leaves SQL ). Once begun, only ``, ``, `;`, ``, `EOS`, and `` are recognised (and can end the section). The `` case is only recognised when the initiating `` tag is within a procedure definition (beneath a `` tag). - **``:**: Ends an section, causing that section to be evaluated. The result of that evaluation is pasted back into the surrounding SQL . - **``:**: 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. - **` 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 ``, besides ``. - **``:**: Closes the ODBC channel (connection) to the data source. - **``:**: Evaluates `Rexp`, which can be any expression, and returns the resulting value (exits the script). The parentheses are mandatory. - **``:**: Begins a special section, for assigning temporary values to `RODBC`/`SQRL` parameters. Once begun, only ``, ``, `EOS`, and `` are recognised (and can end the section). The `` case is only recognised when the initiating `` tag lies within a procedure definition (beneath a `` 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. - **``:**: Ends an section begun by ``. Causes the section to be evaluated, and assigns temporary parameter values accordingly. - **``:**: 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 `` tags). The parentheses are mandatory. - **``:**: Acts as ``, when the (mandatory) previous ``, and all the (multiple, optional) previous `` tags, evaluated to `FALSE`. Otherwise (when any of those evaluated to `TRUE`), acts as ``. The parentheses are mandatory. - **``:**: Acts as ``. - **``:**: Marks the end of an `` - `` - `` flow-control structure. - **``:**: Acts in the manner of ``. The parentheses are mandatory. - **``:**: Marks the end of a `` flow-control loop. If the previous `` evaluated to `TRUE`, the parser returns to that `` tag, and re-evaluates the expression. Otherwise (if the previous `` evaluated to `FALSE`), no action is performed (the parser continues from this point). - **``:**: 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). - **``:**: Marks the end of a procedure definition (acts as an EOS for that procedure). - **``:**: 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 ``. - **EOS :**: The end of the script (EOS ), acts as ``. As necessary, it also acts as `` and `` (it does not act as ``). ## Note The verbose parameter toggles extended output when running scripts (see `sqrlParams`). This includes the display of intermediate values. ## See Also `sqrlUsage`

sqrlScript function