An example of using indicator variables

The code segments in Using indicator variables with EXEC SQL and the colon (:) symbol and Using indicator variables with the dollar sign ($) notation show examples of how to use indicator variables with host variables. Both examples use indicator variables to perform the following tasks:

Determine if truncation has occurred on a character array
If you define lname in the customer table with a length that is longer than 15 characters, nameind contains the actual length of the lname column. The name host variable contains the first 15 characters of the lname value. (The string name must be terminated with a null character.) If the family name of the company representative with customer_num = 105 is shorter than 15 characters, truncates only the trailing blanks.
Check for a null value
If company has a null value for this same customer, compind has a negative value. The contents of the character array comp cannot be predicted.

The following figure shows the program that uses the EXEC SQL syntax for the SQL statements.

Figure 1: Using indicator variables with EXEC SQL and the colon (:) symbol

EXEC SQL BEGIN DECLARE SECTION;
   char   name[16];
   char   comp[20];
   short  nameind;
   short  compind;
EXEC SQL END DECLARE SECTION;
;

EXEC SQL select lname, company
   into :name INDICATOR :nameind, :comp INDICATOR :compind
   from customer
   where customer_num = 105;

Using indicator variables with EXEC SQL and the colon (:) symbol uses the INDICATOR keyword to associate the main and indicator variables. This method complies with the ANSI standard.

The following figure shows the program that uses the dollar sign ($) format for the SQL statements.

Figure 2: Using indicator variables with the dollar sign ($) notation

$char   name[16];
$char   comp[20];
$short  nameind;
$short  compind;
;

$select lname, company
      into $name$nameind, $comp$compind
      from customer
      where customer_num = 105;