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.

Because COBOL supports the packed decimal data type, the Ingres decimal type is mapped to it. In COBOL, the decimal data type is COMP‑3. For example, the COBOL packed decimal declarations (where Pr = precision and Sc = scale):

correspond to the Ingres decimal types:

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.

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.

User variables may contain embedded single quotes and need no special handling unless the variable represents the entire search condition of a where clause:

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:

Otherwise, a runtime error will occur. For more information on escaping single quotes, see String Literals in this chapter.

Embedded SQL accepts data declarations in the alphabetic, alphanumeric, and alphanumeric edited categories. The syntax for declaring data items in those categories is:

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:

An indicator data item is a 2‑byte integer numeric data item. You can use an indicator data item in an application in three ways:

An indicator data item declaration must have the following syntax:

where p is less than or equal to 4.

When associating an indicator array (COBOL table) with a COBOL record, you must declare the indicator array as an array of 2‑byte integers. In the example above, the data item IND‑ARRAY can be used as an indicator array with a record assignment.

The syntax for a declaration of numeric edited data is:

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 (COMP‑3) 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.

Embedded SQL/COBOL accepts the following declarations of numeric variables:

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 non‑scaled COMP, COMP‑3, COMP‑5, 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 integer‑valued 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 COMP‑3 scaled data items is allowed. For more information on type conversion, see Data Type Conversion in this chapter.

Ingres supports data items declared with USAGE COMP‑5. 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 byte‑wise 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 COMP‑5 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 COMP‑5 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.

Embedded SQL accepts COBOL record and group declarations. To declare a record, use the following syntax:

where record-item is a group item:

or an elementary item:

DCLGEN (Declaration Generator) is a structure‑generating 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:

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:

and the DCLGEN system‑level command is:

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:

Use the embedded SQL include statement, in an embedded SQL declaration section, to include this file as follows:

You can then use the emprec record in a select, fetch, or insert statement.

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.

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:

and the DCLGEN system-level command is:

The contents of the jobs.dcl file would be:

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:

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:

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.

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.

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.

The following examples demonstrate some simple embedded SQL/COBOL declarations.