Representing Data

When you access data with SQL statements in COBOL programs, you must provide corresponding data definitions in your COBOL source code. This requirement is usually accomplished by stating the data definitions in a copybook and including that copybook in your COBOL source code.

The copybook EMPREC contains a COBOL representation of the column names and data types for the EMPLOYEE sample table. The Declaration Generator generates copybooks from tables, automatically creating a COBOL data definition for each table columnYou can modify the generated copybook file as needed. See the SQL Option Declaration Generator User's Guide for more information about using the SQL Option Declaration Generator.

The following guidelines are offered to assist you in working with host variables and data declarations.

• For data declarations of host variables use level number 01 to 77.
• A varying-length character string data item must have group level 01. The group contains two, level 49 elementary items consisting of:
  • Length Item.

    The first elementary item must be a 2-byte integer variable; for example, PICTURE S9(4) COMP. It represents the length of the character string.

  • Value Item.

    The second elementary item must have the same description as a fixed-length character string; for example, PICTURE X(80), where 80 is the maximum length of the string. This item is used to contain the value of the character string. If you use the host variable to insert a character string into a table, the database manager inserts only as many characters as are indicated by the length item. Also, when data is placed into the field, only the number of characters defined in the length item is returned. This can cause previous information stored in this field not to be cleared.

  The data item and elementary items can have any acceptable COBOL name. However, in SQL statements, refer to this host variable by its group name.

• COBOL host variables used in SQL statements must be of a type compatible with the database columns with which they are to be used.
  • Numeric data types are compatible with each other. A SMALLINT, INTEGER, DECIMAL, or FLOAT column is compatible with a COBOL host variable of COMP, COMP-2, COMP-3, COMP-4, or COMP-5.
  • Character data types such as CHAR and VARCHAR are compatible with each other. The database manager automatically converts a fixed-length character string to a varying-length string, or vice versa, when necessary.
• Be careful of overflow. For example, if you retrieve an INTEGER column value into a PICTURE S9(4) host variable and the column value is larger than 32767 or smaller than -32768, you will get an overflow error.
• Be careful of truncation. For example, if you retrieve an 80-character CHAR column value into a PICTURE X(70) host variable, the rightmost 10 characters of the retrieved string will not be included.

  Retrieving a floating-point or decimal column value into a COMP host variable removes any fractional value.

• The COBOL declarations for SMALLINT and INTEGER data types are expressed as a number of decimal digits. The database manager uses the full size of the binary integers, and may place larger values than would be allowed in the specified number of digits in the COBOL declaration. This situation can cause data truncation. Ensure that the size of any numbers in your application is within the allowable number of digits.
• The SQL Option preprocessor also supports using host variables defined with the SQL TYPE IS declaration. The actual data definition for the host variable is then determined by the preprocessor. For example, a host variable for a RESULT SET LOCATOR could be defined as:

  01 my-locator usage SQL TYPE IS RESULT-SET-LOCATOR varying.