---

4glworks reference: Extended SQL interepreter

---

Additional statements. Besides supporting multiple SQL statements (separated by semicolons), the following is supported

foreach <select statement>;
    <statement list>|break|continue
done;

for <variable> in <expression>[, <expression>...];
    <statement list>|break|continue
done;

while <expression>;
    <statement list>|break|continue
done;

if <expression>;
    <statement list>
[elif <expression>;
    <statement list>
...]
[else
    <statement list>]
fi;

unload to <expression> [delimiter <expression>] <select statement>;

load <expression> [delimiter <expression>]< insert statement>;

let <variable>[<substring range>]=<expression>;

display <expression>[, <expression>...];

append to <expression>|default;

output to <expression>;

pipe to <expression>;

exit [<expression>];

whenever error continue|stop;

Data Storage. Foreach and plain select statements can assign values to variables. Use an

  into variable[, variable...]

clause at the end of the statement to list the target variables.

Placeholders. As an alternative to the expansion facility (see below), it is possible to use placeholders as you would on an open or execute statement. Use an

  using variable[, variable...]

clause at the end of the statement to list the variables 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 values on the fly.

Formatting. Queries and displays can be formatted. add the following clause at the end of the select statement:

  format brief|{full|vertical [<expression>] [headers <expression list>]}

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), and lastly full formats rows as you would expect.

The first expression is a format specifier in which c and 4gl format specifiers can be used. C formats are specified the usual way (see sprintf (3)), while 4gl formats have to be enclosed in square brackets. format specifiers can be interspersed with plain text, hard tabs & 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. Note that 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).

An expansion facility caters for pick lists or query or application output substitution anywhere in the script. Expansions are performed whenever a <+ +> pair is encountered, can be freely nested (up to a depth of 10 at present), and can be any of the following:

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

A few gotchas:

• default behavior is coded single (only because I use it a lot :-)
• quotes and separator are illegal with single, and imply multi (so, multi usage is redundant, with them)
• pick list expansions will fail if the pick list is empty, while get and silent expansions can happily expand to nothing. this is useful to silently execute external applications

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.