Purpose
Denotes the beginning of an SQL statement in an embedded SQL application.
Invocation
Nonexecutable preprocessor declaration.
Embedded SQL only.
Syntax
EXEC SQL [ FOR [:] count_value ] embedded_sql_statement sql_statement_terminator
- FOR
- Specifies that parameter arrays are supported for the SQL statement that follows. Note that iterated support is provided only for multiple INSERTs.
- ([:]count_value)
- A user-defined INTEGER host variable or a literal INTEGER constant that specifies the number of iterations to be executed.
- embedded_sql_statement
- The embedded SQL statement to be executed by the client application program.
- sql_statement_terminator
- The SQL statement terminator for the client language.
ANSI Compliance
EXEC SQL is ANSI/ISO SQL:2011-compliant with extensions.
Authorization
None.
General Rules for Using EXEC SQL
- EXEC SQL must precede each SQL statement embedded in any client application program, regardless of the language used to write the application code.
- The phrase EXEC SQL must be coded together on a single line.
- The SQL statement that follows an EXEC SQL phrase can begin immediately following the phrase or it can begin on a new line.
- You cannot use EXEC SQL with interactive SQL statements.
Rules for Using EXEC SQL With DML Arrays
The following rules apply to using EXEC SQL with arrays using a FOR clause:
Iteration support is provided for simple single INSERT statements only.
- DELETE
- INSERT … SELECT
- SELECT
- UPDATE
You can specify a count_value using either a host variable or a literal INTEGER value.
All host variable parameter arrays must be single-dimensioned.
Arrays of arrays are not supported except in C, and then the system supports only arrays of character strings.
You are responsible for setting all host variables before they are used, including count_value.
Literal constants embedded in the SQL string are not iterated.
Instead, they are propagated in every inserted row.
The collection of host variables used in the iterated statement is treated as independent parallel arrays.
Preprocessor2 checks the value of FOR count_value to determine if it is less than or equal to any of the array sizes.
If the count value exceeds the array size for any column, Preprocessor2 aborts the request and returns an error message.
The same host variable can be specified for multiple fields.
Any given iteration will use the same value because there is one index for all of the arrays.
References to arrays of host program structs are not supported, only references to arrays of variables.
Example: Simple Array Example
This example provides a simple example of SQL DML array processing. Note that count_value is supplied as an INTEGER literal value of 19.
EXEC SQL FOR 19 INSERT INTO table1 VALUES (:var1, :var2, :var3);
Example: Array Example For Dynamic SQL
This example demonstrates the use of SQL DML array processing using dynamic SQL within a program written in C. Note that count_value is supplied using a host variable named cNewEmployees:
char empname[50][20]; integer empnum[50]; float empsal[50]; intc NewEmployees = 50; VARCHAR stmtstr[100]; char *ins001= “INSERT INTO EMPLOYEE (EMPLOYEE_NUMBER, LAST_NAME, SALARY_AMOUNT)” “VALUES (?, ?, ?);”; strcpy(stmtstr.arr,ins001); stmstr.len = strlen(ins001); EXEC SQL PREPARE insStmt FROM :stmtstr;’ EXEC SQL FOR :cNewEmployees EXECUTE insStmt USING :empnum, :empname, :empsal;
Related Topics
For more information about EXEC SQL, see END-EXEC Statement Terminator.