CLOB (IDS)

The CLOB data type stores any kind of text data in random-access chunks, called sbspaces. Text data can include text-formatting information, as long as this information is also textual, such as PostScript, Hypertext Markup Language (HTML), Standard Graphic Markup Language (SGML), or Extensible Markup Language (XML) data.

The term smart large object refers to CLOB and BLOB data types. The CLOB data type supports special operations for character strings that are inappropriate for BLOB values. A CLOB value can be up to 4 terabytes (4*2⁴⁰ bytes) in length.

Use the BLOB data type (see BLOB (IDS)) for random access to binary data. For general information about the CLOB and BLOB data types, see Smart Large Objects (IDS).

The following SQL functions can perform operations on a CLOB column:

• FILETOCLOB copies a file into a CLOB column.
• LOTOFILE copies a CLOB (or BLOB) value into a file.
• LOCOPY copies a CLOB (or BLOB) value to a new smart large object.
• ENCRYPT_DES or ENCRYPT_TDES creates an encrypted BLOB value from a plain-text CLOB argument.
• DECRYPT_BINAR or DECRYPT_CHAR returns an unencrypted BLOB value from an encrypted BLOB argument (that ENCRYPT_DES or ENCRYPT_TDES created from a plain-text CLOB value).

For more information on these SQL functions, see the IBM Informix: Guide to SQL Syntax.

No casts exist for CLOB data. Therefore, the database server cannot convert data of the CLOB type to any other data type, except by using these encryption and decryption functions to return a BLOB. Within SQL, you are limited to the equality ( = ) comparison operation for CLOB data. To perform additional operations, you must use one of the application programming interfaces from within your client application.

Multibyte Characters with CLOB

You can insert data into CLOB columns in the following ways:

• With the dbload or onload utilities
• With the LOAD statement (DB–Access)
• From CLOB (ifx_lo_t) host variables (ESQL/C).

For examples of CLOB types, see the IBM Informix: Guide to SQL Tutorial and the IBM Informix: Database Design and Implementation Guide.

With GLS, the following rules apply:

• Multibyte CLOB characters must be defined in the database locale.
• The CLOB data type is collated in code-set order.
• The database server handles code-set conversions for CLOB data.

For more information on database locales, collation order, and code-set conversion, see the IBM Informix: GLS User's Guide.