LOB host variables in ILE RPG applications that use SQL

Here are some examples of LOB host variables (CLOB, DBCLOB, BLOB) in ILE RPG applications.

CLOB example

  • The following declaration in free-form:
      DCL-S MYCLOB	SQLTYPE(CLOB:1000);

    results in the generation of the following structure:

      DCL-DS MYCLOB;                             
    MYCLOB_LEN UNS(10);                      
    MYCLOB_DATA CHAR(1000);
  END-DS MYCLOB;
  • The following declaration in fixed-form:
      D MYCLOB          S          SQLTYPE(CLOB:1000)

    results in the generation of the following structure:

      D MYCLOB          DS
  D MYCLOB_LEN                 10U
  D MYCLOB_DATA              1000A

DBCLOB example

  • The following declaration in free-form:
      DCL-S MYDBCLOB SQLTYPE(DBCLOB:400);

    results in the generation of the following structure:

      DCL-DS MYDBCLOB;                             
    MYDBCLOB_LEN UNS(10);                      
    MYDBCLOB_DATA GRAPH(400);
  END-DS MYDBCLOB;
  • The following declaration in fixed-form:
      D MYDBCLOB          S          SQLTYPE(DBCLOB:400)

    results in the generation of the following structure:

      D MYDBCLOB          DS
  D MYDBCLOB_LEN                 10U
  D MYDBCLOB_DATA               400G

BLOB example

  • The following declaration in free-form:
      DCL-S MYBLOB	SQLTYPE(BLOB:500);

    results in the generation of the following structure:

      DCL-DS MYBLOB;                             
    MYBLOB_LEN UNS(10);                      
    MYBLOB_DATA CHAR(500) CCSID(*HEX);
  END-DS MYBLOB;
  • The following declaration in fixed-form:
      D MYBLOB          S          SQLTYPE(BLOB:500)

    results in the generation of the following structure:

      D MYBLOB          DS
  D MYBLOB_LEN                 10U
  D MYBLOB_DATA               500A CCSID(*HEX)
Notes:
  1. For BLOB and CLOB host variables, the length must be in the range 1 to 16 773 100.
  2. For DBCLOB host variables, the length must be in the range 1 to 8 386 550.
  3. LOB host variables are allowed to be declared in host structures.
  4. LOB host variables are not allowed in host structure arrays. LOB locators should be used instead.
  5. LOB host variables declared in structure arrays cannot be used as standalone host variables.
  6. SQLTYPE, BLOB, CLOB, DBCLOB can be in mixed case.
  7. SQLTYPE must be between positions 44 to 80 for fixed-form declarations.
  8. When a LOB is declared as a stand-alone host variable, position 24 must contain the character 'S' and position 25 must be blank for fixed-form declarations.
  9. The stand-alone field indicator S in position 24 for fixed-form declarations should be omitted when a LOB is declared in a host structure.
  10. LOB host variables cannot be initialized.