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. This data type exists 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:

  |WHERE col1 = :param1 OR :param1 IS NULL

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

  |WHERE col1 = ? OR ? IS NULL

This is a case where the developer writes an SQL query and considers :param1 as though it were a variable that they 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 SQL_NULL data type solves this problem. Whenever the engine encounters an ? IS NULL predicate in a query, it assigns the SQL_NULL type to the parameter, which will indicate that parameter is only about nullness and the data type or the value need not be addressed.

The following example demonstrates its use in practice. It assumes two named parameters — say, :size and :colour — 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.

  |SELECT
  |  SH.SIZE, SH.COLOUR, SH.PRICE
  |FROM SHIRTS SH
  |WHERE (SH.SIZE = ? OR ? IS NULL)
  |  AND (SH.COLOUR = ? OR ? IS NULL)

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 the two optional filters in the 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.

Once the values for size and colour have been set (or left unset) by the user, and the query is about to be executed, each pair of XSQLVARs must be filled as follows:

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 (null pointer, not SQL NULL) and *sqlind to 0 (for NOT NULL)

User has left the field blank

Both parameters: set sqldata to null (null pointer, not SQL NULL) and *sqlind to -1 (indicating NULL)

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