Variable-length Character Strings

Variable-length character strings (VARCHAR) are SQL data types that can be declared in COBOL in a number of ways:

As fixed-length character strings.
As group items containing only two elementary items, both of which must have a level number of 49. The first item is a 2-byte field declared with usage COMP or COMP-5 that represents the effective length of the character string. The second item is a PIC X(n) data type, where n is an integer, and holds the actual data.
For example:
```
03 varchar1.
   49 varchar1-len        pic 9(4) comp.
   49 varchar1-data       pic x(200).
03 Longvarchar1.
   49 Longvarchar1-len    pic 9(4) comp-5.
   49 Longvarchar1-data   pic x(30000).
```
In your SQL statements you must reference the group name.

Using SQL TYPES.

For example:

 
03 fixchar1               pic x(200).
03 varchar1.
   49 varchar1-len        pic s9(4) comp.
   49 varchar1-data       pic x(200).
03 longvarchar1.
   49 longvarchar1-len    pic s9(4) comp-5.
   49 longvarchar1-data   pic x(30000).
03 varchar2     SQL TYPE CHAR-VARYING(200).
03 longvarchar2 SQL TYPE LONG-VARCHAR(50000).
03 clob1        SQL TYPE CLOB(32K).
03 varying1     PIC X(20) VARYING.

In your SQL statements you must reference the group name.

If the data being copied to a SQL CHAR, VARCHAR or LONG VARCHAR data type is longer than the defined length, then the data is truncated and the SQLWARN1 flag in the SQLCA data structure is set. If the data is smaller than the defined length, a receiving CHAR data type may be padded with blanks.

OpenESQL

OpenESQL accepts all formats as host variables. The maximum number of bytes you can use with OpenESQL host variables is 65000. For more information, see the OpenESQL SQL TYPEs in this chapter.

DB2 LUW

DB2 only accepts fixchar1, varchar1, longvarchar1, and clob1 formats as host variables.

COBSQL - Oracle

Oracle only accepts format varying1 as a host variable. The host variable is defined using the Oracle keyword VARYING (see example above).

 01 USERNAME
    02 USERNAME-LEN     PIC S9(4) COMP-5.
    02 USERNAME-ARR     PIC X(20).

For Oracle, the host variable is defined using the Oracle keyword VARYING. An example of its use is as follows:

 EXEC SQL
     BEGIN DECLARE SECTION
 END-EXEC.
 01 USERNAME     PIC X(20) VARYING.
 EXEC SQL
     END DECLARE SECTION
 END-EXEC.

Oracle will then expand the data item USERNAME into the following group item:

 01 USERNAME
    02 USERNAME-LEN     PIC S9(4) COMP-5.
    02 USERNAME-ARR     PIC X(20).

Within the COBOL code, references must be made to either USERNAME-LEN or USERNAME-ARR but within SQL statements the group name USERNAME must be used. For example:

     move "SCOTT" to USERNAME-ARR.
     move 5 to USERNAME-LEN.
     exec sql
         connect :USERNAME identified by :pwd
          using :db-alias
     end-exec.

This maps to the Oracle data type VARCHAR(n) or VARCHAR2(n). For very large character items, Oracle provides the data type LONG.

COBSQL - Sybase

Sybase only accepts fixchar1 format because the Sybase precompiler does not support the use of group items to handle VARCHAR SQL data types.

For Sybase, the host variable must be defined with a PIC X(n) picture clause as the Sybase precompiler does not support the use of group items to handle VARCHAR SQL data types.

These map to the Sybase data type of VARCHAR(n).

COBSQL - Informix

Informix only accepts fixchar1 format because the Informix precompiler does not support the use of group items to handle VARCHAR SQL data types.

For Informix, the host variable must be defined with a PIC X(n) picture clause as the Informix precompiler does not support the use of group items to handle VARCHAR SQL data types.

These map to the Informix data type of VARCHAR(n). The maximum length of a VARCHAR field depends on the version of Informix being used. For more information on VARCHAR data items, please refer to the Informix Guide to SQL manual.

Parent topic: Character Data Types