Host variable usage

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.

SQLCODE and SQLSTATE host variables

The ISO/ANSI standard allows an embedded SQL source file to declare the following special host variables within a 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).

The SQLCODE and SQLSTATE host variables must be visible in the scope of every embedded SQL statement that generates database requests.

For more information, see the description of the sqlpp -k option in SQL preprocessor.

The following is valid embedded SQL:

EXEC SQL INCLUDE SQLCA;
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 ...
}

The following is not valid embedded SQL:

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...
 // No SQLSTATE in scope of this statement
}

Discuss this page in DocCommentXchange.