Querying with Native SQL¶

• Querying with Native SQL
  • Scope
  • Choosing an Entry Point
  • Executing a Statement
    • A Single Scalar Value
    • Binding Parameters
    • A Result Set
    • Multi-Tenant Databases
  • Reading the Rows
    • Raw Column Values
    • Holding Records Outside a Buffer
    • Loading Records into a Buffer
  • What a Native Query Cannot See
    • Changes Not Yet Flushed in the Same Session
    • Uncommitted Changes in Other Sessions
    • Changes Committed While the Query Runs
  • Reading Records Under a Lock
    • The Order in Which load Works
    • Three Routes
    • Why Projection Comes First
  • Result-Set Lifetime and Size
    • Preselect, Not a Live Cursor
    • A Transaction Boundary Invalidates the Cursor
    • Reading Without a Cursor
    • Cursor Options
    • Bounding the Result Set
  • Prepared Statements and Statement Caching
  • Worked Example: An Aggregate Report
  • Common Mistakes
  • API Completeness
  • Related Pages

Scope¶

This page covers executing a hand-authored SELECT against a FWD database and reading its results. It is the Level 3 counterpart of the read path described in Layers of the Persistence Stack, and assumes the decisions made there: that no 4GL formulation achieves the same result, and that the guarantees forfeited below Level 1 are acceptable.

Modifying data is covered separately in Modifying Data with Native SQL. Turning result rows into managed records is covered on this page, under Loading Records into a Buffer, and failure handling under Common Mistakes.

Choosing an Entry Point¶

Five entry points read data, at decreasing levels of runtime involvement. The first two accept FQL and are documented here only for comparison; the last three accept SQL and are the subject of this page.

The pairing mirrors the FQL side: executeSQLRows is to executeSQLQuery what list is to scroll. Prefer it wherever the row count is bounded, because a materialised list has no cursor and therefore none of a cursor's hazards; see Reading Without a Cursor. Where records rather than raw values are wanted, the projection-describing overload is the subject of Loading Records into a Buffer.

Other SQL methods appear on the same interface but are runtime internals rather than application API; they are not documented here and should not be called.

Executing a Statement¶

A Single Scalar Value¶

Use the scalar form for a statement that yields one value, and pass the bound values as one array.

A report predicate rarely binds a single value, and a predicate assembled at run time binds a count not known when the helper was written. Both cases want the values carried as an array rather than as one parameter per value. The 4GL expresses a mixed array by declaring its element type as a Java type, which is a documented special case: an element that is an FWD value is passed through exactly as it is, with no conversion.

def var db      as character no-undo initial "hotel".
def var country as character no-undo initial "USA".
def var fromDay as date      no-undo initial 1/1/2026.
def var maxDisc as decimal   no-undo initial 15.00.
def var args    as java.lang.Object extent 3 no-undo.
def var cnt     as integer   no-undo.

/* character, date and decimal in one array */
assign args[1] = country
       args[2] = fromDay
       args[3] = maxDisc.

cnt = NativeSQLAdapter:scalarIntWith(db,
        "select count(*) from reservation r, guest g" 
      + " where g.guest_id = r.guest_id" 
      + "   and upper(rtrim(g.country)) = ?" 
      + "   and r.checkin  >= ?" 
      + "   and r.discount <= ?", args).

public static integer scalarIntWith(character db, character sql, Object[] args)
{
   String                      stmt = text(sql);
   ScrollableResults<Object[]> rows = null;

   try
   {
      rows = persistence(db).executeSQLQuery(stmt, args);

      if (!rows.next())
      {
         // no row matched: the unknown value, not an error
         return new integer();
      }

      Object[] row   = rows.get();
      Object   value = (row == null || row.length == 0) ? null : row[0];

      return (value == null) ? new integer()
                             : new integer(((Number) value).intValue());
   }
   catch (PersistenceException exc)
   {
      throw failed(stmt, exc);
   }
   finally
   {
      close(rows);
   }
}

Two details of that array are worth stating.

• Assign 4GL values to it directly. They are bound as they are, so nothing has to be converted or wrapped first. A homogeneous integer extent or character extent works equally well where the values share a type.
• An extent cannot be sized at run time, and it passes every element it declares. An array declared larger than the statement needs binds unknown values past the last placeholder, and the database rejects the statement with a column-index error. Where the count is decided at run time, pass the number of meaningful elements alongside the array so the surplus can be trimmed, or join a temp-table into the statement instead.

The helper above reads one value and stops, so extra columns and extra rows are discarded and a statement that accidentally matches many rows still yields a value rather than an error. That is the helper's choice rather than a property of executeSQLQuery, but it is the usual shape, so bound the statement wherever a second row would be a defect.

Two properties of the entry point itself are worth knowing.

• A statement matching no rows is indistinguishable from one whose first column is genuinely SQL NULL. Both arrive as null, so the unknown value that reaches 4GL does not say which occurred.
• Transaction scope is the caller's responsibility. Unlike the FQL entry points, executeSQLQuery does not open a transaction of its own, so a statement issued with none active should be preceded by beginTransaction and followed by commit or rollback. Within converted 4GL a transaction is normally already open.

Binding Parameters¶

Use ? placeholders and pass values positionally. Do not assemble SQL by concatenating values into it.

The statement text and the value array have to agree on one thing: the number of placeholders must equal the number of values after flattening. Where the count is decided at run time, build both from the same source in the same loop, so that they cannot drift apart.

def var db     as character no-undo initial "hotel".
def var wanted as character no-undo initial "USA,Canada,Mexico".
def var args   as java.lang.Object extent 20 no-undo.
def var stmt   as character no-undo.
def var idx    as integer   no-undo.
def var cnt    as integer   no-undo.

stmt = "select count(*) from guest where upper(rtrim(country)) in (".

do idx = 1 to num-entries(wanted):
   if idx > 1 then
      stmt = stmt + ", ".

   assign stmt      = stmt + "?" 
          args[idx] = entry(idx, wanted).
end.

stmt = stmt + ")".

/* the count matters: an extent of 20 would otherwise bind 20 values */
cnt = NativeSQLAdapter:scalarIntWith(db, stmt, args, num-entries(wanted)).

The Java behind this is the scalarIntWith shown above, with one extra parameter: the number of leading elements to bind. That parameter exists because a 4GL extent is fixed at its declared size and passes all of it, so an array sized for the worst case cannot simply be under-filled. Note that stmt rather than sql names the variable: SQL is a 4GL keyword and will not parse as an identifier.

Bind through executeSQLQuery or executeSQL. Both accept 4GL values and Java values in the same array, which is what allows a mixed predicate to be expressed without converting anything first. The supported types are:

• 4GL: character, integer, int64, decimal, logical, date, datetime, datetime-tz, rowid, recid, handle, comhandle, blob, clob, raw and object.
• Java: String, Integer, Long, Double, Float, BigDecimal, Boolean, byte[], java.util.Date, java.sql.Date, java.sql.Timestamp and java.time.OffsetDateTime. Note java.sql.Blob and java.sql.Clob appear in the registry but are not bindable: those entries serve an internal path and expect a byte[] or a String, so passing a driver's Blob is refused. Bind the byte[] or String instead.

An unsupported Java type is rejected. Types are matched exactly, so a near relative of a supported type is not supported: java.time.LocalDate and java.time.Instant are unsupported although java.util.Date and java.sql.Date are, as are Short, Byte and enums. Binding one fails with the offending position and class. Only hand-written Java can reach this; convert to a supported type first.

That rejection is recent, and it is deliberately narrow. Previously any value without a handler was bound as SQL NULL and the statement ran, so a predicate matched nothing and reported success while a DML statement wrote NULL over real data. Now a value that is not a 4GL type fails instead.

A 4GL type with no handler still binds NULL. The rejection does not extend to FWD's own types, and must not: the unknown value binds as NULL because that is precisely what ? means in the 4GL, and converted code depends on it. The rarer case of a valued 4GL type with no registered handler, such as a longchar or memptr in a predicate, also still binds NULL rather than failing a query that used to run, but is now reported at WARNING with the parameter position and class so the gap is visible rather than silent.

Two counting rules.

• An array or list argument expands. Its elements become consecutive placeholders rather than one value, which is how an IN list is expressed. Count placeholders after expansion. The exception is an array type that is itself a supported bind type -- byte[] is the only one -- which binds whole as a single value against one placeholder, because a bytea column wants the bytes, not one placeholder per byte.
• A datetime-tz consumes one placeholder in a query and two in an update. It is stored across two columns, a timestamp and an offset, but only a statement that writes binds both; a predicate binds the timestamp alone. So the count differs between executeSQLQuery and executeSQL for the same value.

Beyond correctness, bind parameters are what make statement caching effective; see Prepared Statements and Statement Caching.

A Result Set¶

Use the row-collecting form for a statement that yields rows. The adapter closes the result set.

Returning the projected column as a list keeps the 4GL free of per-row column extraction, which otherwise needs a Class literal, a CAST and an unboxing assignment for every value read.

The persistence, text, failed and close helpers used by the methods on this page are listed under Shared Helpers.

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 exists (select 1 from guest g" 
          + "                where g.guest_id = r.guest_id" 
          + "                  and upper(rtrim(g.country)) = ?)" 
          + " order by r.checkin asc, r.recid asc" 
          + " limit 500", "USA").

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

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

Closing is the caller's responsibility and is not optional, which is a further reason to keep it inside the adapter. An unclosed result set holds server-side resources and keeps its statement checked out of the connection pool. The finally block above is what makes that reliable, since the error paths are exactly the ones that leak.

Note also that the statement is prepared with JDBC defaults, which makes the result forward-only and applies no fetch size. Only forward iteration works. Backward navigation compounds the problem by failing quietly: the underlying exception is caught, logged at SEVERE, and false returned, which is indistinguishable from reaching the end of the rows.

Multi-Tenant Databases¶

In a multi-tenant runtime, use the overload that takes the context flag explicitly.

executeSQLQuery(sql, args) and executeSQL(sql, args) infer the persistence context. The inference is best-effort and is documented as unreliable under multi-tenancy. The three-argument overloads take the context explicitly, using Persistence.PRIVATE_CTX for a tenant-private database and Persistence.SHARED_CTX for the shared one:

rows = fwd-db:executeSQLQuery(sql, Persistence:PRIVATE_CTX, args).

Single-tenant applications may use the shorter overloads safely.

The entry points added for native SQL do not infer anything: executeSQLRows, executeSQLGuarded and the hydrating executeSQLQuery overloads all run on the tenant-private context, because that is the right default for a hand-authored statement.

That default is wrong for a table which is not tenant private, and the consequence is quiet rather than loud: the statement reads this tenant's copy of a shared table, and because the rows are registered in the tenant's session, a later mutation flushes to the copy instead of to the shared row. So under multi-tenancy, name the context for a shared table -- each hydrating overload has a counterpart taking the flag, and !dmoMeta.multiTenant is the value to pass. A shape whose tenancy disagrees with the context it is about to run on is reported at WARNING (MTCtx: hydrating a shared table on the tenant-private context...). The warning does not redirect the statement: the tables named in it are the caller's, and rerouting it would address a table the caller never wrote. A join mixing shared and tenant-private tables is a stronger case and is refused outright, because the two live in different physical databases: no one statement and no value of the flag can read both, so there is nothing to warn about and no correct way to proceed. Read each database with its own statement and combine the results.

Both judgements are made against this context's tenant rather than the process's, and only where the runtime has active tenants and the authenticated tenant is not the default one. A session which never authenticated carries the default tenant, sees no shared and tenant-private split worth policing, and is left alone; so is every single-tenant deployment.

executeSQLGuarded has no shared-context form; a shared-table statement needing a savepoint has to use executeSQL and do its own savepoint handling, as Working with Savepoints describes. Single-tenant deployments are unaffected throughout -- there is one context, so nothing can disagree.

Reading the Rows¶

Raw Column Values¶

A native result set yields raw JDBC values, one per projected column, with no records involved.

Use get() for any projection and index the result positionally. Use get(true) when the projection is recid only, and get(index, class) when one column is wanted and its type is known.

Gather a multi-column projection from the row array, indexing by position. Column indexes are zero based, and the order is the order of the SELECT list, so the statement text and the indexes have to be read together.

def var db   as character no-undo initial "hotel".
def var args as java.lang.Object extent 1 no-undo.
def var rows as character no-undo.

args[1] = 1/1/2026.

/* three columns: int4, text, date */
rows = NativeSQLAdapter:rowListWith(db,
         "select r.reservation_id, g.last_name, r.checkin" 
       + "  from reservation r, guest g" 
       + " where g.guest_id = r.guest_id" 
       + "   and r.checkin >= ?" 
       + " order by r.checkin asc, r.recid asc", 3, args).

/* rows now holds "8801|Smith|2026-01-04,8802|Jones|2026-01-09" */

public static character rowListWith(character db,
                                    character sql,
                                    int       columns,
                                    Object[]  args)
{
   String                      stmt = text(sql);
   ScrollableResults<Object[]> rows = null;
   List<String>                out  = new ArrayList<String>();

   try
   {
      rows = persistence(db).executeSQLQuery(stmt, args);

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

         for (int i = 0; i < columns; i++)
         {
            if (i > 0)
            {
               line.append('|');
            }

            // row[0], row[1], row[2] ... in SELECT-list order
            Object value = (row == null || i >= row.length) ? null : row[i];

            line.append(value == null ? "?" : value.toString());
         }

         out.add(line.toString());
      }
   }
   catch (PersistenceException exc)
   {
      throw failed(stmt, exc);
   }
   finally
   {
      close(rows);
   }

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

That gatherer is type-agnostic: it renders every column through toString, which is what a report line or a test assertion wants. Where the individual values are needed as Java types, cast each element of the row array, remembering that the types are the driver's rather than the schema's. A recid arrives as Long from int8, a count(*) as Long rather than Integer, a character column as String, and a date as java.sql.Date:

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

   Integer        id      = (Integer) row[0];         // int4
   String         surname = (String) row[1];           // text
   java.sql.Date  checkin = (java.sql.Date) row[2];    // date

   // a NULL column is a null element, so test before unboxing
   int day = (checkin == null) ? 0 : checkin.toLocalDate().getDayOfMonth();
}

get(index, class) reads one typed column, and its type parameter is the column type rather than the row type, so a projection of mixed types can be read a column at a time on the same result set:

while (rows.next())
{
   Integer id      = rows.get(0, Integer.class);
   String  surname = rows.get(1, String.class);
}

The requested type must match the column's SQL type. Asking for the wrong one raises rather than returning null: get(0, Long.class) on an int4 column reports that the column cannot be read as a Long, naming the position and the type. That is deliberate, because a null there would be indistinguishable from a genuinely NULL column and would turn a wrong type into empty data that reports success. A null return therefore means only one thing: the column really was NULL, or the cursor is not on a row.

Two projection details matter when reading positionally. Against a temp-table, the generated column order places eight reserved columns ahead of the declared fields, so select * does not begin where the schema suggests; see Temp-Table Multiplexing and Hidden Columns. Against any table, naming the required columns explicitly avoids the question entirely and is preferable for the reasons given under Full-Row and Projection Queries.

Holding Records Outside a Buffer¶

Do not. Load records into a buffer instead; see Loading Records into a Buffer.

It is possible to obtain record instances from a native statement and hold them in program variables, in a collection, or pass them to other code. It is documented here because it looks like the obvious thing to do and because the route exists, not because it is supported practice.

The problem is eviction. A record held by a buffer is protected: the runtime maintains a use count per record instance precisely so that, in the words of the code that maintains it, a record is not evicted while any buffer still needs it. A record held only by application code has no such count. It can therefore be evicted from the ORM session cache while the caller still holds the reference, and nothing announces this.

Two consequences follow, and neither is detectable at the point of use.

• The instance goes stale. After eviction the reference is to a detached copy. Its field values are whatever they were when it was evicted, and they no longer track the database or any subsequent change made through a buffer.
• The one-instance rule is broken in the caller's hands. The whole reason to resolve keys through the runtime rather than construct records is that each row has exactly one instance per context. Once the held instance is evicted, a later load of the same row produces a different instance, and the program has two objects for one row with no indication which is current.

So: no collections of records, and nothing handed to legacy code. A record instance is safe only for the duration of the statement or block that obtained it, before anything can trigger eviction. Anything longer-lived must be a buffer, or must be re-resolved from its key at the point of use.

The route below is therefore appropriate in one narrow case: the keys are already in hand, from an earlier statement or elsewhere in the program, no projection is available to hydrate from, and each record is used and discarded immediately inside the loop. The adapter deliberately returns a count rather than the records, so that nothing escapes.

Loading Records into a Buffer¶

Project the key and the fields the loop reads, and let the runtime load the buffer. The loop body is then ordinary 4GL.

This is the shape that replaces a converted FOR EACH with a hand-authored statement. The statement decides which rows and in what order; the runtime produces the session's record for each row; the buffer is loaded on each advance. The loop body reads buffer fields exactly as it did before, so only the query changes.

Three pieces are involved, and only the first is written by the caller.

• A field list naming the fields the body reads, in the same order as the projection. The statement selects recid first, then those fields.
• A row structure built from that field list, which tells the runtime which field occupies which column. RowStructure:forFields builds it.
• A result set that hydrates, obtained from the executeSQLQuery overload that accepts a structure. Its rows are records rather than column values.

Rewriting a FOR EACH. The 4GL on the left is what conversion would produce a query for. The 4GL on the right runs the same loop over a hand-authored statement.

for each reservation
   where reservation.checkin >= fromDay
   no-lock
   by reservation.checkin:

   display reservation.reservation-id
           reservation.state.
end.

def var args as java.lang.Object extent 1 no-undo.
def var rows as NativeResults              no-undo.

args[1] = fromDay.

rows = NativeSQLAdapter:openRecords(db, BUFFER reservation:HANDLE,
         "select recid, reservation_id, state from reservation" 
       + " where checkin >= ?" 
       + " order by checkin asc, recid asc",
         "reservation-id,state", args).

do on error undo, throw:
   do while rows:next():
      display reservation.reservation-id
              reservation.state.
   end.

   finally:
      rows:close().
   end finally.
end.

The body is unchanged. What changed is that the row set is now chosen by a statement the developer wrote, with the consequences set out under the end of this section.

The Java behind it.

rows = NativeSQLAdapter:openRecords(db, BUFFER reservation:HANDLE,
         "select recid, reservation_id, state from reservation" 
       + " where checkin >= ?" 
       + " order by checkin asc, recid asc",
         "reservation-id,state", args).

do while rows:next():
   /* ... */
end.

public static NativeResults openRecords(character db,
                                        handle    buf,
                                        character sql,
                                        character fields,
                                        Object[]  args)
{
   String       stmt   = text(sql);
   RecordBuffer buffer = recordBuffer(buf);
   List<String> names  = fieldNames(fields);

   try
   {
      // recid first, then the named fields in SELECT order
      RowStructure shape = RowStructure.forFields(buffer.getDMOInterface(),
                                                  names.toArray(new String[0]));

      ScrollableResults<Object[]> rows =
         persistence(db).executeSQLQuery(stmt, args, shape);

      return new NativeResults(rows, buffer);
   }
   catch (PersistenceException exc)
   {
      throw failed(stmt, exc);
   }
}

NativeResults is a thin wrapper holding the rows and one buffer per row structure. Its next() is what the 4GL loop condition calls:

public logical next()
{
   if (done)
   {
      return new logical(false);
   }

   if (!rows.next())
   {
      // off the end: leave every buffer empty rather than holding the last row
      done = true;

      for (RecordBuffer buffer : buffers)
      {
         buffer.loadRecord(null);
      }

      return new logical(false);
   }

   Object[] row = rows.get();

   // one record per structure, in projection order: a two-table join loads
   // two buffers from one row
   for (int i = 0; i < buffers.length; i++)
   {
      buffers[i].loadRecord((Record) row[i]);
   }

   return new logical(true);
}

Running off the end releases the buffers, so AVAILABLE reports false afterwards, matching what a converted query leaves behind. It does not close the result set, deliberately: closing releases the session pin, and doing that from inside the loop condition lets the session be reclaimed while the enclosing 4GL block is still live.

Check getReadError after the loop. A row whose record cannot be built arrives as a null row, which is indistinguishable from running off the end, and a failed advance ends the loop exactly as exhaustion does. So a loop can stop short or skip a record and look like a clean read. After the loop, a non-null getReadError means the records seen are not the records the statement matched. This cannot be raised for you: next() and get() report failure leniently because every converted query depends on that, so the check belongs to the caller. executeSQLRows makes exactly this check internally, which is how it can promise a complete list.

Close in a FINALLY block, always. Closing releases the session pin as well as the cursor, and releasing that pin from inside a loop condition would let the session be reclaimed while the enclosing block is still running; the block's own commit then fails with no current transaction available to commit. So the advance deliberately leaves the result set open and closing is the caller's job, in every case rather than only when a loop exits early.

Closing is idempotent, so a FINALLY that runs after an inner close is harmless, and the pin is released exactly once however many times close is called. That matters because a double release would drive the context's session use count below zero, after which an unrelated query's cursor could have its session closed underneath it.

Close does not raise. A failure to close is recorded and reported by getCloseError, which is kept apart from getReadError because the two mean different things: a read error says the rows are not the rows the statement matched, a close error says a statement or cursor may not have been released at the database. Neither masks the other, and a FINALLY that closes cannot therefore throw over whatever sent control there.

A caller may also register its own cleanup on the result set. Hooks accumulate rather than replace one another, and every one runs on close even if an earlier one throws, so adding cleanup does not disturb the release the runtime registered.

A result set that hydrates records pins its database session open for as long as it is held, which is what keeps the records it produced valid. That is the same treatment a converted query gets. Should the session be closed underneath it anyway, by a forced close or an error, the pin closes the result set as part of that: listeners are notified before the connection goes back to the pool, so the cursor is closed while its connection is still live rather than failing later against a returned one. A caller's own FINALLY still runs, and finds nothing left to do.

Nothing reclaims a hold that is never given back. A result set which is never closed pins its session for the rest of the context's life, and because the hold keeps the session's use count above zero, the session is not closable at the end of the enclosing block either; the cursor and its statement stay in memory with it. There is no collector-driven backstop, deliberately: the hold is the caller's to give back. Closing is therefore mandatory rather than tidy, and a FINALLY block is the only shape that guarantees it.

A join loading two buffers. One structure is supplied per participating table, and the runtime then produces one record per structure per row, so a two-table join loads two buffers from a single row. This is the shape that replaces a converted FOR EACH parent, EACH child.

The projection has one requirement: each table's columns must be contiguous, and each run must begin with that table's recid. A projection that interleaves the two tables' columns cannot be described.

for each sqlNative
   where sqlNative.itemNum eq 55 no-lock,
   each sqlNativeChild
      where sqlNativeChild.parentKey eq sqlNative.keyNum no-lock:

   display sqlNative.ciName sqlNativeChild.note.
end.

def var args as java.lang.Object extent 1 no-undo.
def var rows as NativeResults              no-undo.

args[1] = 55.

rows = NativeSQLAdapter:openJoin(db,
         BUFFER sqlNative:HANDLE,      "keyNum,ciName",
         BUFFER sqlNativeChild:HANDLE, "note",
         "select p.recid, p.key_num, p.ci_name, c.recid, c.note" 
       + "  from sql_native p, sql_native_child c" 
       + " where c.parent_key = p.key_num" 
       + "   and p.item_num = ?" 
       + " order by p.key_num asc, c.child_key asc", args).

do on error undo, throw:
   do while rows:next():
      display sqlNative.ciName sqlNativeChild.note.
   end.

   finally:
      rows:close().
   end finally.
end.

Both buffers are loaded on the same advance and released together at the end. Note that this is an inner join, so a parent with no children does not appear at all; the converted form above behaves the same way, but a converted FOR EACH ... , FIRST child OUTER-JOIN does not, and no native equivalent is provided here.

The Java is the two-structure form of openRecords:

List<RowStructure> shapes = new ArrayList<RowStructure>(2);

shapes.add(RowStructure.forFields(one.getDMOInterface(),
                                  fieldNames(fields1).toArray(new String[0])));
shapes.add(RowStructure.forFields(two.getDMOInterface(),
                                  fieldNames(fields2).toArray(new String[0])));

ScrollableResults<Object[]> rows =
   persistence(db).executeSQLQuery(stmt, args, shapes);

return new NativeResults(rows, one, two);

Letting the caller hold the cursor. Where the cursor is managed elsewhere, as generated code would manage it, the same work is available without the wrapper. openRows returns the hydrating result set directly and nextRecord loads a buffer from it, holding no state of its own:

def var rows as ScrollableResults no-undo.

rows = NativeSQLAdapter:openRows(db, dmo,
         "select recid, ci_name from sqlNative where item_num = ?",
         "ciName", args).

do on error undo, throw:
   do while NativeSQLAdapter:nextRecord(rows, BUFFER sqlNative:HANDLE):
      /* ... */
   end.

   finally:
      NativeSQLAdapter:closeRows(rows).
   end finally.
end.

This form is the one conversion would emit, since it already manages one cursor object per query block and can choose the buffer per advance. The bound form above is the one application code should prefer, because the buffer cannot then be mismatched with the statement.

Two name spaces, and either spelling works. Every field has a legacy name, as written in the schema and in converted source, and a SQL column name, which is what the statement text uses. They differ: FWD lowercases and inserts underscores when it generates the schema, so the legacy field reservation-id becomes the column reservation_id, and ciName becomes ci_name.

The field list accepts either. Each name is matched first as a legacy field name and then as a SQL column name, so a projection can be described in whichever spelling is already to hand, usually the column names, since those are what the statement in front of the author uses:

/* both of these describe the same projection */
rows = NativeSQLAdapter:openRecords(db, BUFFER reservation:HANDLE,
         "select recid, reservation_id, state from reservation" 
       + " where checkin >= ?", "reservation-id,state", args).

rows = NativeSQLAdapter:openRecords(db, BUFFER reservation:HANDLE,
         "select recid, reservation_id, state from reservation" 
       + " where checkin >= ?", "reservation_id,state", args).

A name matching neither spelling is rejected. The error names the offending field and the table; the candidate legacy and column names are written to the log at FINE rather than into the message, because an exception's text is copied into the 4GL error list and shown to the user, and a converted table of two or three hundred fields would put every name there twice over. The reverse mistake, a legacy name inside the statement text, is rejected by the database.

Choosing the field list. Name every field the body reads, and no more. Five properties follow from that.

• At least one field must be named. A projection describing nothing but the key is refused, because a row of nothing but keys is returned as raw values rather than records; where a statement genuinely selects only keys, resolve each one instead as Holding Records Outside a Buffer describes.
• Naming an expanded array field names the whole array. An expanded extent is stored one column per element, suffixed _1 upwards, so naming qty on a qty[5] describes five columns and obliges the statement to project qty_1 through qty_5, in that order and contiguously. Individual elements cannot be named: the structure walks an extent from its first element, so a projection of some elements but not others cannot be described. A non-expanded extent is a different matter and cannot be reached at all: its values live in a separate table, so it cannot appear in the projection, and naming it is refused rather than silently ignored. A loop that needs one wants a second query against the extent table, or a converted query for the whole loop.
• An unnamed field is not readable. This is the rule to get right, because it is the opposite of what the mechanism suggests. The records are marked incomplete, but nothing tops them up: reading a field the projection omitted raises error 8826, Field <name> from <table> record (recid N) was missing from FIELDS phrase. There is one exception, and it is too narrow to rely on -- the record has to be read through a buffer and the -rereadfields startup parameter has to be active, which it is not by default. So a short field list is a commitment, not a cheap guess to be corrected later. Name every field the loop body touches.
• The order must match the projection. The structure is positional after recid. A field list naming a different number of fields than the statement projects is rejected when the query runs, with both counts reported, and the rejection is a plain failure: nothing is rolled back and the session stays usable, because the statement changed nothing. A list of the right length in the wrong order is also rejected where the projection names real columns, since the runtime derives one expected column name per projected column and can see that the labels are those names transposed. That derivation is per column rather than per field, so it survives the fields which occupy more than one: an expanded extent contributes col_1 upwards and a datetime-tz contributes its _offset companion, and both are checked in place. What defeats it is a label that is not a column name at all, an alias or an expression, and ordering among those remains the caller's responsibility.
• select * is the wrong instinct. The primary key must come first, extent fields occupy contiguous runs of columns, and a datetime-tz occupies two. Naming the columns avoids all of it.

Temp-tables cannot be loaded this way, and the attempt is refused. Their rows carry a _multiplex discriminator, and one physical table holds the rows of every buffer sharing that schema within the context; converted queries stay isolated only because the runtime injects a _multiplex predicate into each one. A hand-authored statement has none, so it can return another buffer's rows, and a record hydrated without the discriminator silently updates nothing when it is flushed. The discriminator identifies a buffer instance, not the schema, so a row structure built from a DMO interface has no way to carry it: describing a temp-table raises rather than misbehaving. Use a converted query.

Read that refusal narrowly. It closes hydration, not access. The temp-table database, a temp-table's physical table name and a buffer's multiplex value are all reachable through public API, FWD uses that route itself, and nothing prevents application code from issuing a raw SELECT against those rows. What is missing is any safety: omit the _multiplex predicate and the statement silently returns another buffer's rows, and the value is assigned to a buffer instance at run time rather than being a property of the schema, so it cannot be hard-coded or cached across scopes. If a raw read is genuinely required, Temp-Table Multiplexing and Hidden Columns sets out the eight reserved columns and the rules that go with them. Records, and therefore anything that has to be written back, remain the converted query's job.

What the loop gives up. A native loop is a read-only pass over committed data. Relative to the converted query it replaces, it does not acquire locks, does not see uncommitted changes held by other buffers or other sessions, does not see records the session has not yet flushed, does not have its index chosen by the optimizer, and does not survive a transaction boundary. Each of those is covered in its own section on this page. Where any of them is required, the converted query is the correct implementation and the statement should not be hand-authored at all.

What a Native Query Cannot See¶

A native SELECT reads the physical database and nothing else. Three categories of change are therefore invisible to it, for three different reasons.

Changes Not Yet Flushed in the Same Session¶

A record created or updated in this session but not yet flushed is invisible, because no row exists for the statement to read.

The runtime holds such a record in memory, in a structure called the record nursery, and a converted query consults the nursery before reading the database. A native statement does not. This is not an isolation question: the data has not been sent.

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

Flushing is not a neutral act: it fires the WRITE trigger. The flush path invokes the trigger before writing the row, so forcing a flush to make a record visible to a native statement also runs whatever the WRITE trigger does, at that point in the program rather than wherever it would otherwise have happened. Where the trigger has side effects, audit columns, derived values, downstream publishing, those side effects are pulled forward too. A RELEASE inserted purely to satisfy a native query is therefore a business-logic change as well as a timing change, and needs to be considered as one.

The practical consequence is an ordering constraint. 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. Records mid-creation are the common case: they are visible on a fully updated index in the 4GL sense while several of their fields are still unset.

def var db as character no-undo initial "tstcasesdb".

create sqlNative.
assign sqlNative.keyNum = 301
       sqlNative.ciName = "NURSERY".

/* No row exists yet: the record is in the nursery, not the database. */
message NativeSQLAdapter:scalarInt(db,
   "select count(*) from sql_native where key_num = 301").   /* 0 */

/* RELEASE flushes it. No commit is required. */
release sqlNative.

message NativeSQLAdapter:scalarInt(db,
   "select count(*) from sql_native where key_num = 301").   /* 1 */

The order of those two statements is not incidental, and reversing them hides the behaviour. A converted query resolves against the nursery, and resolving publishes a valid persistent record to its database. So a FIND or FOR EACH placed before the native statement, even one written only to check what is there, flushes the record as a side effect and the native statement then finds it. The first native read has to come before any 4GL query touches the table, or the effect being observed is the diagnostic's own.

This is also why a native statement placed after unrelated 4GL work on the same table may appear to see uncommitted records. It is not seeing the nursery; the earlier query flushed it.

Note that this applies to temp-tables exactly as it does to persistent tables, since several buffers within one session can observe each other's unflushed changes.

Forcing the nursery is not the answer. The runtime has an internal operation that publishes held records, but it works one index at a time rather than whole-table, it validates as it publishes -- so a not-yet-valid record, which is frequently why the record is held at all, raises at a point the program did not choose -- and it is not reachable from application code by design. RELEASE on each buffer being created or updated does the same job explicitly, in the order the program wants, validating where the developer chose.

Uncommitted Changes in Other Sessions¶

Another session's uncommitted changes are invisible, and there is no way to observe them.

The 4GL exposes an updated index to other sessions immediately, so converted code can observe records that are neither committed nor fully initialised; the runtime reproduces this through cross-session dirty share. No SQL isolation level offers it, and under READ COMMITTED another transaction's uncommitted rows do not exist as far as the statement is concerned. This matters less than it appears: cross-session dirty share is off by default, only partly supported, and slated to be dropped, and it cannot arise for temp-tables at all. Database Configuration has the flags and the per-table dirty-read hint.

The discipline is to avoid partial compensation. Looking up a dirty-share image for each row the statement returned repairs only the rows that were returned, and the records at issue are precisely those that were not -- one may sort anywhere in the ordering, including past a LIMIT or before the first row examined. Target data known to be flushed and committed instead.

Changes Committed While the Query Runs¶

A native query is a point-in-time read. A converted FOR EACH is not.

A converted FOR EACH walks an index under the runtime's control and is re-driven as it advances, so it picks up other sessions' commits while the loop still runs. A native query has no such mechanism, and the isolation level it inherits gives two effects.

Iterating one result set reflects the snapshot taken when the statement executed, including where the driver streams under a fetch size, because a JDBC cursor never observes other sessions' commits as it advances. Iteration is therefore internally consistent, at the cost of staleness: an image in hand may already be out of date, and since no lock was taken, nothing stops it changing again before it is acted upon.

Re-executing the statement takes a fresh snapshot, so rows may appear, change or vanish between the two runs -- phantom and non-repeatable reads. Two executions are not guaranteed to agree, so a re-read is never a correctness check.

Locking is the only mitigation; see Reading Records Under a Lock below. Absent that, treat the result as a snapshot and act on it once.

Reading Records Under a Lock¶

A native SELECT never takes a lock. Locking belongs to load, and is fully available to hand-written code.

This can be a misunderstood point when combining native SQL with the 4GL lock model. FWD locks are application-level locks held in the runtime rather than in the database, so a raw statement neither observes nor acquires them: a native SELECT behaves as NO-LOCK and will read a record another session holds EXCLUSIVE.

What matters is that locking is not a facility of the query objects. It belongs to Persistence.load, which sits at Level 2, and the query objects call into it. A converted query needing EXCLUSIVE-LOCK resolves the primary key first and then calls load, which acquires the lock and hydrates the record. Hand-written code can do precisely the same.

The Order in Which load Works¶

load takes two steps, in this order:

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 the whole point. It lets a session take ownership of a fully hydrated record, with no window in which another session could modify the row between the lock being taken and the data being read. The implementation guards the converse case too, releasing the lock again if the record turns out to have been deleted after its identifier was obtained but before its data was retrieved.

Three Routes¶

All three are reachable through UnsafePersistence.

The third is the pattern this page recommends. The SQL decides which rows; load supplies the locking and the hydration:

ScrollableResults<Object[]> rows = persistence.executeSQLQuery(
   "select r.recid from reservation r where ... order by ...", args);

while (rows.next())
{
   Long   pk  = (Long) rows.get(true)[0];
   Record rec = persistence.load(implClass, pk, LockType.EXCLUSIVE, timeout, true);
}

Why Projection Comes First¶

Do not select full rows and lock them afterwards. The ordering is not a stylistic preference. 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. The data in hand is therefore already potentially stale at the moment it becomes owned, and nothing reports the fact.

Projecting recid and letting load lock before it hydrates closes that window. This is the substantive reason to prefer projections here, over and above their memory and staleness characteristics described under Full-Row and Projection Queries.

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 field narrowing and locking cannot be combined.

A load per key costs a round trip per record, and holds the earlier locks while the later ones are still being taken, so for more than a handful of records the locks are better acquired in bulk beforehand; see Record Locking.

Temp-tables are exempt from all of this. They are private to their session, so nothing can contend for their rows and the runtime applies no locking to them.

Result-Set Lifetime and Size¶

Preselect, Not a Live Cursor¶

The result set is fixed at execution. The equivalent 4GL construct is DO PRESELECT, not FOR EACH.

A converted FOR EACH does not decide its result set in advance. A native query does: whatever the database determined at execution is what will be iterated, and later changes are invisible until the statement is re-executed. The distinction matters when comparing execution times, because a preselect and a FOR EACH do not perform the same work, and it matters when replacing one with the other, because business logic written against an index walk may assume it sees concurrent changes.

A Transaction Boundary Invalidates the Cursor¶

Do not allow a transaction to end while a result set is still being read.

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 the error is portal "C_n" does not exist.

The runtime avoids this for its own preselect queries by listening for the commit and draining the cursor into an in-memory row list before it happens, so the query remains usable afterwards. Hand-written code cannot do the same, because the session-listener registration is not public API.

The common form of the mistake is a transaction nested inside the reading loop, because the loop body looks like ordinary business logic. The remedy is to finish reading before any transaction ends: collect the keys, then do the transactional work.

rows = NativeSQLAdapter:openRecords(db, BUFFER reservation:HANDLE,
         "select recid, state from reservation where checkin >= ?",
         "state", args).

do while rows:next():

   /* this transaction commits at END, taking the cursor with it */
   do transaction:
      find current reservation exclusive-lock.
      assign reservation.state = "CLOSED".
   end.

end.   /* next advance: portal "C_1" does not exist */

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

/* read to completion first; nothing is open afterwards */
keyList = NativeSQLAdapter:firstColumnListWith(db,
            "select reservation_id from reservation" 
          + " where checkin >= ?" 
          + " order by checkin asc, recid asc", args).

do idx = 1 to num-entries(keyList):

   do transaction:
      find reservation
         where reservation.reservation-id eq integer(entry(idx, keyList))
         exclusive-lock no-error.

      if available reservation then
         assign reservation.state = "CLOSED".
   end.

end.

The working form pays for its safety twice: once to read the keys, and once per key to re-find the record. That is the cost of a result set which does not survive a commit, and it is why the read and the write are best kept in separate passes rather than interleaved.

Two further notes on the working form. The keys are read in a single statement that completes before any transaction opens, so no cursor is alive when the first commit happens. And each FIND is scoped to its own transaction, so a failure on one key does not roll back the keys already processed, which the interleaved form cannot offer either.

Alternatively read on a dedicated connection, which is not subject to the 4GL transaction at all; see Using a Separate Database Connection.

Where a statement is issued outside a transaction, open one before it and close it after the result set has been fully processed, so that the cursor's lifetime is defined rather than incidental.

Reading Without a Cursor¶

Where the row count is bounded, take the rows as a list and skip the cursor entirely.

Most of the difficulty on this page is cursor lifetime: closing it, pinning its session, keeping a transaction from ending underneath it. None of that applies if the rows are read to completion and the cursor closed before anything else happens, which for a report or an aggregate is what the code wants anyway.

// read, close, return: nothing left open, nothing to release
List<Object[]> rows = persistence.executeSQLRows(sql, args, 500);

for (Object[] row : rows)
{
   Long   id   = (Long) row[0];
   String name = (String) row[1];
}

The cap is the second argument, and it is applied to the statement rather than merely to the list, so it bounds what crosses the connection. A value of one or more is honoured; anything lower reads every row, which is safe only where the statement bounds itself. Passing a cap is the better habit, because it fails visibly at a known size rather than by exhausting the heap.

The list is complete or it raises. A row that cannot be read is not dropped and not returned as a null element, which matters because the cursor underneath reports a failed read the same way it reports the end of the rows. That ambiguity is the cursor's, and reading without one is how to avoid inheriting it.

Rows, not records. This form yields raw column values deliberately. Materialising records into a list is not offered, because a record held outside a buffer can be evicted and go stale; see Holding Records Outside a Buffer. Where records are needed, use the buffer-loading route and consume each record as the cursor advances.

Cursor Options¶

Ask for a scrollable cursor when rows are read more than once, and bound the fetch size when the result set is large.

A native statement is prepared forward-only with the configured default fetch size unless it says otherwise. Both are adjustable through an options value passed with the query.

// re-readable, and 500 rows per round trip rather than the whole set at once
NativeQueryOptions opts = NativeQueryOptions.DEFAULTS.scrollable().fetchSize(500);

ScrollableResults<Object[]> rows =
   persistence.executeSQLQuery(sql, args, shapes, opts);

The two settings address different problems.

• Scroll mode. On a forward-only cursor, previous, first and scroll return false rather than raising, which is indistinguishable from having reached the last row. Requesting scrollable() makes them work. The insensitive flavour is used, so the rows do not change under the reader, matching the snapshot semantics a native statement already has.
• Fetch size. A hint, and a conditional one. PostgreSQL opens a server-side cursor only when three things hold at once: the connection is out of autocommit, the cursor is forward-only, and a fetch size is set. FWD leaves autocommit on except inside a transaction, so a fetch size takes effect only for a forward-only cursor inside one. Outside those conditions the driver reads the whole result set into the application server heap before delivering the first row, which is the failure the hint appears to prevent. Combining it with scrollable() makes it doubly ineffective, because a scrollable cursor cannot be fetched incrementally at all.
• Row limit. maxRows(n) is not a hint. It is applied with setMaxRows, which a driver must honour whatever the autocommit state or cursor type, so it is what actually bounds the transfer. Where an upper bound is known, set it. This is the option to reach for; the fetch size is a refinement on top of it, not a substitute.

Two consequences worth stating plainly. A statement issued outside a transaction cannot be bounded by a fetch size at all, so bracket it with beginTransaction and commit if that matters, or bound it with a row limit. And because commit does not restore autocommit, whether a fetch size takes effect can depend on what the session did earlier, which is a further reason to prefer the row limit where a guarantee is wanted.

Options are immutable and each setter returns a new value, so a shared constant cannot be altered by a caller that reuses it.

Bounding the Result Set¶

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

Because executeSQLQuery applies no fetch size, the PostgreSQL JDBC driver has no server-side cursor from which to stream and transfers the entire result set into the application server heap before the first next returns. A query that behaves acceptably in psql can exhaust the JVM here. This is a property of the entry point rather than of SQL: the FQL path applies the configured fetch size and streams.

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

list deserves separate mention. It retains the entire result set in the returned collection, and its own documentation 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.

Prepared Statements and Statement Caching¶

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

Prepared statements are cached per pooled connection, governed by c3p0.maxStatementsPerConnection. A parameterised statement is prepared once and reused across executions. A statement assembled by concatenation is a distinct statement on every execution, so it never hits the cache and displaces entries that would otherwise be reused. The injection argument for bind parameters is the better known one; the caching argument is the one that shows up as latency.

Resource release matters as much. An unclosed result set retains server-side resources and keeps its statement checked out of the pool. In sufficient numbers this exhausts the pool, at which point unrelated database access begins to fail. Close in a FINALLY block.

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

Worked Example: An Aggregate Report¶

Aggregates are the case where native SQL is most clearly worthwhile, because the work happens at the database server and only the summary crosses the connection. This example counts reservations per country without transferring a single reservation row, and needs no records at all.

def var db     as character no-undo initial "hotel".
def var args   as java.lang.Object extent 1 no-undo.
def var summary as character no-undo.

args[1] = 1/1/2026.

/* upper(rtrim(...)) matches the index expression, not the bare column */
summary = NativeSQLAdapter:rowListWith(db,
           "select upper(rtrim(g.country)) as country," 
         + "       count(*) as bookings" 
         + "  from reservation r, guest g" 
         + " where g.guest_id = r.guest_id" 
         + "   and r.checkin >= ?" 
         + " group by upper(rtrim(g.country))" 
         + " order by bookings desc, country asc" 
         + " limit 25", 2, args).

/* summary now holds "USA|412,CANADA|97,MEXICO|31" */

public static character rowListWith(character db,
                                    character sql,
                                    int       columns,
                                    Object[]  args)
{
   String                      stmt = text(sql);
   ScrollableResults<Object[]> rows = null;
   List<String>                out  = new ArrayList<String>();

   try
   {
      rows = persistence(db).executeSQLQuery(stmt, args);

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

         for (int i = 0; i < columns; i++)
         {
            if (i > 0)
            {
               line.append('|');
            }

            Object value = (row == null || i >= row.length) ? null : row[i];

            line.append(value == null ? "?" : value.toString());
         }

         out.add(line.toString());
      }
   }
   catch (PersistenceException exc)
   {
      throw failed(stmt, exc);
   }
   finally
   {
      close(rows);
   }

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

Each point this example makes is a rule from earlier on the page.

• upper(rtrim(g.country)) matches the index expression rather than the bare column, so the grouping can use the index. See Authoring SQL Against the Generated Schema.
• Both columns are gathered, so the counts keep their labels. Reading only the count would produce numbers with nothing to attribute them to.
• order by bookings desc, country asc has a second sort key, so countries with equal counts come out in a defined order. An aggregate has no recid to fall back on, which makes the tie-break the caller's responsibility.
• limit 25 bounds the transfer even though the aggregate itself is small, because no fetch size is applied and the result would otherwise be materialised whole.
• count(*) arrives as a bigint, so the gatherer renders it through toString rather than assuming it narrows to an Integer.
• No records are produced or needed, so none of the hydration machinery applies. This is the case where raw column values are exactly right.

Every example on this page has an executable counterpart in the testcases project under tests/persistence/sql, with the adapter in srcnew/java.

Common Mistakes¶

Every entry below was produced by writing this page's examples and running them. The first table covers failures that announce themselves; the ones after it do not, which makes them worth reading even when nothing is broken.

Failures that report themselves.

Failures that report nothing. These are the ones worth guarding against deliberately, because the program continues and the data is wrong.

Three worth an example, because they look alike and only one of them gets through.

A miscounted field list is caught by the length check:

/* three columns after recid, two names: rejected at execution */
rows = NativeSQLAdapter:openRecords(db, BUFFER sqlNative:HANDLE,
         "select recid, key_num, ci_name, amount from sql_native",
         "keyNum,ciName", args).

A transposition among plain columns is caught too, by comparing the projected labels with the expected column names:

/* two columns, two names, but transposed: rejected at execution */
rows = NativeSQLAdapter:openRecords(db, BUFFER sqlNative:HANDLE,
         "select recid, key_num, ci_name from sql_native",
         "ciName,keyNum", args).

The same transposition behind aliases is not caught, because a and b are not column names and nothing can be compared. This succeeds and populates every record from the wrong column:

/* labels are not column names, so the order cannot be judged */
rows = NativeSQLAdapter:openRecords(db, BUFFER sqlNative:HANDLE,
         "select recid, key_num as a, ci_name as b from sql_native",
         "ciName,keyNum", args).

API Completeness¶

Reading data with native SQL should cost a statement and a loop, and nothing else. On the read path it very nearly does; one gap remains, recorded below.

What was missing has been added: an unsupported argument type is rejected rather than bound as SQL NULL; the field list is checked against the statement's real column count and, wherever the projection names real columns, against its column order, a comparison drawn per column so an expanded extent's elements and a datetime-tz offset are covered too; cursor options allow a scrollable cursor or a bounded fetch size; the per-column accessor is typed by the column and raises on a wrong type instead of returning null; raw rows can be materialised into a list in one call, which raises rather than truncating; an executeSQLQuery overload takes a caller-supplied row shape and returns managed records, with RowStructure:forFields building that shape from field names and expanding an extent field to one column per element; a hydrating result set pins its session for as long as it is held, and is closed by that session should it close first; and executeSQLGuarded undoes a failed statement rather than the enclosing 4GL transaction, confining it to a savepoint inside an open transaction and to a transaction of its own outside one.

The one open gap. A hydrated record is marked incomplete whether or not the field list covered the whole table, because nothing compares the number of fields read against the number the DMO has. Naming every field therefore still yields records flagged incomplete, and converted code that later touches one re-reads the row, since the find-by-rowid shortcut rejects an incomplete cached instance. So a full field list buys correct field access but not a free record. Closing it means having the structure report itself complete when it covers every field, which is a change to a code path all converted queries share, so it is recorded here rather than attempted alongside this work.

Three further limits remain by design rather than by omission, and each is documented where it bites rather than listed as a defect.

• A record must not be held outside a buffer. It can be evicted with no notification, going stale while the reference stays usable. This is a rule to follow, not a gap to close; see Holding Records Outside a Buffer. It is also why a record cursor cannot cross a transaction boundary, where raw rows can.
• A raw result set pins its session only on request. A read that keeps nothing needs no pin, so requiring one by default would cost every caller for the benefit of a few; ask for it when the cursor is held across anything that might close the session. See Reading Without a Cursor for the shape that avoids the question.
• A bind list decided at run time needs its length passed alongside. A 4GL extent is fixed at its declared size and passes every element, and FWD cannot infer how many are meaningful, because an unfilled element is the unknown value and binding an unknown deliberately is legal. A java.util.List carries its own length and sidesteps it.

The remaining work is on the write path -- batch DML preserving validation and triggers, bulk load-by-key, and the locking endpoint's name -- and is recorded in Running Native SQL.

Related Pages¶

• Running Native SQL is the parent page, and carries the decision table, the layer model and the consolidated API gap list.
• Modifying Data with Native SQL covers INSERT, UPDATE and DELETE, including locking, triggers, validation and primary-key allocation.
• Using a Separate Database Connection covers reading outside the current 4GL transaction.
• Writing FWD-Compatible SQL covers ORDER BY, index matching, character columns, unknown values and UDFs.
• Database Configuration documents the dirty-share flags and their defaults.
• Using EXPLAIN to Analyze SQL Performance documents confirming that a statement obtains the intended plan.
• 4GL Database Access Performance Tips covers the 4GL rewrites worth trying before descending to SQL.

© 2004-2026 Golden Code Development Corporation. ALL RIGHTS RESERVED.