The keywords EXEC SQL precede SQL statements in order to distinguish them from the statements of the corresponding programming language. They are not components of the SQL statement.
In addition to SQL statements, it is possible to perform QUERY commands, REPORT sequences, and COMMAND system calls from programs. The keywords EXEC QUERY precede QUERY commands, EXEC REPORT precede REPORT sequences, and EXEC COMMAND precede COMMAND system calls. Otherwise, the same rules are valid which apply to SQL statements.
The keyword END-EXEC terminates SQL statements, QUERY routines, and REPORT sequences.
Each SQL statement, including the EXEC SQL prefix, can extend to several lines. It can begin in the COBOL AREA A or B (column 8-72).
An SQL statement can be interrupted by comments in COBOL format.
Identifiers declared in the program must not begin with the characters 'SQ'.
By means of the SQL statement EXEC SQL INCLUDE <filename>, any arbitrary source text can be inserted into the program. The source text is stored in the specified file and must not contain an INCLUDE statement of its own.
Return codes of the database system which, for example, inform about error occurrences are returned via the global record SQLCA (SQL Communication Area). The component SQLCODE contains encoded messages regarding the execution of an SQL statement. This code should be checked whenever ADABAS has been called. At precompilation time, the SQLCA is included at an appropriate place. (The INCLUDE SQLCA statement will be deleted.)
The variables of the programming language which are used for the transfer of values from and to the database are called host variables. They are declared in a special section: the DECLARE SECTION.
The parts to be analyzed by the precompiler (DECLARE SECTION, INCLUDE SQLCA, and SQL statements) must not be components of COBOL copy elements.
Host variables which are used in SQL statements are preceded by a colon. They have the form ':var' and can therefore be distinguished from ADABAS identifiers. When they are used outside of SQL statements, the colon is omitted. Example:
To process NULL values (undefined values) of the ADABAS database system, special host variables, called indicator variables (indicators), are required. These are specified - also with preceding colon - immediately after the corresponding host variable. Their value indicates whether NULL values have been encountered or whether character strings have been truncated when they were passed to host variables.
Within SQL statements, host variables can only be used at those positions where the SQL syntax allows parameters (for more details refer to the Reference manual). In particular, no table names can be specified via host variables. To substitute table and column names, there is a separate macro mechanism available.
When the option CHECK is set, the precompiler tests the SQL statements by making one sequential pass through the program. For a correct check of the SQL statements, it is therefore important to observe the following conventions about the static position of SQL statements:
- CREATE TABLE precedes INSERT, UPDATE, DELETE, SELECT. These five precede DROP TABLE.
- SELECT precedes FETCH. (An unambiguous assignment of SELECT statement and FETCH loop must be possible.)
- DECLARE CURSOR precedes OPEN, OPEN precedes FETCH, FETCH precedes CLOSE.
- In the case of dynamic statements, PREPARE precedes EXECUTE, DECLARE, DESCRIBE, OPEN, FETCH, and CLOSE.
- In the case of macros, it is necessary to call SET MACRO before using the macro parameter specified in an SQL statement.
These conventions do not affect the dynamic order of the SQL statements at execution time.
Deviations from the above conventions result in confusing warnings when precompiling with CHECK option, because the context of an SQL statement is not correct. In such a case the option CHECK makes no sense.