Oninit® SQLMorph — Records and Arrays
Records
A record is a set of variables, one per leaf field; nested records flatten. These forms work:
| Form | Meaning |
|---|---|
INSERT ... VALUES (r.*) | One bound value per field, in order. |
SELECT ... INTO r.*, FETCH ... INTO r.*, FOREACH c INTO r.* | Columns are read back into the fields in order. |
r.a THRU r.b | The fields from a to b, in embedded SQL, LET, call arguments and FOREACH ... INTO. |
LET q.* = r.*, LET q = r | Field-by-field copy. The records need the same number of leaf fields; each value converts to the target field’s type. |
CALL f(r.*) | The fields are passed as separate arguments. |
a[i].* | A whole element of a record array works in every form above: LET a[i].* = r.*, LET r.* = a[i].*, LET a[i].* = a[j].*, FOREACH c INTO a[n].*, VALUES (a[i].*) and as call arguments. |
Arrays
SPL has no array variable. A routine cannot say sales[i].amt, so a 4GL function that
fills a table in memory and indexes into it has no direct SPL translation. SQLMorph converts it as it stands.
DEFINE sales ARRAY[500] OF RECORD
cust INTEGER,
amt DECIMAL(12,2)
END RECORD
DEFINE grid ARRAY[12,4] OF DECIMAL(12,2)
LET sales[n].amt = tmp.amt
LET top3 = top3 + sales[i].amt
LET grid[i,1] = total / 12
| Property | Behaviour |
|---|---|
| Shape | One, two or three dimensions; elements are any scalar type or a record. A record may hold an array of its own (a[i].b[j]), and nested records are fine. |
| Where an element can appear | a[i], a[i,j] and a[i].field in expressions, on the left of LET, as INTO targets and as SQL host variables. |
| Subscripts | 1-based and checked. An index below 1, above the declared size or NULL raises an engine error naming the bound (for example “out of range 1..10”); the call fails cleanly and never touches a neighbouring variable. |
| Storage | On the heap, allocated when the function starts and released when it ends, including when it ends with an error. At most one million elements per array. |
| Layout | Row-major, as in 4GL. A record element keeps each field in its own array. |
| Initial values | Each element starts with the default of its type, like an ordinary variable. |
| Parameters and returns | An array cannot be passed or returned, as in 4GL. Pass element values, or let the function load its own array. |
Copying and clearing
As in 4GL there is no whole-array assignment; copy element by element. The record copy carries the element’s inner array with it.
DEFINE i SMALLINT
FOR i = 1 TO 100 -- the declared size
LET a[i].* = b[i].* -- .* for an ARRAY OF RECORD
END FOR
INITIALIZE a[3].* TO NULL -- one element, inner array included
INITIALIZE a TO NULL -- the whole array
Not converted yet
An array of records declared inside a record, copied as a whole (LET a[i].* = r.* where r holds such an array).
It is refused with a message naming the line; keep that inner table as a separate array. Inner arrays of scalars are not affected.