Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

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.

Statement Delimiters

SQL statements are identified by the leading delimiter EXEC SQL and terminated by
END-EXEC.

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 END-EXEC terminator.

Examples: 
 EXEC SQL DELETE FROM countries END-EXEC.
 
 IF SQLSTATE NOT = "02000" THEN
             EXEC SQL COMMIT END-EXEC
 ELSE
             EXEC SQL ROLLBACK END-EXEC.

Margins

Statements (including delimiters) may be written anywhere between positions 8 and 72 inclusive.

Line Continuation

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.

Example: 
 EXEC 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.

Comments

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 D and / respectively in position 7) are treated as comments by the preprocessor.

Special Characters

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.

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

Restrictions

The following restrictions apply specifically to COBOL:

Host Variables in COBOL

The following sections discuss declarations, SQL data type correspondence, preprocessor output format and value assignments.

Declarations

Host variables used in SQL statements must be declared within the SQL DECLARE SECTION, delimited by the statements BEGIN DECLARE SECTION and END DECLARE SECTION.

Variables declared within the SQL DECLARE SECTION must conform to the following rules in order to be recognized by the SQL preprocessor:

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:

Abbreviation
Full term 
 PIC

 PICTURE or PICTURE IS

 USAGE

 USAGE or USAGE IS

 COMP

 COMPUTATIONAL

 SYNC

 SYNCHRONIZED

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).

SQL data type
COBOL data declaration
Comments 
 SMALLINT
 INTEGER
 BIGINT

 01 name PIC S9(n) COMP.
1 £ n £ 9
 
 DECIMAL
 NUMERIC

 01 name PIC S9(n)V9(m) 
COMP-3.
1 £ n+m £ 15
 
 FLOAT
 DOUBLE PRECISION

 01 name COMP-2.


 REAL

 01 name COMP-1.


 CHARACTER
 VARCHAR
 DATETIME
 INTERVAL
 CLOB

 01 name PIC X(n).
1 £ n

Value Assignments

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 D and / respectively in position 7) remain unchanged after preprocessing.

The preprocessed code is structured to reflect the structuring of the original source code.

Scope Rules

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
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX