Using Indicator Variables

Another method for trapping and assigning NULL values – through indicator variables – is necessary in applications that prompt users for data, where users can choose not to enter values. By default, when InterBase stores new data, it stores zeroes for NULL numeric data, and spaces for NULL character data. Because zeroes and spaces may be valid data, it becomes impossible to distinguish missing data in the new row from actual zeroes and spaces.

To trap missing data with indicator variables, and store NULL value flags, follow these steps:

1. Declare a host-language variable to use as an indicator variable.

2. Test a value entered by the user and set the indicator variable to one of the following values:

	0	The host-language variable contains data.
	–1	The host-language variable does not contain data.

3. Associate the indicator variable with the host variable in the INSERT statement using the following syntax:

INSERT INTO table (<col> [, <col> ...])
VALUES (:variable [INDICATOR] :indicator
[, :variable [INDICATOR] :indicator ...]);

Note: The INDICATOR keyword is optional.

For example, the following C code fragment prompts the user for the name of a department, the department number, and a budget for the department. It tests that the user has entered a budget. If not, it sets the indicator variable, bi, to –1. Otherwise, it sets bi to 0. Finally, the program INSERTS the information into the DEPARTMENT table. If the indicator variable is –1, then no actual data is stored in the BUDGET column, but a flag is set for the column indicating that the value is NULL.

. . .
EXEC SQL
BEGIN DECLARE SECTION;
short bi; /* indicator variable declaration */
char department[26], dept_no_ascii[26], budget_ascii[26];
long num_val; /* host variable for inserting budget */
short dept_no;
EXEC SQL
END DECLARE SECTION;
. . .
printf("Enter new department name: ");
gets(cidepartment);
printf("\nEnter department number: ");
gets(dept_no_ascii);
printf("\nEnter department’s budget: ");
gets(budget_ascii);
if (budget_ascii = "")
{
bi = -1; num_val = 0;
}
else
{
bi = 0;
num_val = atoi(budget_ascii);
}
dept_no = atoi(dept_no_ascii);
EXEC SQL
INSERT INTO DEPARTMENT (DEPARTMENT, DEPT_NO, BUDGET)
VALUES (:department, :dept_no, :num_val INDICATOR :bi);
. . .

Indicator status can also be determined for data retrieved from a table. For information about trapping NULL values retrieved from a table, see Retrieving Indicator Status.