PreviousHost Variables CursorsNext

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.

3.1.1 COBSQL Preprocessor

When using Sybase, Informix or Oracle with COBSQL, the database engine is 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.

3.1.1.1 Oracle

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.

3.1.1.2 Sybase

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.1.1.3 Informix

Informix provides a number system routines that you can call to manipulate different data types. For more information about these routines, please refer to the Programming with INFORMIX-ESQL/COBOL manual.

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.


Note: DB2 does not support the tiny integer data type.


3.2.1.1 COBSQL Preprocessor

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.

3.2.2.1 OpenESQL Preprocessor

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

3.2.2.2 COBSQL Preprocessor - Oracle

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

3.2.2.3 COBSQL Preprocessor - Sybase

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.2.4 COBSQL Preprocessor - Informix

With Informix, 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 Informix 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.

3.2.3.1 OpenESQL Preprocessor

OpenESQL currently supports signed integers, but not unsigned integers.

3.2.3.2 COBSQL Preprocessor - Oracle

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

3.2.3.3 COBSQL Preprocessor - Sybase

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.3.4 COBSQL Preprocessor - Informix

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

03 longint7     PIC S9(9) COMP-5.

These map to the Informix 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.


Notes:

DB2 Preprocessor
DB2 does not support the big integer data type.

COBSQL Preprocessor
Oracle, Sybase and Informix do not support big integers.

OpenESQL Preprocessor
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. However, 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; therefore, ensure that your code checks for data truncation.


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

Note:

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


3.3.2 Variable-length Character Strings

3.3.2.1 OpenESQL and DB2 Preprocessors

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

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.

3.3.2.2 COBSQL Preprocessor

3.3.2.2.1 Oracle

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.

3.3.2.2.2 Sybase

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.3.2.3 COBSQL - Informix

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.

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.

3.4.1 OpenESQL Preprocessor

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.

3.4.2 DB2 Preprocessor

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

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

3.4.3 COBSQL Preprocessor

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

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.

Informix does not support either COMP-1 or COMP-2 data items. Informix only supports fixed numeric data items in COBOL, that is PIC S9(m)V9(n). Informix will convert FLOAT and SMALLFLOAT SQL columns into this format.

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.

3.5.1 COBSQL Preprocessor

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 Informix, they map to either DECIMAL(p,s) or to MONEY(p,s).

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

3.6.1 DB2 Preprocessor

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

3.6.2 COBSQL Preprocessor

3.6.2.1 Oracle

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:

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.

3.6.2.2 Sybase

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.6.2.3 Informix

Informix expects dates to either be in Julian format, or of the format mm/dd/yyyy:

For more information on passing dates to Informix, refer to the INFORMIX-ESQL/COBOL Programmer's Manual.

3.7 Binary Data Types

3.7.1 OpenESQL Preprocessor

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.

3.7.2 DB2 Preprocessor

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.

3.7.3 COBSQL Preprocessor

3.7.3.1 Oracle

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.

3.7.3.2 Sybase

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.


3.7.3.3 Informix

Informix supports two types of Binary data items, TEXT and BYTE. These data types do not store the actual data; in fact, they are file names. Therefore, in COBOL, the corresponding item is a PIC X(n).

For more information on TEXT and BYTE data items, refer to the Informix Guide to SQL.


Copyright © 2000 MERANT International Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.

PreviousHost Variables CursorsNext