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.
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.
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:
while character or text data types should be defined as:
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.
A tiny integer (TINYINT) is a 1-byte integer SQL data type that can be declared in COBOL as
PIC S9(4) COMP-5.
The tiny integer data type is not supported by DB2.
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.
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 currently supports signed small integers, but not unsigned small integers.
With Oracle, it is best to define the host variable as
shortint2 or as:
03 shortint7 PIC S9(4) COMP-5.
These map onto the Oracle data type NUMBER(38).
With Sybase all except
shortint3 should be accepted. You can
03 shortint7 PIC S9(4) COMP-5.
These map onto the Sybase data type SMALLINT.
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 currently supports signed integers, but not unsigned integers.
With Oracle, it is best to define integer host variables as
longint2 or as:
03 longint7 PIC S9(8) COMP-5.
These map to the Oracle data type NUMBER(38).
With Sybase, all except
longint3 should be accepted. You can
03 longint7 PIC S9(8) COMP-5.
These map to the Sybase data type INT.
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 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.
The big integer data type is not supported by DB2.
Neither Oracle nor Sybase support big integers.
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.
03 char-field1 pic x(5). 03 char-field2 pic x(254).
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.
OpenESQL and DB2
Variable-length character strings (VARCHAR) are SQL data types that can be declared in COBOL in one of two ways:
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.
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-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.
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).
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.
01 float1 usage comp-2.
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 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.
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.
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.
03 packed1 pic s9(8)v9(10) usage comp-3. 03 packed2 pic s9(8)v9(10) usage display.
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
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 DB2, the TIMESTAMP data type has a maximum length of 26 characters.
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:
Converts from Oracle's date format to a character string.
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.
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.
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.
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.
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.
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.
Copyright © 1998 Micro Focus Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.