Host variables are defined by putting them into a declaration section. According to the ANSI embedded SQL standard, host variables are defined by surrounding the normal C variable declarations with the following:
EXEC SQL BEGIN DECLARE SECTION; /* C variable declarations */ EXEC SQL END DECLARE SECTION;
These host variables can then be used in place of value constants in any SQL statement. When the database server executes the command, the value of the host variable is used. Note that host variables cannot be used in place of table or column names: dynamic SQL is required for this. The variable name is prefixed with a colon (:) in a SQL statement to distinguish it from other identifiers allowed in the statement.
The SQL preprocessor does not scan C language code except inside a DECLARE SECTION. Thus, TYPEDEF types and structures are not allowed. Initializers on the variables are allowed inside a DECLARE SECTION.
The following sample code illustrates the use of host variables on an INSERT command. The variables are filled in by the program and then inserted into the database:
EXEC SQL BEGIN DECLARE SECTION; long employee_number; char employee_name; char employee_initials; char employee_phone; EXEC SQL END DECLARE SECTION; /* program fills in variables with appropriate values */ EXEC SQL INSERT INTO Employees VALUES (:employee_number, :employee_name, :employee_initials, :employee_phone );
For a more extensive example, see Static cursor sample.