Host Variables

Cursors

Chapter 3: Data Types

SQL data types differ from those used in COBOL.

SQL has a standard set of data types, but the exact implementation of these varies between databases and many databases do not implement the full set.

3.1 Converting Data Types

Within your COBOL program a host variable can act as a COBOL program variable and as a SQL database variable and so the preprocessor must convert, or map, COBOL data types to the appropriate SQL data type. This means that you need to declare your host variables with the correct COBOL picture clause so that the preprocessor maps it to the correct SQL data type. To do this, you need to know the SQL data types used by the data source to which you are going to connect.

The following sections describe the different SQL data types and how to declare host variables that map directly onto them.

COBSQL
When using either Sybase or Oracle with COBSQL, the database engine will be able to perform some sort of conversion to change the data from a COBOL data type to a database data type. A general rule of thumb is that for numeric or integer data types host variables should be defined as:

PIC S9(..)..COMP..

while character or text data types should be defined as:

PIC X(...).

Both Oracle and Sybase allow you to define the database data type for a given host variable. This can be useful for the more complex data types.

For Oracle, this is done as follows:

EXEC SQL
BEGIN DECLARE SECTION
END-EXEC.
*
* Define data item as Oracle data type DISPLAY
*
01 emp-comm pic s9(6)v99 DISPLAY SIGN LEADING SEPARATE
*   
EXEC SQL
   VAR emp-comm IS DISPLAY(8,2)
END-EXEC.
EXEC SQL
   END DECLARE SECTION
END-EXEC.

For Sybase, this is done as follows:

EXEC SQL
   BEGIN DECLARE SECTION
END-EXEC.
*
* Define item as Sybase specific data type
*
01 money-item CS-MONEY.
*
EXEC SQL
   END DECLARE SECTION
END-EXEC.

For more information about defining the database type of a host variable, refer to the COBOL precompiler manual supplied by your database vendor.

3.2 Integer Data Types

3.2.1 Tiny Integer

A tiny integer (TINYINT) is a 1-byte integer SQL data type that can be declared in COBOL as

PIC S9(4) COMP-5.

DB2
The tiny integer data type is not supported by DB2.

COBSQL
Sybase supports the use of tiny integer host variables. The definition for Sybase is:

03 tinyint1     PIC S9(2) COMP-5.
03 tinyint2     PIC S9(2) COMP.
03 tinyint3     PIC S9(2) BINARY.

These map onto the Sybase data type TINYINT.

3.2.2 Small Integer

A small integer (SMALLINT) is a 2-byte integer SQL data type that can be declared in COBOL with usage BINARY, COMP, COMP-X, COMP-5 or COMP-4.

For example, all of the following definitions are valid for host variables to map directly onto the SMALLINT data type.

03 shortint1      PIC S9(4)  COMP.
03 shortint2      PIC S9(4)  BINARY.
03 shortint3      PIC X(2)   COMP-5.
03 shortint4      PIC S9(4)  COMP-4.
03 shortint5      PIC 9(4)   USAGE DISPLAY.
03 shortint6      PIC S9(4)  USAGE DISPLAY.

OpenESQL
OpenESQL currently supports signed small integers, but not unsigned small integers.

COBSQL
With Oracle, it is best to define the host variable as shortint1 or shortint2 or as:

03 shortint7     PIC S9(4) COMP-5.

These map onto the Oracle data type NUMBER(38).

COBSQL
With Sybase all except shortint3 should be accepted. You can also use:

03 shortint7     PIC S9(4) COMP-5.

These map onto the Sybase data type SMALLINT.

3.2.3 Integer

An integer (INT) is a 4-byte integer SQL data type that can be declared in COBOL with usage BINARY, COMP, COMP-X, COMP-5 or COMP-4.

All of the following definitions are valid for host variables to map directly onto the INT data type.

03 longint1      PIC S9(9)  COMP.
03 longint2      PIC S9(9)  COMP-5.
03 longint3      PIC X(4)   COMP-5.
03 longint4      PIC X(4)   COMP-X.
03 longint5      PIC 9(9)   USAGE DISPLAY.
03 longint6      PIC S9(9)  USAGE DISPLAY.

OpenESQL
OpenESQL currently supports signed integers, but not unsigned integers.

COBSQL
With Oracle, it is best to define integer host variables as longint1, longint2 or as:

03 longint7     PIC S9(8) COMP-5.

These map to the Oracle data type NUMBER(38).

COBSQL
With Sybase, all except longint3 should be accepted. You can also use:

03 longint7     PIC S9(8) COMP-5.

These map to the Sybase data type INT.

3.2.4 Big Integer

A big integer (BIGINT) is an 8-byte integer SQL data type that can be declared in COBOL as:

PIC S9(18) COMP-3.

OpenESQL
OpenESQL supports a maximum size of S9(18) for COBOL data items used as host variables to hold values mapped from the SQL data type BIGINT. You should be aware, however, that a BIGINT data type can hold a value that is larger than the maximum value that can be held in a PIC S9(18) data item and ensure that your code checks for data truncation.

DB2
The big integer data type is not supported by DB2.

COBSQL
Neither Oracle nor Sybase support big integers.

3.3 Character Data Types

3.3.1 Fixed-length Character Strings

Fixed-length character strings (CHAR) are SQL data types with a driver defined maximum length. They are declared in COBOL as PIC X(n) where n is an integer between 1 and the maximum length.

For example:

03 char-field1      pic x(5).
03 char-field2      pic x(254).

COBSQL
This maps to the Oracle data type CHAR(n) and to the Sybase data type CHAR(n). For both Oracle and Sybase the largest supported fixed length character string is 255 bytes.

3.3.2 Variable-length Character Strings

OpenESQL and DB2
Variable-length character strings (VARCHAR) are SQL data types that can be declared in COBOL in one of two ways:

As fixed-length character strings (PIC X(n)).
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-5.
   49 varchar1-data       pic x(200).
03 Longvarchar1.
   49 Longvarchar1-len    pic 9(4) comp.
   49 Longvarchar1-data   pic x(30000).

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.

COBSQL
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 statments 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
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).

3.4 Approximate Numeric Data Types

The 32-bit SQL floating-point data type, REAL, is declared in COBOL as usage COMP-2.

The 64-bit SQL floating-point data types, FLOAT and DOUBLE, are declared in COBOL as usage COMP-2.

For example:

01 float1      usage comp-2.

OpenESQL
Both 32-bit and 64-bit floating-point data types are mapped to COMP-2 COBOL data items because single-precision floating point is not supported in embedded SQL by OpenESQL.

DB2
DB2 Universal Database supports single-precision floating point (REAL) as COMP-1 and double-precision floating point (FLOAT or DOUBLE) as COMP-2.

DB2
DB2 Version 2.1 only supports double-precision floating point (FLOAT or DOUBLE) as COMP-2.

COBSQL
Oracle supports the use of both COMP-1 and COMP-2 data items. These both map to the Oracle data type NUMBER.

COBSQL
Sybase supports the use of both COMP-1 and COMP-2 data items. COMP-1 data items map to the Sybase data type REAL. COMP-2 data items map to the Sybase data type FLOAT.

3.5 Exact Numeric Data Types

The exact numeric data types DECIMAL and NUMERIC can hold values up to a driver-specified precision and scale.

They are declared in COBOL as COMP-3, PACKED-DECIMAL or as NUMERIC USAGE DISPLAY.

For example:

03 packed1      pic s9(8)v9(10) usage comp-3.
03 packed2      pic s9(8)v9(10) usage display.

COBSQL
For Oracle, these map to the data type NUMBER(p,s). For Sybase, they map to either NUMBER(p,s) or to DECIMAL(p,s)

For more information on the difference between the NUMERIC and DECIMAL data types, refer to the chapter on Using and Creating Datatypes in the Sybase Transact-SQL Users Guide

3.6 Date and Time Data Types

COBOL does not have date/time data types so SQL date/time columns are converted to character representations.

If a COBOL output host variable is defined as PIC X(n), for a SQL timestamp value, where n is greater than or equal to 19, the date and time will be specified in the format yyyy-mm-dd hh:mm:ss.ff..., where the number of fractional digits is driver defined.

For example:

1994-05-24 12:34:00.000

DB2
For DB2, the TIMESTAMP data type has a maximum length of 26 characters.

COBSQL
Oracle date items have a unique data definition and Oracle provides functions to convert date, time and datetime fields when used within a COBOL program. These functions are:

TO_CHAR
Converts from Oracle's date format to a character string.
TO_DATE
Converts a character string into an Oracle date.

Both functions take an item to be converted followed by the date, time or datetime mask to be applied to that data item. An example of this is as follows:

exec sql
   select ename, TO_CHAR(hiredate, 'DD-MM-YYYY')
      from emp
      into :ename, :hiredate
      where empno = :empno
end-exec.

exec sql
   insert into emp (ename, TO_DATE(hiredate, 'DD-MM-YYYY'))
          values   (:ename, :hiredate)
end-exec.

This maps to the Oracle data type of DATE. For more information about the DATE data type, refer to the Oracle SQL Language Reference Manual. More information about the use of functions within Oracle SQL statements can be found in this manual.

COBSQL
Sybase provides a function, called convert, to change the format of a data type. Using the Oracle examples above, the SQL syntax would be:

exec sql
   select ename, convert(varchar(12) hiredate, 105)
      from emp
      into :ename, :hiredate
      where empno = :empno
end-exec.

exec sql
   insert into emp (ename, hiredate)
          values   (:ename, convert(datetime :hiredate, 105)
end-exec.

This maps to the Sybase data type of either SMALLDATETIME or DATETIME. For more information on the difference between the SMALLDATETIME and the DATETIME data types, refer to the chapter Using and Creating Datatypes in the Sybase Transact-SQL User's Guide.

For more information on the Sybase convert function, refer to the Sybase SQL Server Reference Manual: Volume 1 Commands, Functions and Topics.

3.7 Binary Data Types

OpenESQL
SQL BINARY, VARBINARY and IMAGE data are represented in COBOL as PIC X (n) fields. No data conversion is performed. When data is fetched from the database, if the COBOL field is smaller than the amount of data, the data is truncated and the SQLWARN1 field in the SQLCA data structure is set to "W". If the COBOL field is larger than the amount of data, the field is padded with null (x"00") bytes. To insert data into BINARY, VARBINARY or LONG VARBINARY columns, you must use dynamic SQL statements.

DB2
With DB2 use CHAR FOR BIT DATA to represent BINARY, VARCHAR(n) FOR BIT DATA to represent VARBINARY and LONG VARCHAR FOR BIT DATA to represent LONG VARBINARY. If you use the IBM ODBC driver, BINARY, VARBINARY and LONG VARBINARY are the data types returned instead of the IBM equivalent. The IMAGE data type can be represented by BLOB. DB2 uses LOBs (Character Large Object, Binary Large Object or Graphical Large Object to define very large columns (2 Gigabytes maximum). You can use static SQL with these data types.

COBSQL
Oracle provides support for binary data. The difference between binary and character data is that Oracle will do codeset conversions on character data, but will leave binary data untouched.

The two Oracle data types are RAW and LONG RAW. There are some restrictions on the use of RAW and LONG RAW - consult your Oracle documentation for further details.

COBSQL
Sybase provides three binary data types: BINARY, VARBINARY and IMAGE. IMAGE is a complex data type and as such, host variables can be defined as CS-IMAGE, for example:

EXEC SQL
   BEGIN DECLARE SECTION
END-EXEC.
*
* Define item as Sybase specific data type.
*
01 image-item     CS-IMAGE.
*
EXEC SQL
   END DECLARE SECTION
END-EXEC.

Note: For more information on using the Sybase data types of BINARY, VARBINARY and IMAGE, please refer to the chapter Using and Creating Datatypes in the Sybase Transact-SQL User's Guide.

Host Variables

Cursors