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.