Program Structure
To develop correct SQL application programs, it is important to understand the difference between the physical order of the SQL statements in a program, and the order of their execution.
The CONNX Embedded SQL scans the source application program for SQL statements and effectively skips any host language statements or commands. The CONNX Embedded SQL has no understanding of the underlying logic of the application, or the context of any particular SQL statement. All it can actually understand is an isolated collection of SQL statements.
Under the following circumstances, the physical order of the statements is relevant and does not have anything to do with the actual order in which the statements will be executed.
When running in ANSI compatibility mode, any statements, which reference a cursor, must physically follow the associated DECLARE CURSOR statement. In CONNX Embedded SQL mode, this restriction does not exist and therefore the physical order of such statements is irrelevant.
In CONNX Embedded SQL mode, although the DECLARE and OPEN statements must be in the same source file, other associated statements do not need to be. For more details, refer to the section on Static SQL.
The physical ordering of other statements can now follow freely, as long as any host variables accessed within an SQL statement have been declared physically prior to usage, and an SQLCA is in scope for each statement.
Positioning the SQL statement
Almost all SQL statements may be positioned anywhere within an application program where a host language statement is permitted. This is, because in general, SQL statements are replaced with appropriate generated host language statements by the precompiler. The rules governing the positioning of host language statements also apply to the embedding of SQL statements. The positioning of the SQL statement must also conform to the context of the logic of the application program. As long as each SQL statement is individually delimited, where the host language permits, more than one SQL statement can be positioned on a single line.
The following statements are exceptions to the above rules:
BEGIN DECLARE SECTION, END DECLARE SECTION, INCLUDE - These statements can only be positioned where host language declarations are allowed.
WHENEVER, DECLARE CURSOR (static) - These statements can be placed anywhere depending on the desired control flow.