Oninit® SQLMorph — ANSI SQL/PSM
SPL converts to vendor-neutral ANSI SQL/PSM (the procedural extension to
the ISO SQL standard). This is the portable starting-point export —
the same procedure body should compile across any engine that supports
SQL/PSM (DB2 LUW is the closest in-the-wild dialect; MariaDB, HSQLDB, and
several others accept SQL/PSM-shaped routines with minimal vendor
adjustments). Each input procedure produces a single <name>.sql
file.
What gets emitted
| File | Contents |
|---|
| <name>.sql | CREATE OR REPLACE PROCEDURE or FUNCTION in ANSI SQL/PSM. Install with the host engine's CLI (e.g. db2 -td@ -vf <name>.sql, mysql --delimiter=// <name>.sql, etc. — per-vendor adjustments may be required for terminator handling). |
| REPORT.md | Only emitted when constructs were flagged. Each entry carries file:line, the construct, and a one-line note explaining what to review. |
Type mapping
| SPL type | ANSI SQL type |
|---|
| SMALLINT | SMALLINT |
| INT / INTEGER / SERIAL | INTEGER |
| INT8 / BIGINT / SERIAL8 / BIGSERIAL | BIGINT |
| FLOAT / DOUBLE PRECISION | DOUBLE |
| SMALLFLOAT / REAL | REAL |
| DECIMAL / NUMERIC / MONEY | DECIMAL(p,s) |
| CHAR(n) / VARCHAR(n) | CHAR(n) / VARCHAR(n) |
| LVARCHAR | VARCHAR(32672) or CLOB |
| DATE | DATE |
| DATETIME | TIMESTAMP |
| INTERVAL | VARCHAR(64) — ANSI INTERVAL is poorly supported across engines; customer parses |
| BOOLEAN | BOOLEAN |
| TEXT / CLOB | CLOB |
| BYTE / BLOB | BLOB |
Procedural mapping
| SPL construct | ANSI SQL/PSM |
|---|
| DEFINE name TYPE | DECLARE name TYPE; |
| LET name = expr | SET name = expr; |
| IF / ELIF / ELSE / END IF | IF / ELSEIF / ELSE / END IF |
| WHILE cond / END WHILE | WHILE cond DO … END WHILE |
| FOREACH (cur AS) SELECT … / END FOREACH | FOR cur AS SELECT … DO … END FOR |
| EXIT <cur> / CONTINUE <cur> | LEAVE <cur> / ITERATE <cur> |
| ON EXCEPTION … END EXCEPTION | DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN … END; |
| RAISE EXCEPTION code, msg | SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = … |
| RETURN expr | RETURN expr; |
| OUT parameters / multi-column RETURN | Native — trailing OUT cN T params on the procedure signature; body assigns SET _ret0 = …. |
Vendor portability
The emitted ANSI SQL/PSM should install with minor adjustments on:
- DB2 LUW — near-identical to the dedicated db2 target; the SQL PL emit has DB2-specific extensions the ANSI target omits, so the ANSI form trades a few DB2 conveniences for portability.
- MariaDB — SQL/PSM-conformant procedures install with the standard DELIMITER // … // wrapper.
- HSQLDB / H2 — both accept SQL/PSM-shaped routines.
- PostgreSQL, Oracle, MSSQL, MySQL — have dedicated SQLMorph targets that emit the engine's native procedural dialect; use those for production deployments rather than the ANSI export.
