RE: CLOB fields

+1 iACS Run SQL Scripts is what you want to use to view CLOB data on the IBM i

From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Daniel Gross
Sent: Friday, June 5, 2026 3:18 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: 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 C͏​​‌͏
[External email]<https://summary.us1.defend.egress.com/v3/summary?ref=email&crId=6a22784ef0758bc1f781b371&lang=en>
External email <https://summary.us1.defend.egress.com/v3/summary?ref=email&crId=6a22784ef0758bc1f781b371&lang=en>
[External email]<https://summary.us1.defend.egress.com/v3/summary?ref=email&crId=6a22784ef0758bc1f781b371&lang=en>


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://links.us1.defend.egress.com/Warning?crId=6a22784ef0758bc1f781b371&Domain=mbi-inc.com&Threat=eNpzrShJLcpLzAEADmkDRA%3D%3D&Lang=en&Base64Url=eNoNx0sKwzAMBcATPaurFgqlZ5E_xALFci0luX46u-kR099E13UlyXsqtlO14tQGCb3SMz2-YVPKp_ZTZHEcjtqK8pKxQS2jmwfO_zlrc8iAaMOaG3hOlcIhNhzROXB4g__0BuWjK3c%3D&@OriginalLink=www.ibm.com>

- https://www.itjungle.com/2016/12/13/fhg121316-story02/<https://links.us1.defend.egress.com/Warning?crId=6a22784ef0758bc1f781b371&Domain=mbi-inc.com&Threat=eNpzrShJLcpLzAEADmkDRA%3D%3D&Lang=en&Base64Url=eNoFwlEKgCAQBNAT5Tgr-NF1IrVIjVxZur28V1TfsQNm5i69Z8vP6Y5eIZ4RFDAglUxhYNyG9u_3ggXxoRDc&@OriginalLink=www.itjungle.com>

Am 05.06.2026 um 02:12 schrieb James H. H. Lampert via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx<mailto: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<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

To subscribe, unsubscribe, or change list options,

visit: https://lists.midrange.com/mailman/listinfo/midrange-l<https://links.us1.defend.egress.com/Warning?crId=6a22784ef0758bc1f781b371&Domain=mbi-inc.com&Threat=eNpzrShJLcpLzAEADmkDRA%3D%3D&Lang=en&Base64Url=eNo1yUkKACAIAMAXpfd-I62CWqT_Jwia68yI7RlR2MNBuR6y0aAsRSUWJXvF1hf-TXIBNA4UkQ%3D%3D&@OriginalLink=lists.midrange.com>

or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>

Before posting, please take a moment to review the archives

at https://archive.midrange.com/midrange-l<https://links.us1.defend.egress.com/Warning?crId=6a22784ef0758bc1f781b371&Domain=mbi-inc.com&Threat=eNpzrShJLcpLzAEADmkDRA%3D%3D&Lang=en&Base64Url=eNrLKCkpKLbS108sSs7ILEvVy81MKUrMS0_VS87P1YdxdHMAJSUOnw%3D%3D&@OriginalLink=archive.midrange.com>.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.

--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

To subscribe, unsubscribe, or change list options,

visit: https://lists.midrange.com/mailman/listinfo/midrange-l<https://links.us1.defend.egress.com/Warning?crId=6a22784ef0758bc1f781b371&Domain=mbi-inc.com&Threat=eNpzrShJLcpLzAEADmkDRA%3D%3D&Lang=en&Base64Url=eNo1yUkKACAIAMAXpfd-I62CWqT_Jwia68yI7RlR2MNBuR6y0aAsRSUWJXvF1hf-TXIBNA4UkQ%3D%3D&@OriginalLink=lists.midrange.com>

or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>

Before posting, please take a moment to review the archives

at https://archive.midrange.com/midrange-l<https://links.us1.defend.egress.com/Warning?crId=6a22784ef0758bc1f781b371&Domain=mbi-inc.com&Threat=eNpzrShJLcpLzAEADmkDRA%3D%3D&Lang=en&Base64Url=eNrLKCkpKLbS108sSs7ILEvVy81MKUrMS0_VS87P1YdxdHMAJSUOnw%3D%3D&@OriginalLink=archive.midrange.com>.



Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.