SQL_NULL data type

Table of Contents

Rationale
Use in practice

Tip

Find a more recent version at Firebird 5.0 Language Reference: SQL_NULL Data Type

Added in: 2.5

Description: The SQL_NULL data type is of little or no interest to end users. It can hold no data, only a state: NULL or NOT NULL. It is also not possible to declare columns, variables or PSQL parameters of type SQL_NULL. At present, its only purpose is to support the “? IS NULL” syntax in SQL statements with positional parameters. Application developers can make use of this when constructing queries that contain one or more optional filter terms.

Syntax: If a statement containing the following predicate is prepared:

? <op> NULL

Firebird will describe the parameter ('?') as being of type SQL_NULL. <op> can be any comparison operator, but the only one that makes sense in practice is “IS” (and possibly, in some rare cases, “NOT IS”).

Rationale

In itself, having a query with a “WHERE ? IS NULL” clause doesn't make a lot of sense. You could use such a parameter as an on/off switch, but that hardly warrants inventing a whole new data type. After all, such switches can also be constructed with a CHAR, SMALLINT or other parameter type. The reason for adding the SQL_NULL type is that developers of applications, connectivity toolsets, drivers etc. want to be able to support queries with optional filters like these:

select make, model, weight, price, in_stock from automobiles
  where (make = :make or :make is null)
    and (model = :model or :model is null)
    and (price <= :maxprice or :maxprice is null)

The idea is that the end user can optionally enter choices for the parameters :make, :model and :maxprice. Wherever a choice is entered, the corresponding filter should be applied. Wherever a parameter is left unset (NULL), there should be no filtering on that attribute. If all are unset, the entire table AUTOMOBILES should be shown.

Unfortunately, named parameters like :make and :model only exist on the application level. Before the query is passed to Firebird for preparation, it must be converted to this form:

select make, model, weight, price, in_stock from automobiles
  where (make = ? or ? is null)
    and (model = ? or ? is null)
    and (price <= ? or ? is null)

Instead of three named parameters, each occurring twice, we now have six positional parameters. There is no way that Firebird can tell whether some of them actually refer to the same application-level variable. (The fact that, in this example, they happen to be within the same pair of parentheses doesn't mean anything.) This in turn means that Firebird also cannot determine the data type of the “? is null” parameters. This last problem could be solved by casting:

select make, model, weight, price, in_stock from automobiles
  where (make = ? or cast(? as type of column automobiles.make) is null)
    and (model = ? or cast(? as type of column automobiles.model) is null)
    and (price <= ? or cast(? as type of column automobiles.price) is null)

...but this is rather cumbersome. And there is another issue: wherever a filter term is not NULL, its value will be passed twice to the server: once in the parameter that is compared against the table column, and once in the parameter that is tested for NULL. This is a bit of a waste. But the only alternative is to set up no less then eight separate queries (2 to the power of the number of optional filters), which is even more of a headache. Hence the decision to implement a dedicated SQL_NULL data type.

Use in practice

Notice: The following discussion assumes familiarity with the Firebird API and the passing of parameters via XSQLVAR structures. Readers without this knowledge won't have to deal with the SQL_NULL data type anyway and can skip this section.

As usual, the application passes the parameterized query in ?-form to the server. It is not possible to merge pairs of “identical” parameters into one. So, for e.g. two optional filters, four positional parameters are needed:

select size, colour, price from shirts
  where (size = ? or ? is null)
  and (colour = ? or ? is null)

After the call to isc_dsql_describe_bind(), the sqltype of the 2nd and 4th parameter will be set to SQL_NULL. As said, Firebird has no knowledge of their special relation with the 1st and 3d parameter – this is entirely the responsibility of the programmer. 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 filled in 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.