Indicator variable arrays

In the following example, the indicator variable array that is called bonus_ind is declared. The bonus_ind indicator variable array can have up to 100 elements, the same cardinality as the bonus array variable. When the data is being fetched, if the value of bonus is NULL, the value in bonus_ind is negative.

EXEC SQL BEGIN DECLARE SECTION;
    char   empno[100][8];
    char   lastname[100][15];
    short  edlevel[100];
    double bonus[100];
    short  bonus_ind[100];
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE empcr CURSOR FOR
	SELECT empno, lastname, edlevel, bonus
		FROM employee
		WHERE workdept = 'D21';

EXEC SQL OPEN empcr;

EXEC SQL WHENEVER NOT FOUND GOTO end_fetch;

while (1) {
	EXEC SQL FETCH empcr INTO :empno :lastname :edlevel, 
							  :bonus INDICATOR :bonus_ind
	...                                           
	...
}
end_fetch:
EXEC SQL CLOSE empcr;

Instead of being identified by the INDICATOR keyword, an indicator variable can immediately follow its corresponding host variable, as shown in the following example:

EXEC SQL FETCH empcr INTO :empno :lastname :edlevel, :bonus:bonus_ind

In the following example, the indicator variable arrays ind_in1 and ind_in2 are declared. It can have up to three elements, the same cardinality as the arr_in1 and arr_in2 array variables. If the value of ind_in1 or ind_in2 is negative, the NULL value is inserted for the corresponding arr_in1 or arr_in2 value.

// Declare host & indicator variablesof array size 3
EXEC SQL BEGIN DECLARE SECTION;
  sqlint32 arr_in1[3];
  char arr_in2[3][11];
  short ind_in1[3]; // indicator array size is same as host
                    // variable’s array size
  short ind_in2[3]; // note here indicator array size is greater 
                    // than host variable’s array size
EXEC SQL END DECLARE SECTION;

...
// Populating the arrays.
for ( i = 0; i < 3; i++)
{
    arr_in1[i] = i + 1;
    sprintf(arr_in2[i], "hello%d", arr_in1[i]);
}

ind_in1[0] = 0;
ind_in1[1] = SQL_NULL_DATA; // Mark it as a NULL data
ind_in1[2] = 0; 

ind_in2[0] = 0;
ind_in2[1] = 0; 
ind_in2[2] = SQL_NULL_DATA; // Mark it as a NULL data


// ‘arr_in1’ & ‘arr_in2’ are host variable arrays
// ‘ind_in1’ & ‘ind_in2’ are indicator variable arrays
EXEC SQL INSERT into tbl1 values (:arr_in1 :ind_in1, :arr_in2 :ind_in2);

// The tb11 table now contains the following rows:
C1          C2
----------- -----------
          1 hello1    
            hello2  // c1 is set to NULL as indicator is set
          3         // c2 is set to NULL as indicator is set

If the cardinality of indicator variable array does not match the cardinality of the corresponding host variable array, an error is returned.

In the following example, the indicator structure array MyStructInd is declared.

// declaring indicator structure array of size 3 
EXEC SQL BEGIN DECLARE SECTION;
   ...

   struct MyStructInd
   {
       short c1_ind;
       short c2_ind;
   } MyStructVarInd[3];
EXEC SQL END DECLARE SECTION;

...

// using structure array host variables & indicators structure type
// array while executing FETCH statement
// ‘MyStructVar’ is structure array for host variables
// ‘MyStructVarInd’ is structure array for indicators
EXEC SQL FETCH cur INTO :MyStructVar :MyStructVarInd;