---

Structured Query Scripting Language Reference

---

• Statements
  • Connection related statements
  • Control statements
  • SQL statements
  • Simple statements
• Clauses
  • Identifiers
  • Variables
  • Functions
  • Expressions
  • Assignments
  • Pattern clause
  • Using clause
  • Connection clause
  • Aggregate clause
  • SQL redirection clause
  • Storage clause
  • Format clause
• Expansion facility

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 ];
DONE;

CLONE <expression> INTO <variable>, <variable>;
    [ <statement list> | BREAK | CONTINUE ];
[ PARENT;
    [ <statement list> | BREAK | CONTINUE ]; ]
DONE;

WAIT FOR <expression list> INTO <variable>, <variable>;
    [ <statement list> | BREAK | CONTINUE ];
DONE;

FOR <variable> IN <expression list>;
    [ <statement list> | BREAK | CONTINUE ];
DONE;

WHILE <expression>;
    [ <statement list> | BREAK | CONTINUE ];
DONE;

IF <expression>;
    <statement list>
[ ELIF <expression>;
    <statement list>
... ]
[ ELSE;
    <statement list> ]
FI;

BEGIN IMMEDIATE;
    <statement list>
END IMMEDIATE;

BEGIN COMPOUND;
    <statement list>
END COMPOUND [ <connection clause> ];

Few surprises on how the control statements work:

• The CLONE statement forks <expression> children, each executing the same code up to the PARENT clause (or the end of the loop, if none is specified). At each iteration, the current child number and its process id are stored in the two target variables respectively. The PARENT clause can be used to specify parent actions for each spawned child, like error checking.
  As you would expect, the number of children forked must cast to an integer and must be greater than zero. There is currently a limit of 128 children forked by any one statement.
• The WAIT FOR statement waits for each child whose process id is specified in the <expression list>. At each iteration the return code and process id are stored in the two target variables.
• COMPOUND and IMMEDIATE code blocks skip SQSL parsing altogether and just pass the enclosed statements to the engine, the former as a single block, the latter one at a time.
• WHILE loops reevaluate expansions that are part of the expression clause at every iteration
• FOR's expression list is determined at the beginning of the loop and does not change with subsequent iterations, as in the bourne shell or awk, but unlike perl's FOREACH
• Unlike the korn shell, awk or perl, it is perfectly fine to have hash elements as FOR, FOREACH, CLONE and WAIT FOR loops target variables. These are determined at each iteration, so be mindful of side effects that may be introduced by the aggregate clause or the code inside the loop blocks.
• loops can be terminated through the BREAK statement, or their reminder be skipped with the CONTINUE statement.

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>;

DISPOSE <variable>;

INVOKE <function>
   [ ( <expression list> ) ] 
   [ RETURNING <variable> [, <variable>... ] ];

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 ];

LET and DISPLAY are self explaining. 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 Informix-4gl'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 INFORMIX-4gl or bourne shell 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.

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.

Expressions

---

Expressions can be just about anything listed in the "Condition" and "Expression" segments of the Informix guide to sql: syntax, Volume 2, February 1998 (in case you are wondering, that's IDS 7.3) with the following variations:

• expressions do not handle columns, but variables instead :-)
• a number of operators have been borrowed here and there:
  • CLIPPED is a postfix operator that taken an string expression, returns one with trailing blanks removed
  • SPACES is a postfix operator that returns a string of spaces long as specified in the operand taken as an integer expression
  • PICTURE is an infix operator that takes any expression and a string expression and returns a string representing the first expression formatted according to the picture specified by the second expression
  • The CAST (::) operator allows to cast expressions to different types. Valid types are INTEGER (INT), FLOAT, DECIMAL (DEC), MONEY, DATE, DATETIME [<qualifier>], INTERVAL [<qualifier>], STRING, BYTE. Currently only NULL expressions can be cast to BYTE
• various functions have been added:
  • FORK creates a child process as in fork(2). the child does not
  • EXEC(command_line) executes a system command without ever returning. it maps to

        execl("/bin/sh", "-c", command_line, NULL);

    so it handles shell expansions, etc
  inherit database connections, and does an implicit exit (see later) at the end of the script
  • WAIT waits for any child processes as in wait(2). returns the pid of the child that has just terminated. return code and error number are returned by DBINFO("$?") and DBINFO("errno") respectively
  • WAITPID(pid) waits for child pid to terminate as in waitpid(2). Same return value and behaviour as WAIT
  • SPAWN(command_line) combines FORK and EXEC, and returns the result of the FORK
  • RUN(command_line) combines FORK, EXEC and WAITPID, and returns the return code of the child process
  • SLEEP(seconds) sleeps for the specified amount of seconds
  • GETENV(env_var)returns the value of an environmental variable
  • FOPEN(path, mode) opens a file in the specifies mode and returns a stream identifier
  • POPEN(cmd, mode) executes a pipe in the specified mode and returns a stream identifier
  • SCLOSE(stream) closes the stream passed as an argument and returns the exit code of the child process, if the stream is associated to a pipe.
  • ASCII(char) returns the character representation of the integer expression char, as a one character string
  • INDEX(source_string, substring) returns the position of substring within source_string
  • PAD(pattern, len) will build a len bytes string containing repeated pattern
  • RANDOM(mod [, seed]) returns a random number in the range 0..mod-1
• DATETIME and INTERVAL do not introduce constants, but are functions instead. This is consistent with date handling (there's no such thing as a date constant) and offers added versatility with a simplified grammar, as both functions perform conversion and qualifier extension in one invocation
• the FRACTION qualifier and the initial qualifier (intervals only) do not support a precision specifier
• interval qualifiers do not need (and in fact the grammar does not allow) to specify a precision specifier
• datetime and interval expression are automatically extended as the case needs be: 1 UNITS DAY - 1 UNITS HOUR returns INTERVAL(0 23) DAY TO HOUR rather than a -1266
• DATETIME, INTERVAL and DATE accept an optional second parameter specifying the input format a la TO_DATE
• REPLACE accepts optional start and length parameters having the same meaning as in SUBSTR
• DBINFO only knows how to handle "sqlca.sqlerrd1", "sqlca.sqlerrd2", "sqlca.sqlcode", "$?" and "errno"
• USER, TODAY and CURRENT do not necessarily return the same value as they would on the engine side
• inline SPL and subselect invocation is not supported
• GLS support is incomplete at best
• a number of functions has not been implemented as deemed redundant, or not easily implementable, or would needlessly complicate the grammar (or all of the above): DBSERVERNAME, SITENAME, OCTET_LENGTH, EXTEND, TO_CHAR, TO_DATE, TRIM, SUBSTRING

Expression lists

---

[ <expression> |
  <expression> TO <expression> |
  <variable>.* ] [, ... ]

Expression lists can include expanded hashes (denoted by the hash name followed by .*), and integer range expressions.

Assignments

---

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

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

Pattern clause

---

PATTERN [ <expression> |
        DELIMITED [<expression>] [ BY <expression> [ ESCAPE <expression> ] ] |
        BINARY ]

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> ]

AGGREGATE [ [ (<boolean expression>) ] <assignment clause> |
            (<boolean expression>) [ BREAK | CONTINUE ] ] [,...]

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

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

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