Host variables can be used in the following circumstances:
SELECT, INSERT, UPDATE, and DELETE statements in any place where a number or string constant is allowed.
The INTO clause of SELECT and FETCH statements.
Host variables can also be used in place of a statement name, a cursor name, or an option name in statements specific to embedded SQL.
For CONNECT, DISCONNECT, and SET CONNECT statements, a host variable can be used in place of a server name, database name, connection name, user ID, password, or connection string.
For SET OPTION and GET OPTION, a host variable can be used in place of the option value.
Host variables cannot be used in the following circumstances:
Host variables cannot be used in place of a table name or a column name in any statement.
Host variables cannot be used in batches.
Host variables cannot be used within a subquery in a SET statement.
The ISO/ANSI standard allows an embedded SQL source file to declare the following special host variables within an embedded SQL declaration section:
long SQLCODE; char SQLSTATE[6];
If used, these variables are set after any embedded SQL statement that makes a database request (EXEC SQL statements other than DECLARE SECTION, INCLUDE, WHENEVER SQLCODE, and so on). As a consequence, the SQLCODE and SQLSTATE host variables must be visible in the scope of every embedded SQL statement that generates database requests.
The following is valid embedded SQL:
EXEC SQL INCLUDE SQLCA;
// declare SQLCODE with global scope
EXEC SQL BEGIN DECLARE SECTION;
long SQLCODE;
EXEC SQL END DECLARE SECTION;
sub1() {
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
exec SQL CREATE TABLE ...
}
sub2() {
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
exec SQL DROP TABLE ...
}
The following is not valid embedded SQL because SQLSTATE is not defined in the scope of the function sub2:
EXEC SQL INCLUDE SQLCA;
sub1() {
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
exec SQL CREATE TABLE...
}
sub2() {
exec SQL DROP TABLE...
}