Inserting Rows with NULL Column Values

From InterBase

Go Up to Inserting Data (Embedded SQL Guide)


Sometimes when a new row is added to a table, values are not necessary or available for all its columns. In these cases, a NULL value should be assigned to those columns when the row is inserted. There are three ways to assign a NULL value to a column on insertion:

  • Ignore the column.
  • Assign a NULL value to the column. This is standard SQL practice.
  • Use indicator variables.

Ignoring a Column

A NULL value is assigned to any column that is not explicitly specified in an INTO clause. When InterBase encounters an unreferenced column during insertion, it sets a flag for the column indicating that its value is unknown. For example, the DEPARTMENT table contains several columns, among them HEAD_DEPT, MNGR_NO, and BUDGET. The following INSERT does not provide values for these columns:

EXEC SQL
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENT)
VALUES (:newdept_no, :newdept_name);

Because HEAD_DEPT, MNGR_NO, and BUDGET are not specified, InterBase sets the NULL value flag for each of these columns.

Note:
If a column is added to an existing table, InterBase sets a NULL value flag for all existing rows in the table.

Assigning a NULL Value to a Column

When a specific value is not provided for a column on insertion, it is standard SQL practice to assign a NULL value to that column. In InterBase a column is set to NULL by specifying NULL for the column in the INSERT statement.

For example, the following statement stores a row into the DEPARTMENT table, assigns the values of host variables to some columns, and assigns a NULL value to other columns:

EXEC SQL
INSERT INTO DEPARTMENT
(DEPT_NO, DEPARTMENT, HEAD_DEPT, MNGR_NO, BUDGET,
LOCATION, PHONE_NO)
VALUES (:dept_no, :dept_name, NULL, NULL, 1500000, NULL, NULL);

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.

Advance To: