Examples of declaring file reference variables

C Example: Consider the following C declaration:

EXEC SQL BEGIN DECLARE SECTION
  SQL TYPE IS CLOB_FILE hv_text_file;
  CHAR hv_thesis_title[64];
EXEC SQL END DECLARE SECTION

That declaration results in the following Db2-generated construct:

EXEC SQL BEGIN DECLARE SECTION
    /* SQL TYPE IS CLOB_FILE hv_text_file; */
  struct {
     unsigned long name_length;  // File name length
     unsigned long data_length;  // Data length
     unsigned long file_options; // File options
     char name [255];            // File name
         } hv_text_file;
  char hv_thesis_title[64]

With the Db2-generated construct, you can use the following code to select from a CLOB column in the database into a new file that is referenced by :hv_text_file. The file name must be an absolute path.

strcopy(hv_text_file.name, "/u/gainer/papers/sigmod.94");
        hv_text_file.name_length = strlen("/u/gainer/papers/sigmod.94");
        hv_text_file.file_options = SQL_FILE_CREATE;

EXEC SQL SELECT CONTENT INTO :hv_text_file FROM PAPERS
           WHERE TITLE = 'The Relational Theory Behind Juggling';

Similarly, you can use the following code to insert the data from a file that is referenced by :hv_text_file into a CLOB column. The file name must be an absolute path.

strcopy(hv_text_file.name, "/u/gainer/patents/chips.13");
        hv_text_file.name_length = strlen("/u/gainer/patents/chips.13");
        hv_text_file.file_options = SQL_FILE_READ;
strcopy(:hv_patent_title, "A Method for Pipelining Chip Consumption");

EXEC SQL INSERT INTO PATENTS(TITLE, TEXT)
                VALUES(:hv_patent_title, :hv_text_file);

COBOL Example: Consider the following COBOL declaration:

01 MY-FILE SQL TYPE IS BLOB-FILE

That declaration results in the following Db2-generated construct:

01 MY-FILE.
   49 MY-FILE-NAME-LENGTH PIC S9(9) COMP-5.
   49 MY-FILE-DATA-LENGTH PIC S9(9) COMP-5.
   49 MY-FILE-FILE-OPTION PIC S9(9) COMP-5.
   49 MY-FILE-NAME PIC(255);

PL/I Example: Consider the following PL/I declaration:

DCL MY_FILE SQL TYPE IS CLOB_FILE

That declaration results in the following Db2-generated construct:

DCL 1 MY_FILE,
   3 MY_FILE_NAME_LENGTH BINARY FIXED (31) UNALIGNED,
   3 MY_FILE_DATA_LENGTH BINARY FIXED (31) UNALIGNED,
   3 MY_FILE_FILE_OPTIONS BINARY FIXED (31) UNALIGNED,
   3 MY_FILE_NAME CHAR(255);