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 <[email protected]>:
> 
> 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: [email protected]
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: [email protected]
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
> 
> Please contact [email protected] for any subscription related questions.
> 
-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: [email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact [email protected] for any subscription related questions.