SQL Reference

References to BLOB, CLOB, and DBCLOB File Reference Variables

BLOB, CLOB, and DBCLOB file reference variables are used for direct file input and output for LOBs, and can be defined in all host languages. Since these are not native data types, SQL extensions are used and the precompilers generate the host language constructs necessary to represent each variable. In the case of REXX, LOBs are mapped to strings.

A file reference variable represents (rather than contains) the file, just as a LOB locator represents, rather than contains, the LOB bytes. Database queries, updates and inserts may use file reference variables to store or to retrieve single column values.

A file reference variable has the following properties:

Data Type

BLOB, CLOB, or DBCLOB. This property is specified when the variable is declared.

Direction

This must be specified by the application program at run time (as part of the File Options value -see below). The direction is one of:

• Input (used as a source of data on an EXECUTE statement, an OPEN statement, an UPDATE statement, an INSERT statement, or a DELETE statement).
• Output (used as the target of data on a FETCH statement or a SELECT INTO statement).

File name

This must be specified by the application program at run time. It is one of:

• The complete path name of the file (which is advised).
• A relative file name. If a relative file name is provided, it is appended to the current path of the client process.

Within an application, a file should only be referenced in one file reference variable.

File Name Length

This must be specified by the application program at run time. It is the length of the file name (in bytes).

File Options

An application must assign one of a number of options to a file reference variable before it makes use of that variable. Options are set by an INTEGER value in a field in the file reference variable structure. One of the following values must be specified for each file reference variable:

• Input (from client to server)

  SQL_FILE_READ (25)

  This is a regular file that can be opened, read and closed.

• Output (from server to client)

  SQL_FILE_CREATE (26)

  Create a new file. If the file already exists, it is an error.

  SQL_FILE_OVERWRITE (Overwrite) (27)

  If an existing file with the specified name exists, it is overwritten; otherwise a new file is created.

  SQL_FILE_APPEND (28)

  If an existing file with the specified name exists, the output is appended to it; otherwise a new file is created.

Data Length

This is unused on input. On output, the implementation sets the data length to the length of the new data written to the file. The length is in bytes.

As with all other host variables, a file reference variable may have an associated indicator variable.

Example of an Output File Reference Variable (in C)

Given a declare section is coded as:

   EXEC SQL BEGIN DECLARE SECTION
      SQL TYPE IS CLOB_FILE  hv_text_file;
      char  hv_patent_title[64];
   EXEC SQL END DECLARE SECTION

Following preprocessing this would be:

   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_patent_title[64];
   EXEC SQL END DECLARE SECTION

Then, the following code can be used to select from a CLOB column in the database into a new file referenced by :hv_text_file.

   strcpy(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';

Example of an Input File Reference Variable (in C)

Given the same declare section as above, the following code can be used to insert the data from a regular file referenced by :hv_text_file into a CLOB column.

   strcpy(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:
   strcpy(:hv_patent_title, "A Method for Pipelining Chip Consumption");
 
   EXEC SQL INSERT INTO patents( title, text )
            VALUES(:hv_patent_title, :hv_text_file);

(25) SQL-FILE-READ in COBOL, sql_file_read in FORTRAN, READ in REXX.

(26) SQL-FILE-CREATE in COBOL, sql_file_create in FORTRAN, CREATE in REXX.

(27) SQL-FILE-OVERWRITE in COBOL, sql_file_overwrite in FORTRAN, OVERWRITE in REXX.

(28) SQL-FILE-APPEND in COBOL, sql_file_append in FORTRAN, APPEND in REXX.

[ DB2 List of Books | Search the DB2 Books ]