3.7.1. SQL_NULL Data Type

    The SQL_NULL type holds no data, but only a state: NULL or NOT NULL. It is not available as a data type for declaring table fields, PSQL variables or parameter descriptions. It was added to support the use of untyped parameters in expressions involving the IS NULL predicate.

    An evaluation problem occurs when optional filters are used to write queries of the following type:

    After processing, at the API level, the query will look like this:

    This is a case where the developer writes an SQL query and considers :param1 as though it were a variable that he can refer to twice. However, at the API level, the query contains two separate and independent _parameters. The server cannot determine the type of the second parameter since it comes in association with IS NULL.

    The following example demonstrates its use in practice. It assumes two named parameters — say, :size and  — which might, for example, get values from on-screen text fields or drop-down lists. Each named parameter corresponds with two positional parameters in the query.

    Explaining what happens here assumes the reader is familiar with the Firebird API and the passing of parameters in XSQLVAR structures — what happens under the surface will not be of interest to those who are not writing drivers or applications that communicate using the “naked” API.

    The application passes the parameterized query to the server in the usual positional ?-form. Pairs of “identical” parameters cannot be merged into one so, for two optional filters, for example, four positional parameters are needed: one for each ? in our example.

    After the call to isc_dsql_describe_bind(), the SQLTYPE of the second and fourth parameters will be set to SQL_NULL. Firebird has no knowledge of their special relation with the first and third parameters: that responsibility lies entirely on the application side.

    User has supplied a value

    First parameter (value compare): set *sqldata to the supplied value and *sqlind to 0 (for NOT NULL)

    Second parameter (NULL test): set sqldata to (null pointer, not SQL NULL) and *sqlind to 0 (for NOT NULL)

    User has left the field blank

    In other words: The value compare parameter is always set as usual. The SQL_NULL parameter is set the same, except that remains null at all times.