ESQL in COBOL Programs
Mimer SQL supports ESQL for COBOL following the COBOL-85 ANSI standard.
SQL Statement Format
The following sections discuss the SQL statement format.
SQL statements are identified by the leading delimiter
EXEC SQLand terminated by
SQL statements are treated exactly as ordinary COBOL statements with regard to the use of an ending period to mark the end of a COBOL sentence. Any valid COBOL punctuation may be placed after the
Examples:EXEC SQL DELETE FROM countries END-EXEC. IF SQLSTATE NOT = "02000" THEN EXEC SQL COMMIT END-EXEC ELSE EXEC SQL ROLLBACK END-EXEC.
Statements (including delimiters) may be written anywhere between positions 8 and 72 inclusive.
Line continuation rules for SQL statements are the same as those for ordinary COBOL statements.
If a string constant within an SQL statement is divided over several lines, the first non-blank character on the continuation line must be a string delimiter. There is no terminating string delimiter at the end of the line preceding the continuation line.
ExampleEXEC SQL SELECT CODE, CURRENCY FROM MIMER_STORE.CURRENCIES WHERE CODE LIKE :CURRENCY-CODE END-EXEC. EXEC SQL COMMENT ON TABLE CURRENCIES IS 'Holds currency - ' details' END-EXEC.
An alternative way to break a character string constant over several lines, is to use a white-space character (ASCII HEX-values 09 - 0D, or 20, i.e.
<TAB>, <LF>, <VT>, <FF>, <CR> or <SP>), to join two or more substrings.
Each substring must be separately enclosed in delimiters.EXEC SQL COMMENT ON TABLE CURRENCIES IS 'Holds currency'<CR> ' details' END-EXEC.
Comment lines, marked by an asterisk (
*) in position 7, may be written within SQL statements. The whole line following a comment mark is treated as a comment.
Debugging lines and page eject lines (marked by
/respectively in position 7) are treated as comments by the preprocessor.
The delimiters in SQL are single quotation marks (
') for string constants and double quotation marks (
") for delimited identifiers. This is contrary to the default COBOL string delimiter usage.EXEC SQL INSERT INTO "tablename" VALUES ('text string') END-EXEC.
Observe that the minus sign (
-) is valid in variable names in COBOL. All arithmetic expressions using this operator should have at least one space separating the operands from the operator. For example:
:A - B means: variable called A minus column B
:A-B means: variable called A-B
The following restrictions apply specifically to COBOL:
END-EXECis a keyword reserved to SQL.
- COBOL figurative constants (such as
SPACE) may not be used as constants in SQL statements.
Host Variables in COBOL
The following sections discuss declarations, SQL data type correspondence, preprocessor output format and value assignments.
Host variables used in SQL statements must be declared within the
SQL DECLARE SECTION, delimited by the statements
BEGIN DECLARE SECTIONand
END DECLARE SECTION.
Variables declared within the
SQL DECLARE SECTIONmust conform to the following rules in order to be recognized by the SQL preprocessor:
- variable names must begin with a letter. Within this restriction, any valid COBOL variable name may be used
- host variable structures may not be used
- the specifications
BLANK WITH ZERO, and
OCCURSmay not be used
- the data type must be consistent with SQL data types as specified below
- level number 01 or 77 should be used for all variable names that are used in SQL statements. Other levels may be used for program host variables, but they are not recognized by the preprocessor
FILLERentries are ignored for variables used in SQL statements
- Indicator variables should be declared as
PIC S9(9) COMP
A syntax diagram for COBOL variable declarations recognized by the ESQL/COBOL preprocessor is given below. Other declarations are ignored by the preprocessor:
Commas and semicolons may be used in accordance with standard COBOL practice.
The following abbreviations are accepted:
Note: The PIC S9(n)9(m) formulation is not accepted.
SQL Data Type Correspondence
Valid host data types are listed below for each of the data types used in SQL statements.
Varying-length character string structures may be used in ESQL statements in COBOL programs. In assigning the value of such variables to columns, the current length of the string is used.
The variable name used in SQL statements is the name of the structure (level 01 declaration), not of the character string element (level 49).
1 £ n £ 9 1 £ n+m £ 15 1 £ n
The general rules for conversion of values between compatible but different data types, see the Mimer SQL Reference Manual, SQL Syntax Elements, apply to the transfer of data between the database and host variables, with the data type correspondence as given in the table above.
The first element in a varying-length character string structure is used to store the current length of the character string. When writing to the variable, the first element is updated with the current length of the variable. If the column value is longer than the variable, the value is truncated.
Preprocessor Output Format
Output from the ESQL/COBOL preprocessor retains SQL statements from the original source code as comments. Comments within SQL statements are retained exactly as written. The output follows the ANSI standard for record format, and should be compiled with a COBOL compiler set to accept ANSI standard.
Debugging lines and page eject lines (using
/respectively in position 7) remain unchanged after preprocessing.
The preprocessed code is structured to reflect the structuring of the original source code.
Host variables follows the same scope rules as ordinary variables in COBOL. SQL descriptor names, cursor names and statement names must be unique within the compilation unit. A compilation unit for COBOL is the same as a routine.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40