The keywords 'exec sql' precede SQL statements in order to distinguish them from statements of the corresponding programming language. These keywords 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 rules which apply to SQL statements are valid.
A semicolon terminates each SQL statement.
The keywords 'exec' and 'sql' must be placed on the same line. They may only be separated by blanks. Each SQL statement, including the 'exec sql' prefix, can extend to several lines.
An SQL statement can be interrupted by comments in C format (designated by '/*' and '*/'). Within an SQL statement, comments can also be placed at the end of a line. They must be preceded by the characters '--'.
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 structure 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.
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 called the declare section.
The parts to be analyzed by the precompiler (declare section, include sqlca, and SQL statements) must not be components of preprocessor include statements.
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 a 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 the option check, because the context of an SQL statement is not correct. In such a case, the option check makes no sense.