Host Variables | Cursors |
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 Sybase, Informix 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.
Informix provides a number of 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.
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.
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
COBSQL - 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).
COBSQL - 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.
COBSQL - 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.
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
COBSQL - Oracle
With Oracle, it is best to define integer host variables as longint1
,
longint2
or as:
03 longint7 PIC S9(9) COMP-5.
These map to the Oracle data type NUMBER(38).
COBSQL - Sybase
With Sybase, all except longint3
should be accepted. You
can also use:
03 longint7 PIC S9(9) COMP-5.
These map to the Sybase data type INT.
COBSQL - 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.
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 supported by DB2 UDB V6.1 or later.
COBSQL
Oracle, Informix and Sybase do not 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.
For example:
03 char-field1 pic x(5). 03 char-field2 pic x(254).
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.
If you are using OpenESQL, the length field must be defined as COMP-5.
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.
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.
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).
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.
The 32-bit SQL floating-point data type, REAL, is declared in COBOL as usage COMP-1.
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
COBSQL - Oracle
Oracle supports the use of both COMP-1 and COMP-2 data items. These both
map to the Oracle data type NUMBER.
COBSQL - Sybase
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.
COBSQL - Informix
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.
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 - Oracle
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).
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 are 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
Because OpenESQL can access any relational database and each database has different ways of specifying dates and times, a standard way is provided of specifying dates and times in input host variables. If you are using this method, you must use the option DETECTDATE in the SQL directive when compiling your program.
For example:
$set sql(dbman=odbc, detectdate)
01 Hire-Date pic x(26).
move "{d'1965-11-02'} to Hire-Date exec sql insert into emp (HireDate) values (:Hire-Date) end-exec
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.
Informix expects dates to either be in Julian format, or of the format mm/dd/yyyy:
PIC S9(9) COMP
.
PIC X(10)
.
For more information on passing dates to Informix, refer to the INFORMIX-ESQL/COBOL Programmer's Manual.
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.
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.
Host Variables | Cursors |