BLOB, CLOB and COPY-LOB: test suite against OpenEdge, and fixes¶

Project: Database

Problem¶

BLOB and CLOB are marked Full for conversion and Basic at runtime. The recorded reasons are: "flushing behavior needs testing and work; lazy loading to be investigated; some encoding work outstanding" (the last for CLOB only). COPY-LOB is marked Full (R): the statement is complete, but the order in which it validates and reports errors does not always match the 4GL, and it inherits the limits of FWD's codepage conversion.

The work behind these types is spread across many tasks, most of them closed or old. BLOB_data_type lists about twenty known limitations. The tasks it cites include:

• #3500 implement CLOB and BLOB support (Closed). Still cited for fetching the whole lob and for lob import.
• #4421 blob/clob access with H2 (WIP, untouched since 2019).
• #6457 finish COPY-LOB support (Closed at 80%). Still cited for missing overlay and offset errors.
• #6623 validate memptr bytes in COPY-LOB from a memptr to a CLOB or LONGCHAR (New).
• #11903 a temp-table field defined LIKE a CLOB takes its codepage from the persistent DMO (Review).
• #6720 lazy hydration (WIP), the nearest existing task for lazy loading.

No task covers a systematic comparison with OpenEdge, so there is no evidence of which of these limitations are real gaps and which are only untested.

Work¶

Write a test suite against OpenEdge for the areas below, then fix what it finds. Where a finding already has a task, fix it there and link it here.

1. Persistent lob fields. Create, update, read back and delete a BLOB and a CLOB field in a database table, in and out of a transaction, with UNDO, and across RELEASE and the end of the record scope. Include large values (several MB) and empty and unknown values.
2. Flushing. When a changed lob reaches the database, relative to the rest of the record: VALIDATE, RELEASE, the end of a transaction, a FIND on the same buffer, and BUFFER-COPY. What another session sees before and after commit.
3. Lazy loading. Whether reading a record fetches its lob fields, and what that costs for a query over many records that never touches the lob. Coordinate with #6720.
4. Temp-table lob fields, which the knowledge base calls weaker than persistent ones: the same cases, plus BUFFER-COPY and TEMP-TABLE / DATASET parameters passed by value and by reference.
5. CLOB codepages. A CLOB field with a declared codepage that differs from the session's; COPY-LOB ... CONVERT SOURCE CODEPAGE / TARGET CODEPAGE, in particular to and from UTF-8; NO-CONVERT; and FIX-CODEPAGE on a LONGCHAR that is then copied into a CLOB. Include non-ASCII text.
6. COPY-LOB to and from files: FROM FILE and TO FILE for both types, with APPEND, with a file that does not exist or cannot be written, and with binary content.
7. COPY-LOB between memory and lobs: MEMPTR to and from BLOB, LONGCHAR to and from CLOB, and MEMPTR to LONGCHAR with FOR length.
8. Errors. STARTING AT and FOR out of range, OVERLAY AT, an unknown or uninitialized source, and a target that cannot take the result. The error numbers, their order and the state left behind must match the 4GL.
9. Update BLOB_data_type and the gap marking for blob, clob and COPY-LOB with what remains.

Testcases¶

Each area above needs testcases with an OpenEdge baseline, run on PostgreSQL and SQL Server at least, and on H2 for temp-tables.