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 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.
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.
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.
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 book Programming with INFORMIX-ESQL/COBOL.
A tiny integer (TINYINT) is a 1-byte integer SQL data type that can be declared in COBOL as
PIC S9(2) COMP-5. PIC S9(4) COMP-5.
DB2 LUW
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. 03 tinyint4 PIC S9(4) COMP-5.
With Sybase, all formats are acceptable. These map onto the Sybase data type TINYINT.
OpenESQL
Only tinyint4 is accepted.
However, the tiny integer data type is supported when compiling with the SQL(DBMAN=ADO) directive.
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. 03 shortint7 PIC S9(4) COMP-5.
OpenESQL
COBSQL - Oracle
With Oracle, it is best to define the host variable as shortint1, shortint2, or shortint7.
These map onto the Oracle data type NUMBER(38).
COBSQL - Sybase
With Sybase, all except shortint3 should be accepted.
These map onto the Sybase data type SMALLINT.
COBSQL - Informix
With Informix, it is best to define the host variable as shortint1, shortint2, or shortint7.
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. 03 longint7 PIC S9(9) COMP-5.
OpenESQL
COBSQL - Oracle
With Oracle, it is best to define integer host variables as longint1, longint2, or longint7.
These map to the Oracle data type NUMBER(38).
COBSQL - Sybase
With Sybase, all except longint3 should be accepted.
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.
All of the following definitions are valid for host variables to map directly onto the BIGINT data type.
03 bigint1 PIC S9(18) COMP-5. 03 bigint2 PIC S9(18) COMP-3. 03 bigint3 PIC X(8) COMP-5. 03 bigint4 PIC X(8) COMP-X.
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 LUW
Bigint1 or bigint2 are the only formats supported by DB2
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.
DB2 LUW
This maps to the DB2 data type CHAR. The largest supported fixed length character string is 254 bytes. If you need a character string greater than 254 bytes in length, use a VARCHAR field
Variable-length character strings (VARCHAR) are SQL data types that can be declared in COBOL in a number of ways:
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.
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).
Oracle expands 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).
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.
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.
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.
We support Unicode data types in OpenESQL using PIC N(n) fields. For more information, see the section Unicode Support in OpenESQL in the chapter OpenESQL later in this book, and the topic SQL/COBOL Data Type Mappings.
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. 01 real1 usage comp-1.
DB2 LUW
DB2 supports single-precision floating point (REAL) as COMP-1 and double-precision floating point (FLOAT or DOUBLE) as COMP-2.
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 SIGNED 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.
OpenESQL
OpenESQL supports using both formats as host variables.
DB2 LUW
DB2 supports using both formats as host variables.
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 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 for an SQL timestamp value, 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
could use one of the following types of host variables depending upon the precompiler used:
03 timestamp1 pic x(29). 03 timestamp2 SQL TYPE IS TIMESTAMP. 03 timestamp3 SQL TYPE IS TIMESTAMP-RECORD.
OpenESQL supports using all of the formats for host variables with some qualifications. If you use timestamp1 format, OpenESQL requires you to compile applications with the DETECTDATE SQL directive.
For example:
$set sql(dbman=odbc, detectdate)
01 timestamp1 pic x(26).
. . .
move "{ts'1965-11-02 12:08:30'} to timestamp1
exec sql
insert into emp (TransDate) values (:timestamp1)
end-exec
If you use timestamp2 format, you can just move the date time literal without the special characters.
For example:
01 timestamp2 SQL TYPE IS TIMESTAMP.
. . .
move "1965-11-02 08:12:56” to timestamp2
exec sql
insert into emp (TransDate) values (:timestamp2)
end-exec
For more information on how to use the timestamp3 format, see the OpenESQL SQL TYPEs in this chapter.
DB2 supports timestamp1 and timestamp2 format for host variables. For DB2, the TIMESTAMP data type has a maximum length of 26 characters.
Oracle only supports PIC X(n) formats like timestamp1.
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:
01 hiredate pic x(10).
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:
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 either use dynamic SQL statements, or compile your application with SQL directive ALLOWNULLCHAR, or use SQL TYPE host variables. For more information, see the OpenESQL SQL TYPEs in this chapter. OpenESQL only supports a maximum of 65000 character per host variable.
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.
If you are not using OpenESQL, or you need to maintain compatibility with other ESQL preprocessors, you can skip this section. Otherwise, we recommend that you use the SQL TYPEs described in this section wherever possible.
Experience has shown that manipulating SQL data that involves date/time or binary data can be complicated using normal COBOL host variables, and that traditional techniques for handling variable-length character data can also be problematic. We have therefore extended OpenESQL to make it easier to declare host variables that more closely reflect the type of data that the SQL table stores via the SQL TYPE function. This allows more applications to be built using static rather than dynamic SQL syntax.
The following data types can be used as host variables with the SQL TYPE function:
SQL [TYPE] [IS] BINARY(n)
01 hv-name SQL TYPE IS BINARY(n)
generates
01 hv-name pic x(n).
SQL [TYPE] [IS] CHAR-VARYING(n)
01 hv-name SQL TYPE IS CHAR-VARYING(n)
generates
01 hv-name pic x(n).
SQL [TYPE] [IS] DATE
01 hv-name SQL TYPE IS DATE
generates
01 hv-name pic x(10).
SQL [TYPE] [IS] DATE-RECORD
01 hv-name SQL TYPE IS DATE-RECORD
generates
01 hv-name. 03 hv-name-year pic s9(4) comp-5. 03 hv-name-month pic 9(4) comp-5. 03 hv-name-day pic 9(4) comp-5.
OpenESQL supports the TIMESTAMP and TIMESTAMP-RECORD SQL TYPEs to make working with TIMESTAMP column data easier. Each of these types requires that data be organized in fixed date/time formats. Use TIMESTAMP to generate a single working-storage record to contain all timestamp information. Use TIMESTAMP-RECORD to generate a group-level record for the timestamp containing individual records for each element of the timestamp as follows:
SQL [TYPE] [IS] TIMESTAMP
SQL [TYPE] [IS] TIMESTAMP-RECORD
Note: If the OpenESQL format does not match the value of Oracle's NLS_TIMESTAMP_FORMAT parameter, Oracle generates an error message. Use Oracle's SQLPLUS utility to determine the value of NLS_TIMESTAMP_FORMAT. Then do one of the following:
exec sql
insert into mf_datetime
(col_a
,col_date
,col_timestamp
) values
(:mf-col-a
,TO_DATE(:mf-col-date, 'YYYY-MM-DD')
,TO_TIMESTAMP(:mf-col-timestamp, 'YYYY-MM-DD HH24:MI.SS.FF')
)
end-execSee your Oracle documentation for information on the SQLPLUS utility and the NLS_TIMESTAMP_FORMAT parameter.
| If you pass... | SQL Server returns... |
|---|---|
| 01/01/98 23:59.59.999 | 1998-01-02 00:00:00.000 |
| 01/01/98 23:59.59.995 | 1998-01-01 23:59:59.997 |
| 01/01/98 23:59.59.996 | 1998-01-01 23:59:59.997 |
| 01/01/98 23:59.59.997 | 1998-01-01 23:59:59.997 |
| 01/01/98 23:59.59.998 | 1998-01-01 23:59:59.997 |
| 01/01/98 23:59.59.992 | 1998-01-01 23:59:59.993 |
| 01/01/98 23:59.59.993 | 1998-01-01 23:59:59.993 |
| 01/01/98 23:59.59.994 | 1998-01-01 23:59:59.993 |
| 01/01/98 23:59.59.990 | 1998-01-01 23:59:59.990 |
| 01/01/98 23:59.59.991 | 1998-01-01 23:59:59.990 |
When using any DBMS that uses more than three digits to represent fractional seconds, including Oracle and DB2 UDB (depending on the type of DB2 server used), be aware of the following when porting to .NET:
.NET limits fractional seconds of date/time objects to three digits. When using TIMESTAMP or TIMESTAMP-RECORD SQL TYPE host variables in your application, OpenESQL automatically returns the timestamp value with zeros in the last 3 digits. For example, a fractional value of 123456 under Windows is returned by .NET as 123000.
To get around this, code character host variables (i.e., PIC X(n)for your TIMESTAMP and TIMESTAMP-RECORD fields, and be sure the column defined in your EXEC SQL statement has been changed to character data using the TO_CHAR and TO_TIMESTAMP functions. For example:
exec sql
select
a.col_date
,a.col_timestamp
,TO_CHAR(a.col_timestamp,'YYYY-MM-DD HH24:MI:SS.FF')
into
:mf-col-date
,:mf-col-timestamp
,:ws-char-ts
from mf_datetime a
where ( a.col_a = :mf-col-a )
end-exec
To get the correct number of fractional digits returned to your application, compile your program with SQL directive TARGETDB. For example, to compile an Oracle application, specify the directives SQL(DBMAN=ADO TARGETDB=ORACLE)
See the TIMESTAMP demos for more information.
01 hv-name SQL TYPE IS TIMESTAMP
generates
01 hv-name pic x(29).
01 hv-name SQL TYPE IS TIMESTAMP-RECORD
generates
01 hv-name. 03 hv-name-year pic s9(4) comp-5. 03 hv-name-month pic 9(4) comp-5. 03 hv-name-day pic 9(4) comp-5. 03 hv-name-hour pic 9(4) comp-5. 03 hv-name-min pic 9(4) comp-5. 03 hv-name-sec pic 9(4) comp-5. 03 hv-name-frac pic 9(9) comp-5.
This host variable definition:
01 mf.
03 mf-col-a pic s9(09) comp-5.
03 mf-col-date sql type date.
03 mf-col-timestamp sql type timestamp.
03 mf-col-tsrec sql type timestamp-record.
01 ws-char-ts pic x(29).
Requires this field initialization:
move 1 to mf-col-a
move "2005-03-31" to mf-col-date
move "2005-04-15 13:45:56.456123"
to mf-col-timestamp
move 2005 to mf-col-tsrec-year
move 04 to mf-col-tsrec-month
move 16 to mf-col-tsrec-day
move 16 to mf-col-tsrec-hour
move 55 to mf-col-tsrec-min
move 58 to mf-col-tsrec-sec
move 678000000 to mf-col-tsrec-frac
And this INSERT statement:
exec sql
insert into mf_datetime
(col_a
,col_date
,col_timestamp
) values
(:mf-col-a
,:mf-col-date
,:mf-col-timestamp
)
end-exec
Copyright © 2009 Micro Focus (IP) Ltd. All rights reserved.