Structured Query Scripting Language Reference

SQSL is essentially an augmented SQL with a number of feautures borrowed from various sources among which awk, sh, perl and x4GL. The actual interpreter is in fact implemented as an SQL preinterpreter: as such it will pass to the engine in use anything that it cannot handle directly and that it does not readily recognise as incorrect. This makes it somewhat hard to define the entire syntax of the language, as it is dependent on the engine the interpreter is connected to. We will therefore describe the features above and beyond SQL:

Connection related statements

CONNECT TO <expression>
  [ SOURCE <source name> ]
  [ AS <expression> ]
  [ USER <expression>
    USING <expression> ]
  [ <source specific options> ];
DISCONNECT <expression>;
SET CONNECTION [ DEFAULT [ SOURCE <source name> ] | <expression> ];

Sources are implemented as shared objects. Currently there are two sources for Informix engines, three for DB2 and one for SolidDB.

The interpreter sports the concept of 'current source', which is the source selected by the latest CONNECT statement explicitely naming a source. Further CONNECT statements not specifically selecting a source will use the current source.

Connections are named, which means that no two connections can share the same name, even when they are related to different sources. Connections can be renamed via the AS clause, which can be used to allow connections to two identically named identifiers residing on two different sources. Bear in mind that not all database engines support connection synonyms, which means that, even renaming connections, SQSL cannot help getting past the vendor's inability to connect multiple times from the same client to the same database identifier. Fork a child instead.

Certain database engines also sport the concept of a DEFAULT, unnamed, connection. This is a connection that gets automatically established upon receiving any sql statement. Default connections, if supported by the source, cannot be explicitely connected to or disconnected, but they can be set as current.

The initial current SQSL source is the IFXM one. The initial connection is the IFMX default connection, which is not physically established until a statement is passed to the engine.

Control statements

FOREACH [ [ [ [ <identifier> CURSOR [ WITH HOLD ] FOR | WITH HOLD ] ] <select statement> |
            <execute procedure> ] [ <using clause> ] [ <connection clause> ] |
          EXECUTE <expression> [ <using clause> ] |
          [ INPUT FROM <expression> |
            READ FROM <expression> |
            PIPE FROM <expression> ] <pattern clause> ]
    [ <aggregate clause> ] [ <storage clause> ] [ <format clause> ];
    [ <statement list> | BREAK | CONTINUE ];
END FOREACH;
CLONE <expression> INTO <variable>, <variable>;
    [ <statement list> | BREAK | CONTINUE ];
[ PARENT;
    [ <statement list> | BREAK | CONTINUE ]; ]
END CLONE;
WAIT FOR <expression list> INTO <variable>, <variable>;
    [ <statement list> | BREAK | CONTINUE ];
END WAIT;
FOR <variable> IN <expression list>;
    [ <statement list> | BREAK | CONTINUE ];
END FOR;
WHILE <expression>;
    [ <statement list> | BREAK | CONTINUE ];
END WHILE;
IF <expression>;
    <statement list>
[ ELSE IF <expression>;
    <statement list>
... ]
[ ELSE;
    <statement list> ]
END IF;
BEGIN IMMEDIATE;
    <statement list>
END IMMEDIATE;
BEGIN COMPOUND;
    <statement list>
END COMPOUND [ <connection clause> ];

Few surprises on how the control statements work:

SQL statements

[ <select statement> | <execute procedure> ]
   [ <using clause> ]
   [ <connection clause> ]
   [ <aggregate clause> ]
   [ [ <storage clause> ] [ <format clause> ] |
     [ <redirection clause> ] ];
[ <insert statement> | <delete statement> | <update statement> | <select into temp> ]
   [ <using clause> ]
   [ <connection clause> ];
<other SQL>
   [ <connection clause> ];

By and large you are expected to be familiar with the statements above, with the exception, that is, of the interesting stuff: all those little extra clauses. These are described below, each in its own little section.

Simple statements

LET <assignment clause>;
STORE <expression list> INTO <variable> [<type cast>]
   [, <variable> [<type cast>]... ];
DISPOSE <variable>;
INVOKE <function>
   [ ( <expression list> ) ] 
   [ RETURNING <variable> [<type cast>]
      [, <variable> [<type cast>]... ] ];
DISPLAY <expression list> [ <format clause> ];
APPEND TO [ <expression> | DEFAULT ];
[ OUTPUT | WRITE | PIPE ] TO <expression>;
OUTPUT FORMAT [ PLAIN | HTML ];
OUTPUT WIDTH <expression>;
[ INPUT | READ | PIPE ] FROM <expression>
   [ <pattern clause> ]
   [ <aggregate clause> ]
   [ [ <storage clause> ] [ <format clause> ] |
     [ <redirection clause> ] ];
PREPARE <variable> FROM <sql statement>
   [ <using clause> ]
   [ <connection clause> ]
   [ <aggregate clause> ]
   [ <storage clause> ]
   [ <format clause> ];
EXECUTE <expression>
   [ <using clause> ]
   [ <connection clause> ]
   [ <aggregate clause> ]
   [ [ <storage clause> ] [ <format clause> ] |
     [ <redirection clause> ] ];
FREE <expression>;
EXIT [ <expression> ];
WHENEVER ERROR [ CONTINUE | STOP ];
ERROR LOG TO <expression>;

LET and DISPLAY are self explatory. STORE performs multiple assignments. The only issue worth noting is that while the expansion facility on its own is already capable of fairly unpredictable side effects, couple it with the LET statements and things could become pretty nasty.

INVOKE is the equivalent of x4GL's CALL (CALL is used to execute procedures in many SQL implementations, hence the use of INVOKE)

DISPOSE physically obliterates items from the storage space. You can DISPOSE of anything: scalars, hashes, or even hash subsets.

APPEND, OUTPUT, WRITE and PIPE TO all instruct the interpreter to redirect its output. The expression passed to APPEND and OUTPUT is a name of a file (OUTPUT will truncate it, APPEND will add to it), while PIPE, as you would expect, pipes the output to a child process.
In case of failure, the output is redirected to the default stream, and the reason of the failure can be inspected via DBINFO("errno").
APPEND TO DEFAULT causes the new output generated by the interpreter to be added to the default stream, which in the case of the demo application is the viewer display, but can be defined to be a file or pipe.
WRITE writes to a stream previously obtained with FOPEN() or POPEN(). Upon switching to a different output, the stream is not closed.

Conversely, INPUT, READ and PIPE FROM read data from a file, a stream, or a pipe

Streams can be opened and closed via the FOPEN, POPEN and SCLOSE functions. Custom streams can even be created via skillfull combinations of the arbitrary stream interface and the external function API.

PREPARE, EXECUTE and FREE have not been inplemented in aid to dynamic sql (use the expansion facility instead), but rather to improve performance of sql repeatedly used.

Both the PREPARE and EXECUTE statements offer USING, AGGREGATE and INTO clauses, however with the former placeholders, aggregates and storage are determined once and for all at prepare time (plus: it's fast(er)), while the latter allows multiple executions of the same statement using different placeholders agregates and targets (plus: it's flexible).

Be aware, however, that in both cases expressions will be evaluated at execution time, so watch out for those pesky side effects.

EXIT behaves very much like x4GL's or bourne shell's exit however, note that it has different meanings depending whether the process is the result of a FORK (the process terminates) or not (the script terminates and the process resumes normal operation).

Identifiers

Identifiers start with an alphabetic character and continue with alphabetic characters, digits or underscores. Indentifiers are not case sensitive.
A larger character set is allowed and case sensitivenes is enforced when the identifier is enclosed in double quotes. Double quotes only have this special meaning when the environmetal variable DELIMIDENT is set, viceversa they just introduce string constants.

Variables

Variables are dynamic and typed, ie they spring into existence when first referenced and change type at every new assignment (either by means the simple statements or the storage clause) as the case needs be.

Variables come in two flavours: scalars and hashes. Hashes elements can too be scalar or hashes, thereby allowing jagged hashes.
Hashes and scalars share the same namespace, which means that you are not allowed to use a hash where a scalar is expected, nor can you try to make a hash a scalar by assigning to it a scalar value (DISPOSE is your friend).

Hashes are subscripted with an expression list enclosed in round parenthesys:

<variable>(<expression> [, <expression>...])

Expressions

Just like in any other language, expressions are used to aggregate data. They fall broadly in the following categories

Expression lists

[ <expression> |
  <expression> TO <expression> |
  <variable>.* ] |
  <variable>.(<expression list>) ] |
  SPLIT(<expression list>)] <pattern clause> ] [, ... ] |
  DISPLAYLABELS[.(<expression list>)]
  COLUMNS[.(<expression list>)]
]

Expression lists can include expanded hashes (denoted by the hash name followed by .* or .(<expression list>), when only a subset of a hash is required), and integer range expressions.

The DISPLAYLABELS and COLUMNS hash functions can be used whenever there is an active statements to return a list of column names, if supported by the current source.

Functions

SQSL offers a range of predefined functions. Identifiers representing function names are in fact reserved words.

An API to implement external functions is provided. External functions are referenced as <library name>:<function name>.
The API can be used to implement functions that open arbitrary data streams.

Parameters are passed to functions via an optional expression list; enclosed in round parenthesys.

The following classes of functions are on offer:

Datetime and Interval qualifiers

The second qualifier unit must not be higher than the first.

Formatting specifiers

These are used as part of the PICTURE expression clause and FORMAT clause to specify numeric, date and datetime or interval display formats, and, for dates, datetimes and intervals, input pictures as part of the PATTERN clause.

Assignments

[ <variable> [ <substring range> ] = <expression> |
  <variable> ::= <type cast> |
  <variable> [ + | - | * | / | || ] = <expression> ]

This is used as either part of the LET statement or the aggregate clause.

Pattern clause

PATTERN [ <expression> |
        DELIMITED [ BY <expression> [ ESCAPE <expression> ] ] |
        REGEXP DELIMITED BY <expression> |
	EXTRACTOR <external function call> ]

The PATTERN clause is used to specify the format of the data read by the READ, INPUT and PIPE FROM commands. It's use is similar to the FORMAT clause.

Using clause

USING <expression list>

As an alternative to the expansion facility, SQL statements can use placeholders as you would on an OPEN or EXECUTE statement. Use the USING clause to list the expressions that should substitute placeholders. This feature can be used to insert or update (should you ever get a working version of the IFX_UPDDESC feature) text or byte values on the fly.

Connection clause

CONNECTION [ DEFAULT [ SOURCE <source name> ] | <expression> ]

SQL statements are normally executed against the current connection. The CONNECTION clause allows to execute a single statement against a different connection, with the only obvious restriction that it needs to be already established.

Aggregate clause

AGGREGATE [ [ WHEN <boolean expression> THEN ] <assignment clause> |
     WHEN <boolean expression> THEN [ BREAK | CONTINUE ] ] [,...]

Part of the SELECT and INPUT classes of statements, this is quite a flaxible clause, in that it can be used to

Input field, both as part of expressions or in assignments, are denoted by COLUMN(<expression>), with expression denoting the field number.

SQL redirection clause

[ <insert statement> [ <connection clause> ] |
  <execute procedure statement> [ <connection clause> ] |
  EXECUTE <expression> ]

Besides storing data, or writing it in a formatted manner, data retrieval statements can redirect it to a table, or can execute a stored procedure for each row retrieved.

There are a few limitations on the statements that can be executed:

Storage clause

INTO <variable> [<type cast>] [, <variable> [<type cast>]...]

Retrieved data can be stored locally. Use the INTO clause to list the target variables.

Format clause

FORMAT [ BRIEF |
         [ FULL | VERTICAL ] [ <expression> ] [ HEADERS <expression> [, <expression>... ] ] |
         DELIMITED [<expression>] [ BY <expression> [ ESCAPE <expression> ] ] ]

Queries and display statements output can be formatted.

The BRIEF format only outputs column values separated by commas, with no formatting whatsoever, VERTICAL will output rows in a header/column fashion (formatting columns in the way), DELIMITED will produce a single line of delimited expressions (optionally formatted as specified) and lastly FULL formats rows in a tabular fashion.

The first expression is a format specifier in which both c format specifiers and pictorial specifiers can be used. C formats are specified the usual way (see sprintf (3)), while pictorial formats have to be enclosed in square brackets. Either way, format specifiers can be interspersed with plain text, hard tabs and new lines, or \n and \t. To ease header formatting (in case plain text appears in the format, in particular for vertical formats), columns can be separated with the pipe (|) sign. This is not required though. Special characters ([]%| and the quotes in use) can be escaped by doubling them. The backslash has special significance only when followed by either 't' or 'n'.
Note that there's no need to specify as many format specifiers as columns, while with full format, more than needed will combine multiple rows into a single format.

The header clause specifies a list of headers to be output as the first row of the query in full format, and preceding each column in vertical format. Headers will be padded to the length of the appropriate field (full format) or the largest header (vertical).

As for formats, there's no need that the number of headers matches that of the items being selected.

Syntax Variations

Expansion facility

The expansion facility caters for pick lists, query or application output substitution anywhere in the script. Expansions are performed whenever a <+ +> pair is encountered, can be freely nested and can be any of the following:

READ file [ file... ] displays the contents of multiple files
EXEC pipe executes an arbitrary application
GET <expression> [, <expression>... ] returns the concatenated results of the expression list
PROMPT <expression> opens a pop up window, prompts the evaluated <expression> to the user, and returns the user response
PASSWD <expression> behaves like PROMPT, except that the text input by the user is not displayed
a SELECT statement displays the results of a query. Queries are run against the current connection.

prefix the above with any of the following options to change expansion behavior:

[ CODED | LONE | SILENT | INCLUDE ] Specifies which expansion should be performed. CODED will open a code/description pick list (only the code is returned), LONE will open a description only pick list, SILENT will just do a plain expansion, while INCLUDE recursively includes the expansions found while processing the outer one.
[ SINGLE | MULTI ] Specifies whether the expansion should return one or more values. For pick lists, this controls whether multiple selections are allowed. For silent expansions, whether only the first or all values should be returned. It does not apply to includes.
PUT <variable> Stores the output of the expansion in the variable specified.
QUOTES <expression> Specifies quotes to surround each value returned by get or multiple expansions. Default is none. Enter two characters to specify differring start and end quotes.
SEPARATOR <expression> Specifies the entry separator between each value returned by get or multiple expansions. Default is blank.

A few gotchas:

Expansions can be commented using a <* *> pair. At par to <+ +> pairs, such comments can be nested. Expansions and comment tokens can be escaped with a backslash. Note that the backslash does not escape anything else, thus you don't need to escape it to use it in the context of a select statement or a shell script.
Finally, feel free to use whichever comment mechanism you like, as long as it makes sense within the context in which it is used, eg {} or --<CR> in SQL statements, or #<CR> in perl/awk/sh scripts.

Please address questions or comments to Marco Greco
(last updated Thu, 15 December 2016 17:16:30 GMT)