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). 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 ²⁷

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

• Output (from server to client)

  SQL_FILE_CREATE ²⁸

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

  SQL_FILE_OVERWRITE (Overwrite) ²⁹

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

  SQL_FILE_APPEND ³⁰

  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);
  

References to Structured Type Host Variables

Structured type variables can be defined in all host languages except FORTRAN, REXX, and Java. Since these are not native data types, SQL extensions are used and the precompilers generate the host language constructs necessary to represent each variable.

As with all other host variables, a structured type variable may have an associated indicator variable. Indicator variables for structured type host variables behave in the same way as indicator variables for other data types. When a null value is returned from the database, the indicator variable is set and the structured type host variable is unchanged.

The actual host variable for a structured type is defined as a built-in data type. The built-in data type associated with the structured type must be assignable:

• from the result of the FROM SQL transform function for the structured type as defined by the specified TRANSFORM GROUP option of the precompile command; and
• to the parameter of the TO SQL transform function for the structured type as defined by the specified TRANSFORM GROUP option of the precompile command.

If using a parameter marker instead of a host variable, the appropriate parameter type characteristics must be specified in the SQLDA. This requires a "doubled" set of SQLVAR structures in the SQLDA, and the SQLDATATYPE_NAME field of the secondary SQLVAR must be filled with the schema and type name of the structured type. If the schema is omitted in the SQLDA structure, an error results (SQLSTATE 07002). For additional information on this topic, see Appendix C, SQL Descriptor Area (SQLDA).

Example

Define the host variables hv_poly and hv_point (of type POLYGON, using built-in type BLOB(1048576)) in a C program.

   EXEC SQL BEGIN DECLARE SECTION;
         static SQL 
            TYPE IS POLYGON AS BLOB(1M)
            hv_poly, hv_point;
   EXEC SQL END DECLARE SECTION;

²⁷

SQL-FILE-READ in COBOL, sql_file_read in FORTRAN, READ in REXX.

²⁸

SQL-FILE-CREATE in COBOL, sql_file_create in FORTRAN, CREATE in REXX.

²⁹

SQL-FILE-OVERWRITE in COBOL, sql_file_overwrite in FORTRAN, OVERWRITE in REXX.

³⁰

SQL-FILE-APPEND in COBOL, sql_file_append in FORTRAN, APPEND in REXX.