Running Native SQL¶

• Running Native SQL
  • Introduction
  • Alternatives to Consider First
  • Layers of the Persistence Stack
    • Level 1: A FWD Query Object
    • Level 2: FQL via the Persistence API
    • Level 3: Raw SQL
    • Summary
  • Quick Reference
  • Obtaining Database Access
    • Shared Connection vs. Dedicated Connection
    • Safe and Unsafe Persistence
  • Behaviour Lost Below Level 1
    • Validation
    • Record Locking
    • Uncommitted Changes: Intra-Session and Cross-Session
    • Committed Changes from Other Sessions
    • Database Triggers
    • Primary Key Allocation
    • Temp-Table Multiplexing and Hidden Columns
    • Caches and Buffer Coherence
  • Reading Results Safely
    • Live Cursor and Preselect
    • Commit Invalidates an Open Cursor
    • Bounding the Result Set
    • Full-Row and Projection Queries
    • From Result Set to Buffer
  • Authoring SQL Against the Generated Schema
    • ORDER BY and Index Selection
    • Character Index Components
    • Unknown Value and NULL
    • Built-in Functions (UDFs)
    • CAN-FIND and EXISTS
    • Prepared Statements and Statement Caching
  • Error Handling and Recovery
    • Working with Savepoints
  • Worked Examples
    • A Single Scalar Value
    • Records from a Projection
    • Locking a Set of Records
    • Restoring Cache Coherence after Direct DML
    • Allocating a Primary Key
    • Recovering with a Savepoint
    • Shared Helpers
  • Missing and Proposed FWD APIs
  • Related Pages

Introduction¶

FWD generates and executes the SQL for converted 4GL queries. That processing is not a single step but a stack of distinct layers, and the practical consequence (the subject of this page) is that bypassing the stack at a lower level forfeits every guarantee provided above that level, silently and without diagnostic.

FWD also permits a statement to be authored by hand and submitted directly to the database server. The usual motivation is a construct that SQL expresses efficiently and the 4GL does not: a multi-table join, a set-based aggregate, a bulk insert. This page documents the responsibilities that transfer to the application when that route is taken.

The essential point is that the choice is not binary. There are three entry points at three different depths, and the middle one is frequently the correct answer.

Alternatives to Consider First¶

Before descending the stack at all, establish whether a 4GL or schema-level change achieves the same result. Every alternative below remains a Level 1 construct, so it retains the full feature set described in the next section and continues to behave correctly across FWD upgrades.

The recurring motivations for reaching for SQL, and the construct that usually addresses each:

• One SQL statement per outer record, arising from nested blocks. Combine the blocks into a single multi-table query. The optimizer emits a server-side JOIN where the WHERE clause is join-shaped: a conjunction of equalities between indexed fields of the two tables. More complex predicates (a.x * a.y EQ b.z, or a.x EQ b.y OR a.x EQ b.z) are deliberately left un-joined. For a dynamic query, see FORCE-DB-JOIN Attribute.
• Whole records retrieved when only a few fields are read. Use the FIELDS or EXCEPT options rather than hand-writing a projection. Note that these produce a partial load only for NO-LOCK records; a locked record is always fetched in full.
• Filtering performed in the loop body rather than by the query. Move the condition into the WHERE clause so that the database server discards the rows instead of transferring them.
• Record-at-a-time navigation. FOR EACH is preferable to FIND NEXT / FIND PREV, and REPEAT PRESELECT is preferable again where the whole set is required and the loop will not exit early.
• The same inner set re-read on every outer iteration. Materialise it once into a temp-table. Note the trade-off: joins between a temp-table and a database table cannot be pushed to the database server, so this exchanges round trips for an in-JVM join.
• A predicate the query planner cannot exploit. This is frequently a schema matter rather than a query matter. Mandatory fields, narrower indices, and avoiding comparisons between two nullable indexed fields all yield simpler SQL and better plans.
• A predicate expressible in FQL but not in the 4GL. Stop at Level 2 rather than Level 3; see Layers of the Persistence Stack.

4GL Database Access Performance Tips covers these and others in detail, with the reasoning behind each.

Two examples. Combining nested blocks, which replaces one statement per outer record with a single join:

/* one query per guest */
FOR EACH guest NO-LOCK:
   FOR EACH reservation WHERE reservation.guest-id EQ guest.guest-id NO-LOCK:
   END.
END.

/* one joined query */
FOR EACH guest NO-LOCK,
    EACH reservation WHERE reservation.guest-id EQ guest.guest-id NO-LOCK:
END.

Narrowing the projection, which is the 4GL equivalent of selecting specific columns:

/* every column of guest is retrieved */
FOR EACH guest NO-LOCK:
   DISPLAY guest.last-name.
END.

/* two columns are retrieved */
FOR EACH guest FIELDS (guest-id last-name) NO-LOCK:
   DISPLAY guest.last-name.
END.

Where none of these applies (typically because the required construct has no 4GL expression at all), the sections that follow describe what must be reinstated by hand.

Layers of the Persistence Stack¶

Three levels of access are available, each forfeiting more of the runtime's behaviour than the last.

Level 1: A FWD Query Object¶

AdaptiveQuery, CompoundQuery, PreselectQuery, FindQuery and others are what conversion emits. This layer owns the entire 4GL feature set:

• record locking: honoring NO-LOCK, SHARE-LOCK and EXCLUSIVE-LOCK clauses
• legacy trigger dispatch: FIND, WRITE, CREATE, DELETE and other kind of triggers
• constraint validation: handling uniqueness or mandatory contraint violations
• dirty-share visibility: resolving queries against in-memory data not yet flushed to the DB
• index selection for the sort clause: inferring the index that shall be used to determine the expected ordering
• live-cursor versus preselect semantics: determine if one single big query or paginated queries should be emitted
• query invalidation: pick-up changes done by the same session or committed by other sessions in real-time
• commit-safe result caching: detect if current transaction is committed and cache already extracted results
• buffer association: hydrate records, cache them and load the buffers with them
• block scoping: auto-close the static query on block finish, on deletion of a persistent procedure, or in other out-of-scope scenarios

Level 2: FQL via the Persistence API¶

Persistence.scroll, list, load and others (and their UnsafePersistence counterparts) accept an FQL predicate directly. This layer performs the FQL-to-SQL transformation, hydrates results through the session cache, and owns record locking. Beyond those, it reinstates nothing from Level 1.

What the FQL layer does provide:

• character-field handling, upper and rtrim wrapping to mimic legacy case-insensitive right trimmed look-up
• unknown-value augmentation, so 4GL ? semantics survive into the SQL predicate (i.e. unknown equals unknown, whereas SQL NULL doesn't equal NULL)
• local optimisation and simplification of the predicate tree
• record hydration and integration with session cache
• record locking, through the load overloads, which acquire the lock before hydrating
• UDF execution error handling through error sensitive guards
• it is dialect independent and allows switching of underlying database vendor seamlessly

Level 2 is a potential landing point for hand-written work, because it retains hydration through the session cache (which is the invariant hardest to reinstate correctly by hand), while still allowing a hand-authored predicate. It is not recommended if attempting to use dialect-specific syntax that may not be available in FQL.

Level 3: Raw SQL¶

executeSQLQuery, executeSQL, getSingleSQLResult and executeSQLBatch leverage c3p0 statement caching, bind parameters and execute. Nothing else. Everything absent from Level 2 is also absent here, and in addition:

• No FQL-to-SQL transformation at all. Character rtrim wrapping, unknown-value augmentation, UDF overload resolution, etc. must all be written by hand, correctly, against the generated schema. The rules are set out in Authoring SQL Against the Generated Schema.
• No hydration and integration with any of the FWD caches
• Most importantly, none of the FWD query specific behavior is ensured.

Working at this level from 4GL is best done through a small hand-written Java adapter, which keeps the persistence types, the exception handling and the type conversions out of the business logic. See Worked Examples.

Summary¶

The remainder of this page concerns Levels 2 and 3. Wherever a rule applies only to raw SQL, it is marked as such; the guidance on locking, triggers, validation, dirty share and buffer coherence applies equally to both.

Quick Reference¶

Obtaining Database Access¶

Shared Connection vs. Dedicated Connection¶

Use the connection already held by the FWD runtime unless there is a specific reason not to.

PersistenceFactory returns the Persistence instance for a database and, through it, the connection the converted code is already using. A statement issued on that connection participates in whichever 4GL transaction is currently on the stack: it observes that transaction's uncommitted writes, it is committed or rolled back with it, and it consumes no additional pool capacity.

A dedicated connection is appropriate in two cases: where the operation must succeed or fail independently of the surrounding business logic, and where a result set must remain open across a commit. Both are covered in Using a Separate Database Connection. Note also the pool-sizing implication; connections are supplied from a c3p0 pool bounded by c3p0.maxPoolSize, and per-session additional connections make that ceiling considerably harder to determine.

Safe and Unsafe Persistence¶

Pass TRUE as the second argument to obtain an UnsafePersistence.

using com.goldencode.p2j.persist.PersistenceFactory   from java.
using com.goldencode.p2j.persist.UnsafePersistence    from java.

def var fwd-db as UnsafePersistence no-undo.

fwd-db = PersistenceFactory:getInstance("hotel", true).

Both variants execute identical SQL. They differ in the handling of a failed statement.

• getInstance("hotel") returns a standard Persistence. On failure the runtime logs the error, closes the database session (rolling back the transaction) and raises either a PersistenceException or, for errors the dialect classifies as unrecoverable, a StopConditionException. A StopConditionException reaching the client terminates the session. This is appropriate for FWD's own queries, which are known to be well-formed; it is unsuitable for a statement under development, where a syntax error terminates the session.
• getInstance("hotel", true) returns an UnsafePersistence. The exception propagates unmodified, the session remains open, and recovery is possible; for example by rolling back to a previously established savepoint.

Recovering by issuing a rollback on a shared connection is not safe. That connection belongs to the FWD runtime, so a rollback is not scoped to the hand-written statement: it discards the whole enclosing 4GL transaction, and the runtime's in-memory state is not adjusted to match. A buffer may then remain loaded with a record the rollback removed from the database, and nothing reports the divergence. Establish a native savepoint before the statement and roll back to that instead, refreshing any affected records afterwards (Caches and Buffer Coherence). A dedicated connection avoids the problem altogether.

Note that the destructive route is the more prominent one: rollback appears directly on UnsafePersistence, whereas the savepoint API lives on Session and so requires a Persistence handle to reach. Persistence implements UnsafePersistence, so a single instance covers both; see Working with Savepoints.

→ Details: Native SQL Error Handling

Behaviour Lost Below Level 1¶

Each item below is performed by a FWD query object for every converted query and is absent at both Level 2 and Level 3 unless explicitly reinstated. Descending only as far as FQL preserves hydration and locking, and little else; a point worth restating, because the FQL layer is close enough to the runtime to give a misleading impression of completeness.

Validation¶

Native INSERT and UPDATE statements receive no 4GL validation. Validate in advance, or operate through buffers.

When converted code assigns an indexed field, FWD evaluates mandatory-field and unique-index constraints and, on failure, raises an ERROR condition that the enclosing block undoes and that an ON ERROR or CATCH clause handles. The transaction survives and the message is the one the 4GL developer expects.

A native INSERT receives none of this. Against a persistent table a genuinely invalid row is at least still rejected, but as a raw SQL constraint violation: no ERROR condition, no block undo, no meaningful message, and (on a shared connection) an unusable transaction. A bulk statement compounds the problem, since it is all-or-nothing and a single invalid row fails the entire batch.

Temp-tables have no database-level constraints at all, so nothing rejects an invalid row. The generated DDL deliberately omits them: indices on a temp-table are created without the UNIQUE constraint even where the legacy index is unique, and not null is emitted only for the reserved columns recid and _multiplex, never for declared mandatory fields. Uniqueness and mandatory-field enforcement for temp-tables therefore live entirely in the FWD runtime, which applies them before issuing any DML.

The consequence for native DML against a temp-table is that a duplicate on a unique index, or a null in a mandatory field, is simply accepted and stored. There is no error, no rejection and no diagnostic; the invalid row is discovered later, by whatever converted code next reads it, and the condition raised at that point will be difficult to trace back to the statement that caused it. Validation of such rows must be performed before the statement runs.

A second consideration applies to inserts specifically. INITIAL values are applied by the FWD runtime at record creation and not by the database, so any column omitted from a native INSERT is stored as NULL rather than at its schema default. Every column must be listed explicitly.

→ Details: Modifying Data with Native SQL

Record Locking¶

This section concerns persistent tables only. A temp-table is private to its session, so no other session can reach its rows and there is nothing to contend for. 4GL locking has no meaning for temp-tables, the runtime applies none, and native DML against them requires no lock acquisition.

FWD locks are application-level locks reproducing 4GL semantics (NO-LOCK, SHARE-LOCK, EXCLUSIVE-LOCK, and the corresponding NO-WAIT variants). They are held in the FWD runtime, not in the database. A raw SQL statement has no knowledge of them, so a native SELECT behaves as NO-LOCK (it will read a record another session holds EXCLUSIVE) and a native UPDATE will modify it.

Locking is not, however, a Level 1 facility. It belongs to Persistence.load, which is Level 2, and the query objects call into it rather than implementing locking themselves. A converted query that needs EXCLUSIVE-LOCK typically resolves the primary key first and then calls load, which acquires the lock and hydrates the record. Locking is therefore fully available to hand-written code, which is worth knowing when combining native SQL with the 4GL lock model.

load performs its two steps in a specific order that matters:

1. The lock is acquired on the record identifier, through the lock manager.
2. Only then is session.get called to hydrate the row.

Locking before hydrating is what lets a session take ownership of a fully hydrated record: no other session can modify the row between the point the lock is taken and the point the data is read. The implementation even guards the reverse case, resetting the lock if the record turns out to have been deleted after its identifier was obtained but before its data was retrieved.

Three routes are available, all reachable through UnsafePersistence:

• load(implClass, id, lockType, timeout, updateLock) locks and loads a single record by primary key.
• load(buffer, fql, values, lockType, timeout, ...) evaluates an FQL predicate, takes the first match, locks it and loads it.
• A native SELECT projecting recid, followed by a load per key. The SQL chooses the rows; load supplies the locking and the hydration.

Prefer projecting keys and then locking, rather than selecting full rows and locking afterwards. A statement that reads complete rows and only then acquires locks leaves a window between the read and the lock in which another session may modify or delete the row, so the data in hand is already potentially stale by the time it is owned. Projecting recid and letting load lock before it hydrates closes that window, which is why the projection pattern is recommended throughout this page rather than merely for its memory characteristics.

Note also that a locked load is always a full load. Where a lock type other than NONE is requested, the partial-field set is discarded and every column is fetched, so FIELDS-style narrowing and locking cannot be combined.

For a set of records, acquire the locks in bulk first. invalidateCache accepts a lock query for exactly this purpose:

// the generated DMO interface for the table
Class<? extends DataModelObject> dmo =
   (Class<? extends DataModelObject>) Class.forName(dmoClassName);

persistence.invalidateCache(dmo,
                            "select recid from reservation where guest_id = ?",
                            new Object[] { guestId });

That call executes the supplied SELECT, acquires an EXCLUSIVE lock on every primary key it returns, and increments the corresponding record versions so that stale copies are detected. It is the only bulk lock-acquisition route, and it makes a subsequent fully hydrated query safe, because every record the query will return is already locked. The method name describes only the cache-invalidation half of the behaviour; see Missing and Proposed FWD APIs.

Native DML additionally exposes database-level lock contention that the FWD design normally keeps from surfacing. The database applies its own row-level locks regardless of the application-level ones, so a session writing a row another uncommitted transaction has already written will block until that transaction ends, and, where the conflict is on a unique index, will then fail with a constraint violation once it is released. Under FWD's own locking this is largely pre-empted, because application-level locks serialise access before the database ever sees a conflict; the database locks the runtime acquires are consequently constrained and comparatively relaxed.

Two properties of this blocking matter:

• It is not the 4GL lock-wait mechanism. No lock-wait timeout applies, no LOCKED condition is raised, and no NO-WAIT variant is available; the session simply waits for as long as the other transaction holds the row.
• Contention scales with the number of rows a statement touches, so set-based statements, INSERT INTO ... SELECT FROM ..., or a bulk UPDATE or DELETE; collide considerably more readily than single-row DML.

Native DML must therefore account for collisions in both directions: against rows the FWD runtime is writing, and against rows written by other native statements. Acquiring the FWD locks first, as above, addresses the former; the latter requires that concurrent native writers be serialised by some means of their own.

→ Details: Modifying Data with Native SQL, Chapter 28 Database Record Locking

Uncommitted Changes: Intra-Session and Cross-Session¶

Native SQL observes neither. The two cases have different causes, different configuration defaults, and only one of them has a workaround, so they are worth separating.

FWD terms the mechanism that provides both dirty share. It is implemented as an in-memory "dirty-share database" holding records that have been created or changed but not yet flushed or committed, which the runtime consults alongside the physical database when resolving a query. The two modes are configured independently and have opposite defaults, dirty-intra-share is enabled, dirty-cross-share is not. Database Configuration documents the flags, the per-table dirty-read hint and the known limitations in full.

Intra-session means the current session created or updated a record that has not yet been flushed. The row does not exist at the database in its new form at all, the runtime holding it in memory in a structure called the record nursery, so no SQL statement on any connection can see it. This is not an isolation matter; the data has simply not been sent. Records mid-creation are the most frequent instance, being visible on a fully updated index in the 4GL sense while some of their fields are still unset. Most applications depend on this mode, and it is enabled by default.

The nursery does have an operation to publish what it holds, but it should not be used to satisfy a native statement. It works one index at a time, it writes and therefore fires WRITE triggers, it can raise on the constraint checks that were the reason for deferring in the first place, and it is not reachable from application code. Releasing the buffers is the supported approach; see Querying with Native SQL for the detail.

This applies to temp-tables exactly as it does to persistent tables. Several buffers within one session can observe each other's unflushed changes, so a native statement against a temp-table is subject to the same gap.

The remedy is to have the record flushed before the statement runs, which is the responsibility of the buffer holding it; most usually by a RELEASE, or by the buffer going out of scope. Once flushed, the row exists at the database within the current transaction, so a native statement on the same connection will see it. Note that no commit is required: flushing and committing are separate steps, and only the flush is needed here.

The practical consequence is an ordering constraint on the program. A native statement must not depend on work still pending in a buffer earlier in the same session, so the RELEASE has to be placed deliberately rather than left to whenever the buffer's scope happens to close.

Cross-session means another session has created or changed a record and not yet committed it. This is the genuine isolation break: the 4GL exposes an updated index to other sessions immediately, so they observe records that are neither committed nor fully initialised. No SQL isolation level offers that behaviour (under READ COMMITTED another transaction's uncommitted rows are invisible) and there is no workaround.

This case does not arise for temp-tables at all. A temp-table is private to its session, so there is no other session whose uncommitted changes could be observed, and cross-session dirty share has nothing to apply to.

For persistent tables it also matters less than it appears. Cross-session dirty-share is disabled by default, FWD's support for it is partial, and it is slated to be dropped; Database Configuration states plainly that it should not be relied upon. A native statement that cannot observe cross-session dirty data is therefore consistent with the direction of the runtime rather than at odds with it.

In either case, the essential point is that a native SELECT reads the physical database only. Records held in the dirty-share database are not present there, so they are simply absent from the result set, not stale, not partially populated, absent.

Nor can they be reinstated after the fact. The tempting correction is to take the rows the statement did return and look up a dirty-share image for each, replacing the physical values where one exists. That cannot work: it can only fix rows that were returned, and the records at issue are precisely those the statement never returned. A record held in the dirty-share database may belong anywhere in the result ordering (including beyond a LIMIT boundary, or before the first row examined) so the outcome is a set that matches neither what the 4GL would have produced nor what the database contains. A hand-authored statement should target data known to be flushed.

→ Details: Querying with Native SQL

Committed Changes from Other Sessions¶

This section concerns persistent tables only. A temp-table is private to its session, so no other session can commit changes to it and neither effect below can arise.

Native queries are subject to non-repeatable reads and phantom reads. A converted FOR query is not.

A converted FOR EACH walks an index under the runtime's control and is re-driven as it advances, so changes other sessions commit are picked up while the loop is still running; this is the query invalidation listed among the Level 1 behaviours. The loop observes the current state of the index rather than a snapshot taken at the outset.

A native query has no equivalent mechanism. FWD does not set a transaction isolation level, so the database default applies (READ COMMITTED on PostgreSQL), and that level produces two distinct effects.

While a result set is being iterated, the rows reflect the snapshot taken when the statement executed. Another session may commit a change to a record already returned, or to one not yet reached, and the iteration will not reflect it. Note that this makes iteration internally consistent: a single result set is pinned to its execution snapshot, including where the driver streams it under a fetch size, so a JDBC cursor never observes other sessions' commits as it advances. The cost of that consistency is staleness; a record image in hand may already be out of date, and because no lock was acquired, nothing prevents it from changing again before it is acted upon.

If the statement is re-executed, the second execution takes a fresh snapshot. Records that did not match before may now match and appear, records that matched may have been modified, and records may have been deleted altogether. These are phantom reads and non-repeatable reads respectively, and they mean the two executions are not guaranteed to agree. A re-read must never be used as a correctness check.

Locking is the only mitigation. Acquiring FWD locks over the affected records before the statement runs, as described under Record Locking, prevents other sessions from modifying them for the duration. Absent that, a native query is a point-in-time read and should be treated as one.

What READ COMMITTED does still guarantee is that uncommitted data from another session is never visible; dirty reads do not occur at any PostgreSQL isolation level. Uncommitted changes are a separate matter from isolation and are covered under Uncommitted Changes.

→ Details: Querying with Native SQL, Transactions

Database Triggers¶

This section concerns persistent tables only. Legacy schema triggers are defined against database tables; temp-tables have none, so there is nothing for native DML against a temp-table to bypass.

Native DML does not dispatch legacy triggers. Invoke the equivalent logic explicitly.

FWD dispatches converted 4GL CREATE, WRITE, DELETE, FIND, etc. triggers around buffer operations. Native DML bypasses the buffer, so no trigger executes.

Several capabilities are lost, and all are easy to overlook because none is visible in the statement being written.

Triggers can veto the operation. CREATE, WRITE and DELETE triggers are not merely notifications; any of them may reject the operation outright, and the runtime honours that rejection by failing the operation and raising the corresponding condition. Native DML has no such gate: an insert, update or delete that a trigger would have refused is simply performed. Any business rule enforced in a trigger body must therefore be re-checked before the statement runs, because it will not be enforced during it.

Triggers can augment the record. A WRITE trigger commonly writes fields the caller never assigned; audit columns recording the modifying user and timestamp are the standard case, and derived or denormalised values are common too. Rows written natively carry whatever the statement supplied and nothing more, so audit columns are left null on insert, or stale on update, while the row itself looks entirely valid. Every column a trigger would have populated must be set explicitly by the statement.

ASSIGN triggers fire per field. These are attached to an individual field rather than to the record, and fire whenever that field is assigned. A native UPDATE touching such a field bypasses its trigger, so per-field validation or derivation attached that way is lost even where no record-level trigger exists on the table.

Replication triggers are bypassed too. REPLICATION-CREATE, REPLICATION-WRITE and REPLICATION-DELETE are dispatched separately from the ordinary trigger set and exist to feed downstream replication. Native DML fires none of them, so the rows it writes are invisible to whatever consumes that stream. Unlike the other cases this failure is silent and remote: the local database is correct while the replica quietly diverges.

Triggers also frequently carry business identity: a CREATE trigger assigning an application-level key independent of the surrogate recid is a common pattern, and rows bulk-inserted without it are unreachable by the application. Where native DML replaces a loop that dispatched triggers, the options are to reimplement the trigger body for the batch (auditing the operation once rather than per row is often both correct and substantially faster) or to relocate key assignment to a database sequence.

Note also that dispatch carries a fixed per-record cost of its own, independent of what the trigger body does; registry lookup, old-buffer creation and scope management all occur per row. In a record-by-record loop this can dominate, which makes trigger dispatch a frequent motivation for moving to a set-based statement in the first place. The distinction worth holding onto is that the overhead then disappears along with the trigger semantics: eliminating it is legitimate, but it must be a deliberate decision about what the triggers were for, not an unnoticed side effect of changing how the rows are written.

→ Details: Modifying Data with Native SQL, DatabaseTriggers

Primary Key Allocation¶

Draw surrogate keys from the p2j_id_generator_sequence sequence. Never compute them independently.

Every persistent record's recid comes from a single sequence, p2j_id_generator_sequence, created in the primary database by the import tool and used by the runtime for all new keys. A native INSERT must draw from the same sequence, on PostgreSQL:

select nextval('p2j_id_generator_sequence')

Two properties of the schema follow from this. The primary key column is not an auto-increment type, because the runtime needs the value in hand before the insert occurs; consequently recid must be listed explicitly in every native INSERT. And the sequence value must never be altered, no setval against it.

Drawing directly from the sequence is safe even while the runtime is allocating keys concurrently. The default identity manager reserves keys in blocks rather than one at a time, so any value the sequence issues has already been consumed from the runtime's point of view and cannot be handed out again. A sequence value that is obtained and then not used simply leaves a gap, which is harmless.

Persistence.nextPrimaryKey is an alternative that routes the request through the configured identity manager. The implementation is selected per database by the directory node database/<db>/p2j/identity_manager/class, defaulting to SequenceIdentityManager, with an optional identity pool enabled by database/<db>/p2j/identity_manager/pool.

Using the sequence directly remains safe when an identity manager is in use, including the default one, which draws from that same sequence. It is the wrong choice only where a custom identity manager has been configured whose scheme depends on keys not being taken from the sequence behind its back. Where that applies, nextPrimaryKey may be used instead.

Temp-tables work differently and require more care. There is no sequence. Keys are allocated through the temporary database's direct-access interface, and they are reclaimable (a deleted record's recid becomes available for reuse) with reclamation tracked per multiplex, so one multiplex never reuses another's keys even within the same physical table.

Because the key must exist before the row does, allocation and reservation are a single operation:

long pk = DirectAccessHelper.nextPrimaryKey(session, tableName, multiplex);

That returns the next available key and reserves its slot. The reservation is not self-cleaning. Where the row never reaches the table (the record is deleted or undone within the same transaction without an intervening flush) the slot has to be released explicitly, or it leaks:

DirectAccessHelper.clearPrimaryKey(session, (TempRecord) dmo);

Note also that the table must be defined MULTIPLEXED, since the routine depends on the supporting index that entails; see Temp-Table Multiplexing and Hidden Columns.

→ Details: Modifying Data with Native SQL, and Database Access, which documents the sequence, the mandatory recid column and external record creation in full.

Temp-Table Multiplexing and Hidden Columns¶

A temp-table row carries eight columns its schema never declared. Every native statement against a temp-table has to account for them.

Multiplexing. Temp-tables are private to a session, and FWD keeps them so; the physical table belongs to that session's temporary database and is never shared with another session. A different session using the very same schema gets a physical table of its own.

Within a single session, however, several temp-tables sharing the same schema are stored in one physical table. A hidden integer column, _multiplex, discriminates which rows belong to which of them, and every query the runtime issues is constrained to the relevant multiplex value. Three temp-tables of identical shape in one session are therefore three multiplex values in one table, not three tables.

For a native statement this means:

• _multiplex is mandatory and must be supplied on every INSERT.
• Every SELECT, UPDATE and DELETE must constrain _multiplex. Omitting it reads or, far worse, modifies rows belonging to other instances.
• _multiplex is the leading component of every generated index ((_multiplex, <components>, recid)) alongside an implicit index idx_mpid__<table> on (_multiplex, recid). The ORDER BY rule under ORDER BY and Index Selection therefore gains a leading component for temp-tables.
• Temp-table indices carry a trailing recid even when unique, which differs from the persistent-database rule described in that same section.
• Primary-key reclamation is tracked per multiplex, as noted above.

Hidden state columns. Six further columns exist on every temp-table row, supporting the 4GL before-table and ProDataset change-tracking features. They are absent from the schema definition but present in the table, and each corresponds to a hidden 4GL field:

A native INSERT that omits these leaves them null. That is usually tolerable for a plain temp-table, but not where the table participates in a ProDataset, whose change tracking reads them.

The more common hazard is positional. The generated column order places all eight reserved columns ahead of the declared fields:

recid, _multiplex, _errorFlag, _originRowid, _datasourceRowid, _errorString,
_peerRowid, _rowState, <declared fields...>

So select * against a temp-table does not begin with the declared fields, and any positional column access must skip eight leading columns. Naming the required columns explicitly avoids the problem entirely, and is preferable for the reasons given under Full-Row and Projection Queries.

→ Details: Modifying Data with Native SQL

Caches and Buffer Coherence¶

Following any native DML, invalidate the caches and then refresh the buffers:

Class<? extends DataModelObject> dmo =
   (Class<? extends DataModelObject>) Class.forName(dmoClassName);

persistence.invalidateCache(dmo);   // drop the cached records
persistence.refreshBuffers(dmo);    // tell this context to re-read

invalidateCache evicts the affected records from the ORM session cache and the fast-find cache and increments the record versions, so that any retained copy is recognised as stale. refreshBuffers then broadcasts a buffer-refresh notification, causing buffers in the current context to reload their records.

Both calls are required, and both are required for inserts as well as for updates and deletes: a newly inserted row can change which record is first on an index, which is precisely what a cached FIND FIRST result records.

Both calls are scoped to the current session, which is the correct scope: other sessions cannot observe uncommitted changes in the first place, and once the transaction commits they re-read through ordinary isolation rules.

→ Details: Modifying Data with Native SQL

Reading Results Safely¶

Live Cursor and Preselect¶

A native SQL query is a preselect, not a live cursor.

A converted FOR EACH walks an index. The result set is not determined in advance, and the loop is sensitive to concurrent change: records created, updated or deleted while it runs (by the current session, or by another session that has committed) affect the records it subsequently returns, essentially in real time.

A native query is fixed at execution. The set the database determined at that moment is the set that will be iterated; later changes are not visible until the statement is re-executed. The equivalent 4GL construct is DO PRESELECT with an inner FIND NEXT, not FOR EACH; a distinction that matters when comparing execution times, since a preselect and a FOR EACH do not perform the same work.

Two consequences follow: the data is a snapshot and may be stale by the time it is acted upon; and because no locking applies, another session may already have modified a row that is about to be written. Both are examined under Committed Changes from Other Sessions.

→ Details: Querying with Native SQL

Commit Invalidates an Open Cursor¶

Do not allow a transaction to end while a result set is still being read. Read the required rows, close the result set, then commit.

A JDBC cursor belongs to the transaction that opened it. When that transaction commits or rolls back the server-side cursor ceases to exist, and the next read fails; on PostgreSQL with portal "C_n" does not exist.

FWD avoids this by listening for the commit and draining the cursor into an in-memory row list before it occurs, so the query remains usable afterwards. Hand-written code cannot do the same, because the session-listener registration is not public API; see Missing and Proposed FWD APIs.

Consequently, a full transaction must not be nested inside a loop iterating a native result set. Where a result set genuinely has to outlive a commit, the alternatives are to collect the primary keys first and re-fetch in batches, or to read on a dedicated connection (Using a Separate Database Connection).

→ Details: Querying with Native SQL, Native SQL Error Handling

Bounding the Result Set¶

Apply a LIMIT to the statement, project only the required columns, or preferably both.

At Level 3 this is critical rather than merely advisable. Because executeSQLQuery applies no fetch size, the PostgreSQL JDBC driver has no server-side cursor from which to stream and will transfer the entire result set into the application server heap before the first next returns. A query that behaves acceptably in psql can therefore exhaust the JVM. At Level 2 the configured fetch size applies and the driver streams, so bounding the result set remains good practice but is no longer the difference between working and failing.

Note also that Persistence.list is documented as retaining the entire result set in the returned list, and warns that some drivers hold an intermediate copy as well, effectively doubling the requirement. scroll is preferable wherever the result set is not known to be small.

Note that LIMIT has no FOR EACH equivalent in the 4GL; the closest construct is MAX-ROWS, which applies only to scrolling queries. A native query carrying LIMIT ? is therefore not equivalent to converted code that leaves its loop on a counter; the converted form still traverses every row.

→ Details: Querying with Native SQL

Full-Row and Projection Queries¶

Prefer a projection (a key, plus at most the few fields actually read) over select *.

A projection is preferable on three counts. It transfers less data. It cannot become stale in the manner of a full row, since field values that were not requested are not retained. And a query projecting only a key yields a list of identifiers that can be passed back to FWD, which then produces correctly managed records: no hydration code, no buffer bookkeeping, and no risk of duplicate in-memory copies.

The appropriate key depends on where the result is consumed. From 4GL, project the business key so that an ordinary FIND can resolve it. From Java, project recid first where FWD is to construct the record from the row; see the following section.

FWD supports partially loaded records: a record marked incomplete retains which fields were read, and the runtime supplements it from the database if a missing field is subsequently required. This is the same mechanism underlying the FIELDS and EXCEPT options on 4GL queries, recommended in 4GL Database Access Performance Tips for the same reason.

→ Details: Hydrating Records from Native SQL

From Result Set to Buffer¶

Never construct a record instance directly. Route record creation through the session cache.

FWD maintains one invariant that is straightforward to violate and expensive to diagnose: within a context, a given database row is represented by exactly one record instance. Two buffers holding the same row hold the same instance. Violating this produces changes in one buffer that are invisible in another, records that no rollback will restore, and copies that never observe another session's commit.

The FWD hydration routine honours the invariant in four steps, and hand-written code must do likewise:

1. Read the primary key from the first column of the row. This is an assumption in the implementation rather than a convention, so any SQL intended for hydration must project recid first.
2. Look the key up in the session cache. On a hit that is not stale, return the cached instance and discard the row data. Where the cached record is incomplete, supplement only the fields actually missing.
3. Where the row consists of a single column, load the record by key through the session.
4. Only on a cache miss, instantiate a record, mark it incomplete if the projection is partial, populate it, and register it in the session cache.

Steps 2 and 4 carry the invariant, and skipping them is the single easiest mistake to make here. The tempting shape is to build a record per row and push it into a buffer:

// WRONG - bypasses the session cache entirely
while (results.next())
{
   Record rec = implClass.getDeclaredConstructor().newInstance();
   rowStructure.hydrate(session, resultSet, 1, rec);
   buffer.loadRecord(rec);
}

This compiles, runs, and appears to work. Every API it uses is public. What it does is create a second in-memory representation of a row the session may already hold, and hand that second copy to a buffer. The consequences appear later and elsewhere:

• An assignment through this buffer is invisible to any other buffer on the same row, and vice versa; the two hold different instances, so neither sees the other's changes.
• The record is not tracked for undo, so a rollback restores the database but leaves this instance holding the rolled-back values.
• It never goes stale. Another session committing a change to that row will not invalidate it, because the session does not know it exists.
• Nothing reports any of this. The failure is a wrong value read much later, in code that never touched the native statement.

Note also that the correct form of step 4 is not fully reachable. Marking a record incomplete (which is what allows the runtime to top up a partial projection on demand) is not public API, so a hand-written partial hydration produces a record that claims to be complete while missing fields. Those fields then read as unset rather than being fetched.

The practical conclusion is to not hydrate by hand at all. Let the statement decide which records and let the runtime produce them. At Level 2 this is automatic, because the FQL path supplies a row structure:

// RIGHT - Level 2: hydrated through the session cache, no record handling by hand
ScrollableResults<Object[]> rows = persistence.scroll(fqlPredicate, args);

while (rows.next())
{
   Record rec = (Record) rows.get()[0];   // already the session's instance for this row
}

Raw SQL can be made correct the same way, by projecting only recid and asking the runtime to resolve each key. quickLoad consults the session cache and loads from the database only on a miss, caching the result, so the instance it returns is always the instance for that row:

// RIGHT - Level 3: the SQL chooses the rows, the runtime produces the records
ScrollableResults<Object[]> rows = persistence.executeSQLQuery(
   "select r.recid from reservation r where ... order by ...", args);

String entity = tableMeta.getImplementationClass().getName();

while (rows.next())
{
   Long pk = (Long) rows.get(true)[0];
   Record rec = persistence.quickLoad(new RecordIdentifier<>(entity, pk), false);
}

Every call here is public and available through UnsafePersistence, so this is the supported way to combine a hand-authored SELECT with correctly managed records. Note get(true), which forces the primary-key-only interpretation of the row. The cost is one round trip per cache miss (the arbitrary SQL buys the row selection, not the row retrieval) which is why batched load-by-key appears in Missing and Proposed FWD APIs.

Raw values are also the only thing executeSQLQuery returns: one Object per projected column, directly from JDBC, with no records involved. This is the strongest argument for stopping at Level 2 wherever the predicate can be expressed in FQL; Persistence.scroll and list supply a row structure and therefore hydrate through the session cache, so the invariant is honoured without any hand-written code. No public entry point executes a raw SQL statement and returns managed records; see Missing and Proposed FWD APIs.

→ Details: Hydrating Records from Native SQL

Authoring SQL Against the Generated Schema¶

This section applies to Level 3. At Level 2 the FQL layer applies these transformations automatically. The exception is ORDER BY and index selection, which no layer below Level 1 supplies and which therefore applies to Level 2 as well.

The FWD-generated schema does not follow the conventions of a hand-designed schema. A statement that disregards those conventions will still return rows, but not necessarily the correct rows, and not necessarily by way of an index.

ORDER BY and Index Selection¶

Specify an ORDER BY deliberately, and prefer one that an existing index can satisfy.

Two separate concerns are at work here, and only the second is a hard rule.

Ordering expectations. Where a FOR EACH block walks an index, records arrive in that index's order, and a good deal of business logic is written around it; a loop that detects group boundaries by comparing against the previous record, for instance. SQL guarantees nothing in the absence of an ORDER BY, and PostgreSQL may return rows in whatever order the chosen plan produces, an order that shifts as statistics and data change. A native statement replacing such a loop should therefore reproduce the index order the loop relied upon.

A custom order is perfectly legitimate otherwise. Where the consuming logic does not depend on index order (an aggregate, a set-based update, a result the caller sorts or groups itself), any ORDER BY that suits the task is fine, as is none at all.

Index matching. Whatever ordering is chosen, matching it to an index is what determines performance. An ORDER BY the index can satisfy is delivered by the index scan itself; one it cannot forces the database to sort the result set, which costs time and memory and, on a large result, spills to disk. Matching requires attention to how FWD builds indices: for a non-unique legacy index the surrogate primary key is appended as a trailing pseudo-component, so the index is effectively (comp1, comp2, ..., recid). Unique indices receive no such suffix, being already unique. A sort clause intended to match a non-unique index therefore has to terminate the same way:

-- legacy index idx-r-checkin on reservation (checkin), non-unique
order by reservation.checkin asc, reservation.recid asc

Note that temp-table indices differ on both counts; _multiplex leads and recid trails even when unique; see Temp-Table Multiplexing and Hidden Columns.

Confirm the intended index scan with EXPLAIN rather than assuming it. Using EXPLAIN to Analyze SQL Performance documents the PREPARE / EXPLAIN EXECUTE sequence that reproduces how FWD executes the statement; a Sort node in the plan means the ordering was not satisfied by an index.

→ Details: Writing FWD-Compatible SQL, Chapter 27 Sorting Query Results

Character Index Components¶

Wrap character columns as the index does: rtrim(col) in all cases, and upper(rtrim(col)) only where the index is case-insensitive.

The 4GL treats trailing blanks as insignificant, so FWD indexes character columns on rtrim(col) rather than on the raw column. Case-insensitive indices wrap that expression in upper() as well. A predicate against the bare column does not match the index expression, and the planner falls back to a table scan:

-- misses the index
where guest.country = ?

-- matches a case-insensitive index on country
where upper(rtrim(guest.country)) = ?

-- matches a case-sensitive index on country
where rtrim(guest.country) = ?

Applying the wrong form is a genuine hazard: adding upper() where the index omits it misses the index just as completely as omitting it where the index requires it. No dialect applies the rtrim implicitly, so it is always present explicitly in the SQL. The same wrapping is required in ORDER BY, for the same reason.

→ Details: Writing FWD-Compatible SQL

Unknown Value and NULL¶

Test for the unknown value with is null, never with = ?.

In the 4GL, ? = ? evaluates to true. In SQL, null = null evaluates to unknown, so field = ? never matches a NULL regardless of the value bound. The translations are:

The or field is null term in the third row reproduces 4GL nulls-last ordering semantics for range comparisons. It also defeats index use on that column; 4GL Database Access Performance Tips covers the trade-off, and it constitutes a strong argument for declaring fields mandatory wherever the business permits.

Sort-order null handling requires no special treatment on PostgreSQL. The FWD convention is unknown-sorts-last on ascending order, which is PostgreSQL's native default; this is why FWD emits no explicit NULLS clause for this dialect, whereas H2 and MariaDB, which do not match, require additional handling. An explicit NULLS FIRST or NULLS LAST is therefore redundant at best and contradictory at worst.

→ Details: Writing FWD-Compatible SQL

Built-in Functions (UDFs)¶

Call the FWD UDFs where 4GL semantics are required within the statement, and verify the implementation language before relying on one in a frequently evaluated predicate.

Many 4GL built-ins are installed in the database as user-defined functions under a guarded_* naming convention, guarded_begins_tt, guarded_entryin_it, guarded_eq_tt and others. The definitions ship with FWD under udf/postgresql/, udf/mariadb/ and udf/sqlserver/, and are worth reading, since they define the exact semantics a predicate will receive.

The performance caveat from 4GL Database Access Performance Tips applies equally to hand-authored SQL: a UDF declared LANGUAGE sql can be inlined and optimised by the planner, whereas one declared LANGUAGE plpgsql is opaque to it and is evaluated per row.

→ Details: Writing FWD-Compatible SQL, Database User Defined Functions Overview, Native UDFs for PostgreSQL

CAN-FIND and EXISTS¶

Express CAN-FIND as an EXISTS subquery.

FOR EACH guest NO-LOCK
   WHERE CAN-FIND(FIRST reservation WHERE reservation.guest-id EQ guest.guest-id):

becomes:

select guest.recid
  from guest
 where exists (select 1
                 from reservation
                where reservation.guest_id = guest.guest_id)
 order by guest.recid asc

This is the transformation FWD applies when converting a nested CAN-FIND, and the same constraints hold: keep the nesting shallow, and note that a CAN-FIND carrying a lock cannot be expressed as a subquery at all, since the lock must be evaluated record by record.

→ Details: Writing FWD-Compatible SQL

Prepared Statements and Statement Caching¶

Use bind parameters in all cases, and close both the result set and the statement.

Use ? placeholders rather than assembling SQL by string concatenation. Beyond the injection argument, c3p0 caches prepared statements per pooled connection, governed by c3p0.maxStatementsPerConnection: a parameterised statement is prepared once and reused, whereas a concatenated statement is distinct on every execution and displaces useful entries from the cache.

Resource release is equally important. An unclosed result set retains server-side resources and keeps its statement out of the pool; in sufficient numbers this exhausts the pool. Close in a FINALLY block rather than on the success path only.

Temp-tables do not use c3p0 (they employ a separate, lighter statement cache), but the release requirement is identical.

→ Details: Querying with Native SQL

Error Handling and Recovery¶

Execute through UnsafePersistence, establish a savepoint beforehand, and roll back to it on failure.

A malformed statement does not fail in isolation; it can render the surrounding context unusable. Two mechanisms are involved:

• The transaction. On PostgreSQL a failed statement aborts the entire transaction, and every subsequent statement fails until a rollback occurs. A savepoint established beforehand permits a rollback limited to that point. On a shared connection a savepoint is not merely convenient but necessary, for the reasons given under Safe and Unsafe Persistence. Note that ending the transaction either way also destroys any open result set, as described under Commit Invalidates an Open Cursor.
• The FWD session. Through a standard Persistence, a failure closes the database session and raises PersistenceException, or StopConditionException for errors the dialect classifies as unrecoverable, which terminates the client session. Through UnsafePersistence the exception propagates unmodified and the session survives.

Neither mechanism constitutes 4GL error handling. FWD's own queries map database failures onto 4GL conditions (lock-wait timeout, unique-constraint violation, connection loss), so that ON ERROR, UNDO, RETRY, CATCH and ERROR-STATUS all behave as expected. Native SQL failures do not arrive as 4GL conditions, and a StopConditionException in particular honours none of those clauses. PersistenceException should be caught explicitly and the appropriate business-logic response determined there.

Working with Savepoints¶

The savepoint API lives on Session, which offers three operations:

• setSavepoint(); establishes a savepoint on the session's connection and returns the java.sql.Savepoint handle.
• releaseSavepoint(Savepoint); discards the savepoint, keeping the work done since it was taken. This is the success path.
• rollbackSavepoint(Savepoint); reverts the connection to the savepoint, discarding only the work done since. Returns true where the rollback was performed.

The savepoint must be taken on the same session the statement will run on. Take two handles: the plain one to reach getSession, and the unsafe one to run the statement. This is safe because getInstance(name, true) wraps the very instance getInstance(name) returns, so the two share a persistence context and therefore a session. The proxy adds nothing but the unsafe flag around each call it forwards.

Persistence       safeDb   = PersistenceFactory.getInstance("hotel");
UnsafePersistence unsafeDb = PersistenceFactory.getInstance("hotel", true);

Session   session   = safeDb.getSession();
Savepoint savepoint = session.setSavepoint();

try
{
   unsafeDb.executeSQL(sql, args);   // failure propagates, session survives

   session.releaseSavepoint(savepoint);
}
catch (PersistenceException exc)
{
   session.rollbackSavepoint(savepoint);
   // decide the business-logic response here
}

Running the statement through the unsafe handle is what makes the savepoint useful. Through the plain handle a failure closes the session before the catch runs, leaving nothing to roll back to. The plain handle is used only to obtain the session, because UnsafePersistence does not expose getSession.

Release on success rather than leaving the savepoint outstanding; savepoints held over a long transaction accumulate on the connection. Note also that recovering this way leaves the database consistent but says nothing about the runtime's in-memory state; where the statement modified rows the runtime may hold, follow the rollback with invalidateCache and refreshBuffers as described under Caches and Buffer Coherence.

→ Details: Native SQL Error Handling

Worked Examples¶

Put the interop in one Java class and keep the 4GL thin.

Reaching the entry points directly from 4GL is possible but unpleasant. Every typed column needs a Class literal obtained through Class:forName, a CAST to recover the type erased by a generic signature, and an unboxing assignment before an assertion or comparison will resolve. Every call needs a handler for the checked PersistenceException. None of that is business logic.

A small hand-written Java adapter removes all of it. The adapter owns the persistence types, the exception handling and the conversions; the 4GL consumes plain character, integer and logical values. Two further reasons to prefer this shape, both of which only appear once code is compiled:

• Conversion hands a 4GL character literal to a Java method as a String but a character variable as an FWD character, so a parameter typed for one form rejects the other. Give the adapter an overload per form rather than weakening the parameter to Object: statement text is typed String where the call site passes a literal and character where it passes a variable.
• A Java primitive returned into an assertion is not boxed, so Assert:Equals(0, someJavaInt) finds no matching overload. Returning FWD types from the adapter avoids the problem entirely.

The examples below show the 4GL a developer writes alongside the adapter method that backs it. Only the method is shown; the surrounding class is ordinary, and the handful of private helpers they share is listed under Shared Helpers.

A Single Scalar Value¶

def var db  as character no-undo initial "hotel".
def var cnt as integer   no-undo.

cnt = NativeSQLAdapter:scalarInt(db,
        "select count(*) from guest" 
      + " where upper(rtrim(country)) = ?",
        "USA").

public static integer scalarInt(character db, String sql, String arg1)
{
   try
   {
      Object value = persistence(db).getSingleSQLResult(sql, new Object[] { arg1 });

      // no row matched: the unknown value, not an error
      return (value == null)
         ? new integer()
         : new integer(((Number) value).intValue());
   }
   catch (PersistenceException exc)
   {
      throw failed(sql, exc);
   }
}

Records from a Projection¶

The statement chooses the rows; the runtime produces the records. The adapter returns the projected keys as a list, so the 4GL resolves each one with an ordinary FIND and never touches a record instance directly. That is what keeps the one-instance-per-row invariant intact; see From Result Set to Buffer.

def var keyList as character no-undo.
def var idx     as integer   no-undo.

keyList = NativeSQLAdapter:firstColumnList(db,
            "select r.reservation_id from reservation r" 
          + " where r.checkin >= ?" 
          + " order by r.checkin asc, r.recid asc", checkinFrom).

do idx = 1 to num-entries(keyList):
   find reservation
      where reservation.reservation-id eq integer(entry(idx, keyList))
      no-lock no-error.

   if available reservation then
      /* ... */
end.

public static character firstColumnList(character db, String sql, int arg1)
{
   ScrollableResults<Object[]> rows = null;
   List<String> values = new ArrayList<String>();

   try
   {
      rows = persistence(db).executeSQLQuery(sql, new Object[] { arg1 });

      while (rows.next())
      {
         Object[] row = rows.get();

         values.add(row[0] == null ? "?" : row[0].toString());
      }
   }
   catch (PersistenceException exc)
   {
      throw failed(sql, exc);
   }
   finally
   {
      close(rows);   // never leave the statement checked out of the pool
   }

   return new character(String.join(",", values));
}

Locking a Set of Records¶

The DMO argument is resolved with Class.forName, so it must be the fully qualified name of the generated interface rather than the legacy table name.

NativeSQLAdapter:lockAllMatching(db,
   "com.goldencode.hotel.dmo.hotel.Reservation",
   "select recid from reservation where guest_id = ?", guestId).

public static void lockAllMatching(character db, character dmoClassName,
                                   String lockSql, int arg1)
{
   Class<? extends DataModelObject> dmo = dmoClass(dmoClassName);

   // runs the SELECT and takes an EXCLUSIVE lock on every recid it returns
   persistence(db).invalidateCache(dmo, lockSql, new Object[] { arg1 });
}

Restoring Cache Coherence after Direct DML¶

NativeSQLAdapter:execute(db,
   "update reservation set state = ? where guest_id = 42", "CLOSED").

NativeSQLAdapter:invalidate(db,
   "com.goldencode.hotel.dmo.hotel.Reservation").

public static void invalidate(character db, character dmoClassName)
{
   Class<? extends DataModelObject> dmo = dmoClass(dmoClassName);

   UnsafePersistence persistence = persistence(db);

   persistence.invalidateCache(dmo);   // drop the cached records
   persistence.refreshBuffers(dmo);    // tell this context to re-read
}

Allocating a Primary Key¶

def var pk as int64 no-undo.

pk = NativeSQLAdapter:nextPrimaryKey(db).

public static int64 nextPrimaryKey(character db)
{
   String sql = "select nextval('p2j_id_generator_sequence')";

   try
   {
      Number value = (Number) persistence(db).getSingleSQLResult(sql, null);

      return new int64(value.longValue());
   }
   catch (PersistenceException exc)
   {
      throw failed(sql, exc);
   }
}

Recovering with a Savepoint¶

The savepoint API lives on Session, and UnsafePersistence declares no getSession. The proxy implements only that interface, so it cannot be cast back to Persistence either. The pattern therefore takes two handles over the same instance: the plain one purely to obtain the session, and the unsafe one to run the statement. This is a genuine gap rather than a stylistic preference; see Missing and Proposed FWD APIs.

NativeSQLAdapter:executeGuarded(db,
   "update reservation set state = ? where reservation_id = 7", "CLOSED").

public static integer executeGuarded(character db, String sql, String arg1)
{
   // two handles over the same instance: the plain one only to reach
   // getSession, which UnsafePersistence does not expose
   Persistence       safeDb   = PersistenceFactory.getInstance(text(db));
   UnsafePersistence unsafeDb = PersistenceFactory.getInstance(text(db), true);

   Session   session   = safeDb.getSession();
   Savepoint savepoint = session.setSavepoint();

   try
   {
      // through the unsafe handle the failure propagates and the session
      // survives, so there is still a savepoint to roll back to
      int rows = unsafeDb.executeSQL(sql, new Object[] { arg1 });

      session.releaseSavepoint(savepoint);

      return new integer(rows);
   }
   catch (PersistenceException exc)
   {
      // confined to this statement: the enclosing 4GL transaction survives
      session.rollbackSavepoint(savepoint);

      throw failed(sql, exc);
   }
}

Shared Helpers¶

The five helpers the methods above rely on. Bound values are passed as Object[] because a JDBC parameter is heterogeneous by nature, and because a 4GL unknown value arrives as null, which binds as SQL NULL.

private static UnsafePersistence persistence(character db)
{
   return PersistenceFactory.getInstance(text(db), true);
}

/** Text of a 4GL character value, or null when it is unknown. */
private static String text(character value)
{
   return (value == null || value.isUnknown()) ? null : value.getValue();
}

private static Class<? extends DataModelObject> dmoClass(character name)
{
   try
   {
      return (Class<? extends DataModelObject>) Class.forName(text(name));
   }
   catch (ClassNotFoundException exc)
   {
      throw new RuntimeException("no such DMO interface [" + text(name) + "]", exc);
   }
}

/**
 * Wrap a persistence failure so that it does not have to be handled at the 4GL
 * call site. Carrying the statement text makes a failing test self-explanatory.
 */
private static RuntimeException failed(String sql, PersistenceException exc)
{
   return new RuntimeException("native SQL failed [" + sql + "]", exc);
}

/**
 * Close a result set, ignoring a secondary failure so that it cannot mask the
 * original one. Called from a finally block, so it must tolerate null.
 */
private static void close(ScrollableResults<Object[]> rows)
{
   if (rows != null)
   {
      try
      {
         rows.close();
      }
      catch (Exception exc)
      {
         // deliberately ignored
      }
   }
}

Argument counts are expressed as explicit overloads rather than varargs, so that the call sites do not depend on varargs support in direct Java access. A statement needing an integer argument gets an int overload, one needing text gets a String overload, and the unknown value resolves to the String overload and binds as NULL.

Most of the examples above have executable counterparts in the testcases project under tests/persistence/sql, with the adapter in srcnew/java. The two exceptions are lockAllMatching and executeGuarded: asserting either one meaningfully needs a second concurrent session, which that slice does not set up, so they are illustrative rather than tested.

Missing and Proposed FWD APIs¶

The APIs underlying native SQL are incomplete in places. Everything described above is achievable, but several of the recommended patterns require either a non-public member, or a public one whose name does not describe the behaviour needed. The gaps are recorded here so that they can be discussed and addressed.

Two 4GL syntax extensions would also remove common reasons for descending the stack at all, and belong on this page as it sits under 4GL Enhancements:

• DO PRESELECT ... MAX-ROWS ...; a preselect with a row cap, semantically identical to a native SELECT ... LIMIT n, which would remove the principal reason for authoring one by hand.
• FOR EACH ... MAX-ROWS ...; the same cap on an index-walking block. Note that this would denote maximum iterations rather than maximum rows, since a FOR EACH neither preselects nor scrolls and may visit a given record more than once.

Related Pages¶

• 4GL Database Access Performance Tips; rewriting a nested FOR into a joinable query, mandatory fields, UDF cost, FIELDS and EXCEPT. Worth reading before concluding that native SQL is required.
• FORCE-DB-JOIN Attribute; forcing a join on an OPEN QUERY.
• Using EXPLAIN to Analyze SQL Performance; confirming that a statement obtains the intended index scan.
• Direct Java Access, the USING ... FROM JAVA syntax and boxing rules used throughout the examples above.
• Integrating Hand-Written Java; packaging, classpath and manifest considerations for hand-written Java in a converted project.
• Database Access, the persistence layer in depth.
• Database Configuration; dirty-share in full: intra-session and cross-session behaviour, the configuration flags and their defaults, the per-table dirty-read hint, and the known limitations.
• QueryExecution; how a converted query becomes SQL, which is the processing native SQL replaces.
• Chapter 28 Database Record Locking, 4GL locking semantics.
• Chapter 27 Sorting Query Results, 4GL sort semantics and index order.
• Transactions, 4GL transaction scoping.
• Database User Defined Functions Overview, Native UDFs for PostgreSQL, the guarded_* functions.