Re: CLOB fields

Hi James,

CLOB fields in SQL are a thing.

When you have set the value with your INSERT VALUES the statement implicitly CASTed your CHAR literal into a CLOB - and that works quite fine.

IBM i Access Client Solutions Run SQL Scripts shows CLOBs automatically, and shows their contents. STRSQL does not - and it's also somehow archaic. Other tools are also not always perfect in this way.

If you want the content in RPG your best bet is to use a variable of SQLTYPE(CLOB:<size>). This results in a special data structure with a _LEN and _DATA subfield, that you can use from RPG. And the contents of your CLOB are fully copied into memory.

But that can be a problem with large CLOBs (> 16MB) - there it's better to use SQLTYPE(BLOB_LOCATOR), but then you have to SET and MODIFY the CLOB only with embedded SQL, as there is no "copy" in RPG. And it works only when COMMIT is not *NONE/NC.

As long as you are unter 16MB size, you can also overlay your SQLTYPE(CLOB:<size>) variable with a RPG variable of type VARCHAR(<size>:4) - both share the same value and memory then, and the VARCHAR has the same structure as the SQLTYPE variable (length prefix of 4 bytes followed by the content) - but be sure to have the correct CCSID.

HTH
Daniel

P.S.: Read this for more information:
- https://www.ibm.com/docs/en/i/7.6.0?topic=dhviiratus-declaring-lob-host-variables-in-ile-rpg-applications-that-use-sql
- https://www.itjungle.com/2016/12/13/fhg121316-story02/

Am 05.06.2026 um 02:12 schrieb James H. H. Lampert via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>:

Can anybody tell me how to put a value into a CLOB field, and read the value back from it?

Obviously accessing CLOB fields is an SQL-only thing, but if I use a STRSQL session, all a SELECT statement shows for the field is "*POINTER," and if I use Squirrel SQL through a JDBC connection, all that SELECT statement shows for the field is "<Clob>."

When I created a record in my file (currently the *only* record in it), the VALUES clause for the INSERT statement had a character string in single quotes for the CLOB field.

--
JHHL
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.