1.3 Executing SQL statements
1.3.2 Input indicators and output indicators
It is difficult for database applications written in traditional programming languages to represent the semantics of the null value, because it is a universal value which is in the domain of all types, and the three-valued logic system which accompanies null values does not easily translate to the two-value logic system in traditional programming languages.
In Dylan, a universal value can be achieved if we ignore type specialization, but this inhibits optimization and method dispatching. Even if we were to forgo type specialization, the evaluation of arithmetic and comparison expressions is a problem since Dylan's logic system is boolean and not three-valued. Therefore, the SQL-ODBC library has a goal of identifying null values and translating them into Dylan values that can be recognized as representing null values.
In order to identify null values during SQL statement processing, the <sql-statement> class supports an input indicator and output indicator. An input indicator is a marker value or values which identifies an input host variable as containing the null value. An output indicator is a substitution value which semantically identifies columns of a retrieved record as containing the null value.
If the SQL-ODBC library encounters a null value when retrieving records from a database, and there is no appropriate indicator object, it signals a <data-exception> condition. The condition is signaled from result-set functions (including the collection protocol) and not the execute function.
During the execution of an SQL statement to which an input indicator value was supplied, each input host variable is compared (with the function \==) to the input indicator and, if it holds the input indicator value, the null value is substituted for it.
The input indicator may be a single value or a sequence of values. A single value is useful when it is in the domain of all input host variables; if the host variables have not been specialized, any newly created value will do. Otherwise, a sequence of values must be used. Input indicators that are general instances of <sequence> use their positional occurrence within the SQL statement as the key for the sequence.
The SQL SELECT statement is the only SQL statement that returns non-status results back to the client application. During the retrieval of these results, the SQL-ODBC library substitutes the output indicator, if supplied, for null values found in the columns of each record.
The output indicator may be a single value or a sequence of values. If the output indicator is a general instance of <sequence>, the element of the sequence whose key corresponds to the column index is used as the substitution value. Otherwise, the output indicator value itself is used as the substitution value.