Inserting Rows with NULL Column Values
Go Up to Inserting Data (Embedded SQL Guide)
Contents
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.
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 ...]);
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.