SQL$MOD (VX/SQLMOD) - Rdb SQL module language, on OpenVMS x86 and Linux
Overview
SQL$MOD (VX/SQLMOD) compiles Oracle Rdb SQL module language on OpenVMS x86. Your .SQLMOD modules keep their procedures, their parameter lists and their calling sequence, so the COBOL, FORTRAN, Pascal or C programs that call them do not change. Each module becomes a C module built with VSI C and linked with the same runtime as SQL$PRE (VX/SQL), and the database underneath becomes PostgreSQL, Microsoft SQL Server, MySQL, MariaDB or SQLite, with Oracle reached through SQL Relay.
Oracle is not porting Rdb to OpenVMS x86. SQL module language is where many Rdb applications keep all of their SQL, so the module is the contract between the application and the database, and SQL$MOD keeps that contract: the same procedures, parameters and order. Sector7's SQLMOD toolset converted IKEA's 600 Rdb SQLMOD files to PostgreSQL, and 422 of those modules, with their converted output, form the regression corpus SQL$MOD is checked against. SQL$MOD runs on Linux as well as on OpenVMS x86.
<div class="s7-tech-embed" data-technical-mega-embed="1" lang="en">
<h2 id="model">Your modules, unchanged</h2><p>
In SQL module language the SQL lives in a separate module: each database operation is a named
<code>PROCEDURE</code> with a typed parameter list, and the host program calls it as an ordinary
external routine. SQL$MOD reads the module in Rdb's own SQL module language syntax:
</p>
<pre>MODULE ACCT_INQUIRY_MOD
DIALECT SQL92
LANGUAGE PASCAL
AUTHORIZATION BANK_USER
ALIAS RDB$DBHANDLE
PARAMETER COLONS
DECLARE ALIAS FOR FILENAME banking_accounts
PROCEDURE INSERT_ACCOUNT
(SQLCODE,
:P_ACCOUNT_ID CHAR(5),
:P_LAST_NAME CHAR(14),
:P_FIRST_NAME CHAR(10),
:P_BALANCE INTEGER);
INSERT INTO ACCOUNTS (ACCOUNT_ID, LAST_NAME, FIRST_NAME, BALANCE)
VALUES (:P_ACCOUNT_ID, :P_LAST_NAME, :P_FIRST_NAME, :P_BALANCE);</pre>
<ul>
<li>The module header is read as Rdb writes it: <code>DIALECT</code> (SQLV40, SQL89, SQL92, MIA), <code>LANGUAGE</code>, <code>AUTHORIZATION</code>, <code>ALIAS</code>, <code>PARAMETER COLONS</code> and <code>DECLARE ALIAS FOR FILENAME</code>.</li>
<li>The reserved status parameters keep their place in the parameter list: <code>SQLCODE</code>, <code>SQLSTATE</code>, <code>SQLCA</code>, <code>SQLDA</code> and <code>SQLDA2</code>.</li>
<li>The procedure names and the parameter order stay the same, so callers link to the new module with no change to their <code>CALL</code> statements.</li>
</ul>
<h2 id="x86">On OpenVMS x86</h2><p>
SQL$MOD runs natively on OpenVMS x86. Each <code>.SQLMOD</code> file becomes a C header and a C module
that runs on the SQL$PRE runtime, compiled in one step with VSI C: no Pro*C and no second
precompiler. The module links with the same runtime as your embedded-SQL programs, so an application
that mixes SQL module language and embedded SQL moves as one.
</p>
<h6>What a procedure becomes</h6>
<pre><span class="kw">int</span> INSERT_ACCOUNT(
<span class="kw">int</span> *_sqlcode,
<span class="kw">char</span> *_P_ACCOUNT_ID, <span class="kw">char</span> *_P_LAST_NAME,
<span class="kw">char</span> *_P_FIRST_NAME, <span class="kw">int</span> *_P_BALANCE)
{
<span class="cmt">/* INSERT INTO ACCOUNTS (...) VALUES ($1, $2, $3, $4) */</span>
...
}</pre>
<ul>
<li>The <code>SQLCODE</code> status becomes an output parameter, and each host variable a reference parameter, just as the calling programs already pass them.</li>
<li>Rdb <code>:name</code> placeholders become positional parameters, bound by type, size and indicator.</li>
<li>The generated C is readable and kept for audit, and the SQL$MOD test suite builds each module in both output flavours and checks the values it returns against a live database, so the two builds cross-check each other.</li>
</ul>
<h2 id="sql">What the procedures can contain</h2><div class="table-wrapper"><table><thead><tr><th>Rdb SQL module language</th><th>Handled</th></tr></thead><tbody>
<tr><td>Single-statement procedures: <code>SELECT ... INTO</code>, <code>INSERT</code>, <code>UPDATE</code>, <code>DELETE</code></td><td><span class="ac-live">yes</span></td></tr>
<tr><td>Multi-statement procedures and compound <code>BEGIN [ATOMIC] ... END</code> blocks, <code>FOR ... DO</code> loops</td><td><span class="ac-live">yes</span></td></tr>
<tr><td>Static cursors: <code>DECLARE</code>, <code>OPEN [USING]</code>, <code>FETCH ... INTO</code>, <code>CLOSE</code>, <code>WHERE CURRENT OF</code></td><td><span class="ac-live">yes</span></td></tr>
<tr><td><code>SET TRANSACTION</code>, <code>COMMIT</code> and <code>ROLLBACK</code></td><td><span class="ac-live">yes</span></td></tr>
<tr><td>Dynamic SQL: <code>PREPARE</code>, <code>DESCRIBE</code>, <code>OPEN</code> / <code>FETCH ... USING DESCRIPTOR</code></td><td><span class="ac-live">yes</span></td></tr>
<tr><td>Indicator variables, including <code>INDICATOR ARRAY OF n</code></td><td><span class="ac-live">yes</span></td></tr>
<tr><td><code>RECORD ... END RECORD</code> parameters, from the dictionary or inline</td><td><span class="ac-live">yes</span></td></tr>
</tbody></table></div>
<h2 id="cdd">Dictionary records and OpenVMS data types</h2><p>
Record parameters can come straight from the dictionary. A <code>RECORD</code> parameter becomes a C
structure with the canonical record layout, bound field by field, so <code>SELECT ... INTO :record</code>
fills every column. Domain-typed parameters resolve through <a href="/technical/vx-cdd-vms-cdd">VX/CDD</a>
to real C types.
</p><div class="callout"><strong>Dates can stay quadwords.</strong> A module passes the OpenVMS quadword as a
<code>BIGINT</code> through Sector7's <code>S7_CONVERT_INT64_TO_DATE</code> and <code>S7_CONVERT_DATE_TO_INT64</code>
functions, and it round-trips through a SQL <code>TIMESTAMP</code> column. The database stores
normal timestamps that every other tool can read.
</div>
<h2 id="errors">Rdb status handling preserved</h2><p>
SQL$MOD maps database errors to the Rdb <code>SQLCODE</code> values, so a caller that tests the
<code>SQLCODE</code> status parameter or the procedure's return value behaves as it did under Rdb:
<code>100</code> for no data, <code>-803</code> for a duplicate key, <code>-913</code> for a deadlock.
</p>
<h2 id="proof">Proven at scale</h2><p>
IKEA's store systems kept their SQL in Rdb SQL module language. Sector7 converted
IKEA's <a href="/case-studies/ikea">600 SQLMOD files</a> to PostgreSQL, and 422 of those
modules, with their converted output, are now the regression corpus SQL$MOD is checked against.
</p>
<h2 id="performance">Performance</h2><p>
SQL$MOD modules run on the same runtime as SQL$PRE, including native server-side cursors on PostgreSQL.
In the embedded-SQL benchmark of that runtime on OpenVMS x86, the median complete 57,344-query cycle
took 9.9 seconds against PostgreSQL, where
native Oracle Rdb on Itanium took 373 seconds. The workloads and the charts are on the
<a href="/technical/vx-sql-cobol-with-embedded-sql-to-pro-c-or-ecpg#performance">SQL$PRE page</a>.
</p>
<h2 id="linux">The same tool on Linux</h2><p>
SQL$MOD also runs natively on Linux, where the module can be emitted either as direct C or as embedded
SQL for the PostgreSQL ECPG toolchain, from the same <code>.SQLMOD</code> source. The calling programs
see the same procedures either way.
</p>
<h2 id="related">Related tools</h2><ul>
<li><a href="/technical/vx-sql-cobol-with-embedded-sql-to-pro-c-or-ecpg">SQL$PRE (VX/SQL)</a>: Rdb embedded SQL in COBOL, on OpenVMS x86 and Linux.</li>
<li><a href="/technical/vx-sql-fortran-with-embedded-sql-to-pro-c">SQL$PRE (VX/SQL) for FORTRAN</a>: the same precompiler for VSI Fortran.</li>
<li><a href="/technical/vx-rdml-convert-rdml-to-embedded-sql">RDB$PRE (VX/RDML)</a>: RDML programs to embedded SQL, or straight to the runtime.</li>
<li><a href="/technical/vx-cdd-vms-cdd">VX/CDD</a>: the dictionary behind record and domain parameters.</li>
</ul>
</div>
