Was this helpful?
Data Types
Embedded SQL supports a subset of the COBOL data types. The following table maps the COBOL data types to their corresponding Ingres types. Note that the COBOL data type is determined by its category, picture, and usage.
Category
COBOL Type Picture
Usage
Ingres Type
ALPHABETIC
any
DISPLAY
character
ALPHANUMERIC
any
DISPLAY
character
ALPHANUMERIC
EDITED
any
DISPLAY
Character
NUMERIC
9(p) where p <=10
COMP DISPLAY
integer
NUMERIC
9(p)V9(s) where p+s <=9
COMP DISPLAY
float
NUMERIC
9(p) where p <=10
COMP-3
Integer
NUMERIC
9(p) where p >10
COMP-3
decimal
NUMERIC
9(p)V9(s)
COMP-3
decimal
NUMERIC
 
INDEX
integer
NUMERIC EDITED
any
DISPLAY
integer float
NUMERIC
 
COMP-3
Decimal
Because COBOL supports the packed decimal data type, the Ingres decimal type is mapped to it. In COBOL, the decimal data type is COMP3. For example, the COBOL packed decimal declarations (where Pr = precision and Sc = scale):
01 PACK1 PIC S9(Pr-Sc)V9(Sc) USAGE COMP-3.
01 PACK2 PIC S9(Pr)   USAGE COMP-3.
correspond to the Ingres decimal types:
DECIMAL (Pr,Sc)
DECIMAL (Pr,0)
Note that Ingres precision includes scale, since it includes the total number of digits, and Ingres scale is the number of digits to the right of the decimal point.
The sign (S) is optional on a COBOL declaration and is ignored by the preprocessor. However, decimal values are always stored as signed by Ingres.
Note:  You should always retrieve Ingres decimal data into a signed decimal variable.
COMP is an abbreviation for COMPUTATIONAL. You can use either form. Note that POINTER data items are not supported. The following sections describe the various data categories and the manner in which embedded SQL interacts with them.
Character strings containing embedded single quotes are legal in SQL.
Example: Embedded single quotes usage
mary's
User variables may contain embedded single quotes and need no special handling unless the variable represents the entire search condition of a where clause:
where :variable
In this case you must escape the single quote by reconstructing the :variable string so that any embedded single quotes are modified to double single quotes, as in:
mary''s
Otherwise, a runtime error will occur. For more information on escaping single quotes, see String Literals in this chapter.
Alphabetic, Alphanumeric, and Alphanumeric Edited Categories
Embedded SQL accepts data declarations in the alphabetic, alphanumeric, and alphanumeric edited categories. The syntax for declaring data items in those categories is:
level-number data-name PIC [IS] pic-string
[[USAGE [IS]] DISPLAY].
Syntax: The pic-string can be any legal COBOL picture string for the alphabetic, alphanumeric, and alphanumeric edited classes. Embedded SQL notes only the length of the data item and that the data item is in the alphanumeric class.
You can use alphabetic, alphanumeric, and alphanumeric edited data items with any Ingres object of character (char or varchar) type. You can also use them to replace names of certain objects if the particular embedded SQL statement allows dynamic specification of object names. Note, however, that, when a value is transferred into a data item from an Ingres object, it is copied directly into the variable storage area without regard to the COBOL special insertion rules.
When data in the database is in a different format from the alphanumeric edited picture, you must provide an extra variable to receive the data. You can then MOVE the data into the alphanumeric edited variable. However, if data in the database is in the same format as the alphanumeric edited picture (which would be the case, for example, if you had inserted data using the same variable you are retrieving into), you can assign the data directly into the edited data item, without any need for the extra variable. For more information on type conversion, see Data Type Conversion in this chapter.
The following example illustrates the syntax for these categories:
01 ENAME      PIC X(20).
01 EMP-CODE   PIC xx/99/00.
Indicator Data Items
An indicator data item is a 2byte integer numeric data item. You can use an indicator data item in an application in three ways:
In a statement that retrieves data from Ingres, you can use an indicator data item to determine if its associated host variable was assigned a null value.
In a statement that sets data to Ingres, you can use an indicator data item to assign a null to the database column, form field, or table field column.
In a statement that retrieves character data from Ingres, you can use an indicator data item to check if the associated host variable is large enough to hold the full length of the returned character string. You can also use SQLSTATE or SQLCODE to do this. It is preferable to use SQLSTATE because SQLCODE is a deprecated feature.
An indicator data item declaration must have the following syntax:
level-number indicator-name PIC [IS] S9(p) [USAGE [IS]] COMP
where p is less than or equal to 4.
Example: Indicator declaration
01 IND-VAR      PIC9(2) USAGE COMP.
01 IND-TABLE.
    02 IND-ARRAY  PIC S9(2) USAGE COMP OCCURS 10 TIMES.
When associating an indicator array (COBOL table) with a COBOL record, you must declare the indicator array as an array of 2byte integers. In the example above, the data item INDARRAY can be used as an indicator array with a record assignment.
Numeric Edited Data Category
The syntax for a declaration of numeric edited data is:
level-number data-name PIC [IS] pic-string [[USAGE [IS]]DISPLAY]
Syntax Notes:
The pic-string can be any legal COBOL picture string for numeric edited data. Embedded SQL notes only the type, scale, and size of the data item.
To interact with Ingres integervalued objects, the picture string must describe a maximum of 10 digit positions with no scaling.
While you can use numeric edited data items to assign data to, and receive data from, Ingres database tables and forms, be prepared for some loss of precision for numeric edited data items with scaling. The runtime interface communicates by integer (COMP) or uses packed (COMP3) for Linux variables. In moving from these variables into your program's edited data items, truncation can occur due to MOVE statement rules and the COBOL standard alignment rules. For more information on type conversion, see Data Type Conversion in this chapter.
Example: Numeric edited data category usage
01 DAILY-SALES   PIC $$$,$$9DB USAGE DISPLAY.
01 GROWTH-PERCENT  PIC ZZZ.9(3) USAGE DISPLAY.
Numeric Data Category-
Embedded SQL/COBOL accepts the following declarations of numeric variables:
level-number data-name PIC [IS] pic-string [USAGE [IS]COMP|COMP-3
              |COMP-5|DISPLAY.
level-number data-name [USAGE [IS]] INDEX.
Use the symbol S on numeric picture strings to indicate the presence of an operational sign.
The picture string (pic-string) of a COMP, COMP-3, or COMP-5 data item can contain only the symbols 9, S, and V in addition to the parenthesized length.
To interact with Ingres integervalued objects, the picture string of a COMP, COMP3, COMP5, or DISPLAY item must describe a maximum of 10 digit positions with no scaling.
Do not use a picture string for INDEX data items. While the preprocessor ignores such a picture string, the compiler does not allow it.
You can use any data items in the numeric category to assign and receive Ingres numeric data in database tables and forms. However, only use nonscaled COMP, COMP3, COMP5, and DISPLAY items of 10 digit positions or less to specify simple numeric objects, such as table field row numbers. Generally, try to use COMP data items with no scaling to interact with Ingres integervalued objects, since the internal format of COMP data is compatible with Ingres integer data. Ingres effects the necessary conversions between all numeric data types, so the use of DISPLAY and COMP3 scaled data items is allowed. For more information on type conversion, see Data Type Conversion in this chapter.
Example: Numeric data category usage
01 QUAD-INTVAR  PIC  S9(10) USAGE COMP.
01 LONG-INTVAR  PIC  S9(9)  USAGE COMP.
01 SHORT-INTVAR PIC  S9(4)  USAGE COMP.
01 DISPLAY-VAR  PIC  S9(10) USAGE DISPLAY.
01 PACKED-VAR   PIC  S9(12)V9(4) USAGE COMP-3.
Numeric Data Items with Usage COMP‑5—Linux
Ingres supports data items declared with USAGE COMP5. When you specify this clause, the data item is stored in the same machine storage format as the native host processor rather than in the bytewise Micro Focus storage format. Of course, sometimes the two storage formats are identical. Since the Ingres runtime system that is linked into your COBOL runtime support module (RTS) is written in C, it is important that Ingres interact with native data types rather than Micro Focus data types. Consequently, many of your normal USAGE COMP data items are transferred (using COBOL MOVE statements) into internally declared Ingres USAGE COMP5 data items. Data items declared with this USAGE cause a compiler information message (209 I) to occur.
Dynamic SQL requires that your program point directly at result data items. In that case, you may be required to use USAGE COMP5 data items, rather than having the option to use COMP or COMP-5. For details on dynamic SQL, see Dynamic Programming for COBOL in this chapter.
Record Declarations
Embedded SQL accepts COBOL record and group declarations. To declare a record, use the following syntax:
01 data-name.
              record-item.
             {record-item.}
where record-item is a group item:
level-number data-name.
              record-item.
             {record-item.}
or an elementary item:
level-number data-name elementary-item-description.
Syntax Notes:
The record must have a level number of 01. Thereafter, the level numbers of record‑items can be 02 through 49. Embedded SQL applies the same rules as the COBOL compiler in using the level numbers to order the groups and elementary items in a record definition into a hierarchical structure.
If you do not specify elementary‑item‑description for a record item, the preprocessor and the COBOL compiler assume that the record item is a group item.
The elementary‑item‑description can consist of any attributes described for data declarations in the Data Item Declaration Syntax section. The preprocessor does not confirm that the different clauses are acceptable for record items.
The OCCURS clause, denoting a COBOL table, may appear on any record item.
Example: Record declaration
01 EMPTABLE.
  02 EMPREC OCCURS 25 TIMES.
      03 ENAME    PIC X(20).
      03 EADDRESS.
         04 ESTREET   PIC X(15).
         04 ECITY     PIC X(12).
         04 ESTATE    PIC X(2).
         04 EZIP      PIC X(5).
      03 ESALARY PIC S9(6) USAGE COMP.
DCLGEN Utility
DCLGEN (Declaration Generator) is a structuregenerating utility that maps the columns of a database table into a structure (a COBOL record) that can be included in an embedded SQL declaration section.
The DCLGEN utility can be invoked from the operating system level by executing the following command:
dclgen language dbname tablename filename structurename [-n] [-q]
language
Defines the embedded SQL host language, in this case, COBOL.
dbname
Defines the name of the database containing the table.
tablename
Defines the name of the database table.
filename
Defines the output file into which the structure declaration is placed.
structurename
Defines the name of the host language structure (COBOL record) that the command generates.
-n
Does not print the DECLARE TABLE statement.
-q
Creates output in QUEL format.
This command creates the declaration file filename, containing a record corresponding to the database table. The file also includes a declare table statement that serves as a comment and identifies the database table and columns from which the record was generated.
After the file has been generated, you can use an embedded SQL include statement to incorporate it into the embedded SQL variable declaration section. The following example demonstrates how to use DCLGEN in a COBOL program.
Assume the Employee table was created in the Personnel database as:
EXEC SQL CREATE TABLE employee
(eno integer NOT NULL,
ename char(20) NOT NULL,
age integer1,
job smallint,
sal decimal (14,2) NOT NULL,
dept smallint,
vacation float,
resume long varchar)
END-EXEC.
and the DCLGEN systemlevel command is:
DCLGEN cobol personnel employee employee.dcl emprec
The employee.dcl file created by this command contains a comment and two statements. The first statement is the declare table description of employee, which serves as a comment. The second statement is a declaration of the COBOL emprec record.
The contents of the employee.dcl file are:
* Description of table "employee" from database * "personnel"
EXEC SQL DECLARE employee TABLE
(eno integer NOT NULL,
 ename char(20) NOT NULL,
 age integer1,
 job smallint,
 sal decimal(14,2) NOT NULL,
 dept smallint
 vacation float,
 resume long varchar)
END-EXEC.
01 EMPREC.
 02 ENO         PIC S9(9) USAGE COMP.
 02 ENAME       PIC X(20).
 02 AGE         PIC S9(5) USAGE COMP.
 02 JOB         PIC S9(5) USAGE COMP.
 02 SAL         PIC S9(12)V9(2) USAGE COMP-3.
 02 DEPT        PIC S9(5) USAGE COMP.
 02 VACATION    PIC S9(10)V9(8) USAGE COMP-3.
 02 RESUME      PIC X(0).
Use the embedded SQL include statement, in an embedded SQL declaration section, to include this file as follows:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
    EXEC SQL INCLUDE 'employee.dcl' END-EXEC.
EXEC SQL END DECLARE SECTION END-EXEC.
You can then use the emprec record in a select, fetch, or insert statement.
Linux:
The default generated picture string for Ingres floating-point data is S9(10)V9(8).
DCLGEN converts underscores in column names to dashes when it generates names of the elements of the COBOL record. For example, a column name of column_1 translates to a record element name of column-1. Column names that begin or end with an underscore thus generate record element names unacceptable to the COBOL compiler.
Since COBOL supports packed decimal data, the structure member's type will be packed decimal with a precision and scale that matches the scale and precision of the database column.
Micro Focus COBOL only allows a maximum precision of 18, otherwise a compiler error is generated. Ingres allows 39 precision. If the decimal column is greater than 18, DCLGEN displays a warning message and generates a COBOL variable of S9(10)V9(8). You must verify that this is an acceptable size for the decimal columns because if it's not, you must manually modify the DCLGEN output file.
The field names of the structure that DCLGEN generates are identical to the column names in the specified table. Therefore, if the column names in the table contain any characters that are illegal for host language variable names, you must modify the name of the field before attempting to use the variable in an application.
DCLGEN and Large Objects
When a table contains a large object column, DCLGEN will issue a warning message and map the column to a zero length character string variable. You must modify the length of the generated variable before attempting to use the variable in an application.
For example, assume that the job_description table was created in the personnel database as:
create table job_description
          (job          smallint,
           description long varchar));
and the DCLGEN system-level command is:
dclgen cobol personnel job_description jobs.dcl jobs_rec
The contents of the jobs.dcl file would be:
* Description of the table "employee" from database "personnel"
    EXEC SQL DECLARE long_obj_table TABLE
                  (job          smallint,
                   description long varchar));
 01 JOBS_REC.
    02 JOB               PICTURE S9(4) USAGE COMP.
    02 DESCRIPTION       PICTURE X(0).
How to Declare External Compiled Forms
You can precompile your forms in the Visual Forms Editor (VIFRED). This saves the time otherwise required at runtime to extract the form's definition from the database forms catalogs. When you compile a form in VIFRED, VIFRED creates a file in your directory describing the form in C. VIFRED prompts you for the name of the file with the C description. After the C file is created, you can use the following command to compile it into a linkable object module:
Windows:
cl -c filename.c
Linux:
cc -c filename.c
This command produces an object file containing a global symbol with the same name as your form. Before the embedded SQL/FORMS statement addform can refer to this global object, you must use the following syntax to declare it in an embedded SQL declaration section:
01 formname [IS] EXTERNAL PIC S9(9) [USAGE [IS]] COMP-5.
Some platforms do not accept the above syntax. If EXTERNAL data items cannot be referenced in your COBOL program, see How to Include External Compiled Forms in the RTS in this chapter for an alternate procedure.
Syntax Notes:
The formname is the actual name of the form. VIFRED gives this name to the global object. The formname is used to refer to the form in embedded SQL statements after the form has been made known to the FRS using the addform statement.
The EXTERNAL clause causes the linker to associate the formname data item with the external formname symbol.
The following example shows a typical form declaration and illustrates the difference between using the form's global object definition and the form's name. However, currently, this example does not work on all Micro Focus platforms.
Example: Form declaration
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 empform IS EXTERNAL PIC S9(9) USAGE COMP-5.
* Other embedded SQL data declarations.
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
* Program initialization.
* Making the form known to the FRS via the global
* form object.
EXEC FRS ADDFORM :empform END-EXEC.
* Displaying the form via the name of the form.
EXEC FRS DISPLAY empform END-EXEC.
* The program continues.
For information on linking your embedded SQL program with external compiled forms, see How to Include External Compiled Forms in the RTS in this chapter.
Embedded SQL/COBOL Declarations Example
The following examples demonstrate some simple embedded SQL/COBOL declarations.
Windows:
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
Linux:
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
* Data item to hold database name.
 01 DBNAME PIC X(9) VALUE IS "Personnel".

* Scaled data
 01 SALARY PIC S9(8)V9(2) USAGE COMP.
 01 MONEY PIC S999V99 USAGE COMP-3.

* Array of numerics
 01 NUMS.
   02 NUM-ARR PIC S99 OCCURS 10 TIMES.

* Record of a full name and a redefinition of its parts.
 01 NAME-REC.
   02 FULL-NAME PIC X(20).
   02 NAME-PARTS REDEFINES FULL-NAME.
      03 FIRST-NAME PIC X(8).
      03 MIDDLE-INIT PIC X(2).
      03 LAST-NAME PIC X(10).

* Record for fetching and displaying.
 01 OUT-REC.
   02 FILLER PIC X(15) VALUE "Value fetched: ".
   02 FROM-DB PIC S9(4) USAGE DISPLAY.

* Miscellaneous attributes (ignored by preprocessor).
 01 SALES-TOT PIC S9(6)V99 SIGN IS TRAILING.
 01 SYNC-REC.
   02 NUM1 PIC S99 USAGE COMP SYNCHRONIZED.
   02 FILLER PIC XX VALUE SPACES.
   02 NUM2 PIC S99 USAGE COMP SYNCHRONIZED.
01 RIGHT-ALIGN PIC X(30) JUSTIFIED RIGHT.
01 NUM-OUT PIC S99V99 USAGE DISPLAY BLANK WHEN ZERO.
EXEC SQL END DECLARE SECTION END-EXEC.
Last modified date: 11/09/2022